From: Kumar <sgnerd@yahoo.com.sg>
To: psql <pgsql-sql@postgresql.org>
Subject: Name the Parameters
Date: Sat, 6 Sep 2003 10:12:53 +0530
Message-ID: <003501c37431$59f9f9c0$7502a8c0@hdsc.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sgnerd@yahoo.com.sg
Subject: Re: Name the Parameters
In-Reply-To: <003501c37431$59f9f9c0$7502a8c0@hdsc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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