Thread: Name the Parameters

Name the Parameters

From

"Kumar"

Date:

06 September 2003, 04:38:25

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: Name the Parameters

From

Ian Barwick

Date:

06 September 2003, 05:23:27

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