pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedName the Parameters
2+ messages / 2 participants
[nested] [flat]
* Name the Parameters
@ 2003-09-06 04:42 Kumar <sgnerd@yahoo.com.sg>
2003-09-06 05:21 ` Re: Name the Parameters Ian Barwick <barwick@gmx.net>
0 siblings, 1 reply; 2+ messages in thread
From: Kumar @ 2003-09-06 04:42 UTC (permalink / raw)
To: pgsql-sql
Dear Gurus,
Is it possible to name the IN parameters of the PLpgSQL Functions. Because naming the parameter will ease coding. See the following function for insert, having 10 parameters as a input.
=====================================================================================
CREATE FUNCTION InsertFn(int8, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, timestamp) RETURNS varchar AS 'BEGIN
RETURN ''HAi'';
-- Check if the address_id specified exist in the address table
IF NOT EXISTS ( SELECT address_id FROM address
WHERE address_id = $1 AND rec_deleted_flag = ''N'' )
THEN
RAISE EXCEPTION ''The Address Specified is Invalid'';
END IF;
-- Check if the timestamp is same for the given address_id
IF NOT EXISTS ( SELECT address_id FROM "WATS".address
WHERE address_id = $1 AND rec_modified_date = $12; )
THEN
RAISE EXCEPTION ''The record has already been updated by another user.'';
END IF;
-- Else, update the record
UPDATE "WATS".address
SET address = $2
, city = $3
, state = $4
, country = $5
, zipcode = $6
, email = $7
, home_phone = $8
, work_phone = $9
, cell_phone = $10
, pager = $11
WHERE address_id = $1
;
END;
' LANGUAGE 'plpgsql' IMMUTABLE;
GRANT EXECUTE ON FUNCTION sp_upd_add_001(int8, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, timestamp) TO PUBLIC;
GRANT EXECUTE ON FUNCTION "WATS".sp_upd_add_001(int8, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, timestamp) TO wats;
=====================================================================================
See it is difficult to pass the parameters with out name them.
(2) I am getting error at the code
IF NOT EXISTS.......
Is that not supported at Postgres?
Please shed ur light on this. pls
Regards
Kumar
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Name the Parameters
2003-09-06 04:42 Name the Parameters Kumar <sgnerd@yahoo.com.sg>
@ 2003-09-06 05:21 ` Ian Barwick <barwick@gmx.net>
0 siblings, 0 replies; 2+ messages in thread
From: Ian Barwick @ 2003-09-06 05:21 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; pgsql-sql
On Saturday 06 September 2003 06:42, Kumar wrote:
> Dear Gurus,
>
> Is it possible to name the IN parameters of the PLpgSQL Functions. Because
> naming the parameter will ease coding. See the following function for
> insert, having 10 parameters as a input.
No, though there is an item on the TODO list IIRC.
Using ALIAS FOR might make things easier though, something
along the lines of:
DECLARE
address_id ALIAS FOR $1;
address ALIAS FOR $2;
...
BEGIN
...
END;
Ian Barwick
barwick@gmx.net
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-09-06 05:21 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-06 04:42 Name the Parameters Kumar <sgnerd@yahoo.com.sg>
2003-09-06 05:21 ` Ian Barwick <barwick@gmx.net>
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