Обсуждение: Function Parameters - need help !!!

Hi all,

I am a new joinee to this mailing list. I am using PostgreSql for my
project. We are trying to port a few stored procedures written in SQL to
PGSQL. But i am finding difficulties in doing that. I m new to
Postgresql....dont know much about it. I tried a lot of books and online
documents.....no go. I have problems in passing default values to function
paraameters. I would really appreciate if anyone can help me out with this.
The function I am trying to port is as follows : 

CREATE PROC PP_ReadPointByValue
@SessionID int = NULL, --these r default parameters
@SPSID int = 1,--default value
@ParameterName nvarchar (50) = NULL, -- if NULL read all parameters
@NumValue  real = NULL,
@StrValue nvarchar (255) = NULL,
@ParameterID int = NULL

Now how do I specify default parameters to the arguments in Plpgsql .

> With Best Regards 
> Pradeep Kumar P J 
> Honeywell_______________________________________
> No.151/1, Doraisanipalya, (Opp. to IIMB) Bannerghatta Rd, Bangalore 560
> 076. 
> Telephone No +91 80 26588360 / 51197222 xtn. 8211 
> Fax No +91 80 26584750 
> Website: Honeywell.com
> 
> 

Dear Pradeep ,

>CREATE PROC PP_ReadPointByValue
>@SessionID int = NULL, --these r default parameters
>  
>
SessionID INT := NULL;

>@SPSID int = 1,--default value
>  
>
SPSID int := 1 ;

>@ParameterName nvarchar (50) = NULL, -- if NULL read all parameters
>  
>
ParameterName varchar(50) := NULL ;

>@NumValue  real = NULL,
>@StrValue nvarchar (255) = NULL,
>  
>
same as ParameterName varchar(50) := NULL ;

>@ParameterID int = NULL
>  
>
ParameterID int := NULL;


Kindly note :
1. This all is for plpgsql procedure
2. All the variable in upcase would be used as lower case i.e   SessionID would be sessionid untll it is not as
"SessionID"
3. If a value is not intiliazed it is defaulted to NULL.

-- 
Best Regards,
Vishal Kashyap
Director / Lead Software Developer,
Sai Hertz And Control Systems Pvt Ltd,
http://saihertz.rediffblogs.com [Comming Soon http://www.saihertz.com]
Yahoo  IM: coeb_college[ a t ]yahoo.com

Well this is right for the local variables....but what about the function
parameters. Okay, I will make it more simple....say I want to write a
function like this....

CREATE FUNCTION test(integer,integer) RETURNS INTEGER AS '
DECLARE 
param1 ALIAS FOR $1;
param2 ALIAS FOR $2;
BEGIN-------------
-------
END;
' LANGUAGE 'plpgsql';

Now i want to define default values to param1 and param2....if i dont pass a
value....the function should take default values for those
arguments.....that is what i was trying to implement....need help on this



-----Original Message-----
From: V i s h a l Kashyap @ [Sai Hertz And Control Systems]
[mailto:sank89@sancharnet.in]
Sent: Monday, June 21, 2004 3:12 PM
To: Pradeepkumar, Pyatalo (IE10)
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Function Parameters - need help !!!


Dear Pradeep ,

>CREATE PROC PP_ReadPointByValue
>@SessionID int = NULL, --these r default parameters
>  
>
SessionID INT := NULL;

>@SPSID int = 1,--default value
>  
>
SPSID int := 1 ;

>@ParameterName nvarchar (50) = NULL, -- if NULL read all parameters
>  
>
ParameterName varchar(50) := NULL ;

>@NumValue  real = NULL,
>@StrValue nvarchar (255) = NULL,
>  
>
same as ParameterName varchar(50) := NULL ;

>@ParameterID int = NULL
>  
>
ParameterID int := NULL;


Kindly note :
1. This all is for plpgsql procedure
2. All the variable in upcase would be used as lower case i.e   SessionID would be sessionid untll it is not as
"SessionID"
3. If a value is not intiliazed it is defaulted to NULL.

-- 
Best Regards,
Vishal Kashyap
Director / Lead Software Developer,
Sai Hertz And Control Systems Pvt Ltd,
http://saihertz.rediffblogs.com [Comming Soon http://www.saihertz.com]
Yahoo  IM: coeb_college[ a t ]yahoo.com

Hi,

Quote from Section 37.11 of the manual:

# There are no default values for parameters in PostgreSQL.
# You can overload function names in PostgreSQL. This is often used to work around the lack of default parameters.

So for your example:

> CREATE FUNCTION test(integer,integer) RETURNS INTEGER AS '
> ...

you should be able to write:

CREATE FUNCTION test(integer) RETURNS INTEGER AS '
BEGIN test($1, default_value_for_param2);
END;
' LANGUAGE 'plpgsql';

and also:

CREATE FUNCTION test() RETURNS INTEGER AS '
BEGIN test(default_value_for_param1);
END;
' LANGUAGE 'plpgsql';


Hope this is what you were looking for.

--Phil.

Pradeepkumar, Pyatalo (IE10) wrote:
> Well this is right for the local variables....but what about the function
> parameters. Okay, I will make it more simple....say I want to write a
> function like this....
> 
> CREATE FUNCTION test(integer,integer) RETURNS INTEGER AS '
> DECLARE 
> param1 ALIAS FOR $1;
> param2 ALIAS FOR $2;
> BEGIN
>  -------    IF param1 IS NULL THEN      param1 := 1234;    END IF;

> Now i want to define default values to param1 and param2....if i dont pass a
> value....the function should take default values for those
> arguments.....that is what i was trying to implement....need help on this

Now if you want to be able to call function test like so:  SELECT test(1)
Then you can't.

The workaround is to define two functions: CREATE FUNCTION test(integer,integer) CREATE FUNCTION test(integer)
Function #2 calls function #1 with the 2nd paramter pre-defined.

PG is quite strict about it's type-matching, which is why you need to 
have two entries for the function.

--   Richard Huxton  Archonet Ltd