pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction Parameters - need help !!!
5+ messages / 4 participants
[nested] [flat]
* Function Parameters - need help !!!
@ 2004-06-21 06:21 Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
0 siblings, 1 reply; 5+ messages in thread
From: Pradeepkumar, Pyatalo (IE10) @ 2004-06-21 06:21 UTC (permalink / raw)
To: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function Parameters - need help !!!
@ 2004-06-21 09:42 V i s h a l Kashyap @ [Sai Hertz And Control Systems] <sank89@sancharnet.in>
parent: Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
0 siblings, 0 replies; 5+ messages in thread
From: V i s h a l Kashyap @ [Sai Hertz And Control Systems] @ 2004-06-21 09:42 UTC (permalink / raw)
To: Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function Parameters - need help !!!
@ 2004-06-21 10:48 Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
0 siblings, 2 replies; 5+ messages in thread
From: Pradeepkumar, Pyatalo (IE10) @ 2004-06-21 10:48 UTC (permalink / raw)
To: aspire420@hotpop.com, "Pradeepkumar, Pyatalo (IE10)" <Pradeepkumar.Pyatalo@honeywell.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function Parameters - need help !!!
@ 2004-06-21 11:52 Phil Endecott <spam_from_postgresql_sql@chezphil.org>
parent: Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
1 sibling, 0 replies; 5+ messages in thread
From: Phil Endecott @ 2004-06-21 11:52 UTC (permalink / raw)
To: Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function Parameters - need help !!!
@ 2004-06-21 11:54 Richard Huxton <dev@archonet.com>
parent: Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
1 sibling, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2004-06-21 11:54 UTC (permalink / raw)
To: Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>; +Cc: aspire420@hotpop.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-06-21 11:54 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-21 06:21 Function Parameters - need help !!! Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
2004-06-21 09:42 ` V i s h a l Kashyap @ [Sai Hertz And Control Systems] <sank89@sancharnet.in>
2004-06-21 10:48 Re: Function Parameters - need help !!! Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
2004-06-21 11:52 ` Phil Endecott <spam_from_postgresql_sql@chezphil.org>
2004-06-21 11:54 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox