X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3BAB4D1B4E2 for ; Sat, 6 Sep 2003 04:38:24 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 10739-04 for ; Sat, 6 Sep 2003 01:37:52 -0300 (ADT) Received: from smtp100.mail.sc5.yahoo.com (smtp100.mail.sc5.yahoo.com [216.136.174.138]) by svr1.postgresql.org (Postfix) with SMTP id BBEE1D1BA3B for ; Sat, 6 Sep 2003 01:37:51 -0300 (ADT) Received: from ptil-138-190-chn.primus-india.net (HELO IVESIA67) (sgnerd@203.196.190.138 with login) by smtp.mail.vip.sc5.yahoo.com with SMTP; 6 Sep 2003 04:37:50 -0000 Message-ID: <003501c37431$59f9f9c0$7502a8c0@hdsc.com> Reply-To: "Kumar" From: "Kumar" To: "psql" Subject: Name the Parameters Date: Sat, 6 Sep 2003 10:12:53 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0032_01C3745F.6F886230" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/34 X-Sequence-Number: 15012 This is a multi-part message in MIME format. ------=_NextPart_000_0032_01C3745F.6F886230 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 inser= t, having 10 parameters as a input. =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D CREATE FUNCTION InsertFn(int8, varchar, varchar, varchar, varchar, varchar,= varchar, varchar, varchar, varchar, varchar, timestamp) RETURNS varchar AS= 'BEGIN=20 RETURN ''HAi''; -- Check if the address_id specified exist in the address table IF NOT EXISTS ( SELECT address_id FROM address WHERE address_id =3D $1 AND rec_deleted_f= lag =3D ''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 =3D $1 AND rec_modified_date = =3D $12; ) THEN RAISE EXCEPTION ''The record has already been updated by another user.''; END IF; -- Else, update the record UPDATE "WATS".address SET address =3D $2 , city =3D $3 , state =3D $4 , country =3D $5 , zipcode =3D $6 , email =3D $7 , home_phone =3D $8 , work_phone =3D $9 , cell_phone =3D $10 , pager =3D $11 WHERE address_id =3D $1 ; END; ' LANGUAGE 'plpgsql' IMMUTABLE; GRANT EXECUTE ON FUNCTION sp_upd_add_001(int8, varchar, varchar, varchar, v= archar, varchar, varchar, varchar, varchar, varchar, varchar, timestamp) TO= PUBLIC; GRANT EXECUTE ON FUNCTION "WATS".sp_upd_add_001(int8, varchar, varchar, var= char, varchar, varchar, varchar, varchar, varchar, varchar, varchar, timest= amp) TO wats; =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D See it is difficult to pass the parameters with out name them. (2) I am getting error at the code=20 IF NOT EXISTS....... Is that not supported at Postgres? Please shed ur light on this. pls Regards Kumar ------=_NextPart_000_0032_01C3745F.6F886230 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Dear Gurus,
 
Is it possible to name the IN parameters of the PLpgSQL= =20 Functions. Because naming the parameter will ease coding. See the following= =20 function for insert, having 10 parameters as a input.
 
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
CREATE FUNCTION InsertFn(int8, varchar, varchar, varcha= r,=20 varchar, varchar, varchar, varchar, varchar, varchar, varchar, timestamp)= =20 RETURNS varchar AS 'BEGIN 
RETURN=20 ''HAi'';
-- Check if the address_id specified exist in the addres= s=20 table
IF NOT EXISTS ( SELECT address_id FROM   =20 address
          &nbs= p;            &= nbsp; =20 WHERE address_id =3D $1  AND       = =20 rec_deleted_flag =3D ''N''   )
THEN
RAISE EXCEPTION ''The A= ddress=20 Specified is Invalid'';
END IF;
 
-- Check if the timestamp is same for the given=20 address_id
IF NOT EXISTS ( SELECT =20 address_id FROM    =20 "WATS".address
         &nb= sp;            =    =20 WHERE address_id =3D $1 AND  rec_modified_date =3D=20 $12; )
THEN
RAISE EXCEPTION ''The record has already been update= d by=20 another user.'';
END IF;
 
-- Else, update the=20 record
UPDATE "WATS".address
SET  =20 address          =3D $2
&nb= sp;,=20 city            = ;    =20 =3D $3
 ,=20 state           &nbs= p;  =20 =3D $4
 ,=20 country           =3D=20 $5
 , zipcode         = =3D=20 $6
 ,=20 email           &nbs= p;  =20 =3D $7
 , home_phone  =3D $8
 , work_phone  = =3D=20 $9
 , cell_phone     =3D $10
 ,=20 pager           &nbs= p; =20 =3D $11
WHERE  address_id       =3D= =20 $1
;
END;
'  LANGUAGE 'plpgsql' IMMUTABLE;
GRANT EXECUTE O= N=20 FUNCTION sp_upd_add_001(int8, varchar, varchar, varchar, varchar, varchar,= =20 varchar, varchar, varchar, varchar, varchar, timestamp) TO PUBLIC;
GRANT= =20 EXECUTE ON FUNCTION "WATS".sp_upd_add_001(int8, varchar, varchar, varchar,= =20 varchar, varchar, varchar, varchar, varchar, varchar, varchar, timestamp) T= O=20 wats;
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
 
See it is difficult to pass the parameters with out nam= e=20 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

------=_NextPart_000_0032_01C3745F.6F886230--