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
