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
|
- Re: [SQL] Name the Parameters Kumar
- Re: [SQL] Name the Parameters Ian Barwick