From ssutjiono@wc-group.com Sat Mar 16 21:58:04 2002 Received: from qmsql.netservers.net (hpmail1.netservers.net [209.196.128.163]) by postgresql.org (Postfix) with SMTP id 056AA475D4C for ; Sat, 16 Mar 2002 16:58:04 -0500 (EST) Received: (qmail 43493 invoked from network); 16 Mar 2002 21:58:04 -0000 Received: from unknown (HELO AARON) (66.26.63.73) by hpmail1.netservers.net with SMTP; 16 Mar 2002 21:58:04 -0000 Message-ID: <00e901c1cd34$edd88cb0$110a010a@headquarters.wcgroup.com> From: "Samuel J. Sutjiono" To: , Subject: Variable Substitution for table name Date: Sat, 16 Mar 2002 16:52:54 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00E6_01C1CD0B.048E6620" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200203/876 X-Sequence-Number: 22248 This is a multi-part message in MIME format. ------=_NextPart_000_00E6_01C1CD0B.048E6620 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello all, Does anybody know whether I can do variable substitution in PostgreSQL func= tion ??? create function Test(text) returns text as ' DECLARE=20=20 NewView ALIAS for $1; =20=20=20=20=20=20=20=20 BEGIN For rec_set IN SELECT DeptdID, VendorName from NewView where (DeptID =3D iDeptID)=20 I appreciate any help very much. ------=_NextPart_000_00E6_01C1CD0B.048E6620 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello all,
 
Does anybody know whether I can do variabl= e=20 substitution in PostgreSQL function ???
 
create function Test(text= ) returns=20 text as '
DECLARE  
  NewView ALIAS f= or=20 $1;
       
BEGIN
 
 For rec_set IN SELECT DeptdID,=20 VendorName
 from NewView where  (DeptID =3D i= DeptID)=20
 
I appreciate any help very much.
 
------=_NextPart_000_00E6_01C1CD0B.048E6620-- From sszabo@megazone23.bigpanda.com Sun Mar 17 00:07:56 2002 Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id C98124763E0; Sat, 16 Mar 2002 19:07:56 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 658A0D5FA; Sat, 16 Mar 2002 16:07:59 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 573205BFD; Sat, 16 Mar 2002 16:07:59 -0800 (PST) Date: Sat, 16 Mar 2002 16:07:59 -0800 (PST) From: Stephan Szabo To: "Samuel J. Sutjiono" Cc: , Subject: Re: Variable Substitution for table name In-Reply-To: <00e901c1cd34$edd88cb0$110a010a@headquarters.wcgroup.com> Message-ID: <20020316160551.W74854-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200203/878 X-Sequence-Number: 22250 On Sat, 16 Mar 2002, Samuel J. Sutjiono wrote: > Hello all, > > Does anybody know whether I can do variable substitution in PostgreSQL function ??? In plpgsql you can usually use EXECUTE to build a query string and run it in place of the plain query, so something like EXECUTE ''SELECT DeptdID, VendorName from '' || NewView || '' where DeptId=iDeptID'' in place of the select query. > > create function Test(text) returns text as ' > DECLARE > NewView ALIAS for $1; > > BEGIN > > For rec_set IN SELECT DeptdID, VendorName > from NewView where (DeptID = iDeptID) > > I appreciate any help very much. > >