X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A9D8AD9CBC for ; Fri, 30 Sep 2005 17:59:18 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 87826-10 for ; Fri, 30 Sep 2005 20:59:07 +0000 (GMT) Received: from window.monsterlabs.com (window.monsterlabs.com [216.183.105.176]) by svr1.postgresql.org (Postfix) with SMTP id 2BC2ED8BED for ; Fri, 30 Sep 2005 17:59:07 -0300 (ADT) Received: (qmail 9257 invoked from network); 30 Sep 2005 20:55:48 -0000 Received: from pcp0012204803pcs.blairblvd.tn.nash.comcast.net (HELO ?192.168.15.103?) (69.245.49.69) by 0 with SMTP; 30 Sep 2005 20:55:48 -0000 In-Reply-To: <1128017805.9061.5.camel@localhost.localdomain> References: <1128017805.9061.5.camel@localhost.localdomain> Mime-Version: 1.0 (Apple Message framework v734) Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed Message-Id: <1045972B-4D8E-4801-A958-76A1DB6FAD0B@sitening.com> Cc: pgsql-sql@postgresql.org Content-Transfer-Encoding: quoted-printable From: "Thomas F. O'Connell" Subject: Re: EXECUTE with SELECT INTO variable, or alternatives Date: Fri, 30 Sep 2005 15:59:06 -0500 To: mario.splivalo@mobart.hr X-Mailer: Apple Mail (2.734) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.02 required=5 tests=[AWL=0.020] X-Spam-Level: X-Archive-Number: 200509/305 X-Sequence-Number: 22852 Per the docs: "The results from SELECT commands are discarded by EXECUTE, and =20 SELECT INTO is not currently supported within EXECUTE. So there is no =20= way to extract a result from a dynamically-created SELECT using the =20 plain EXECUTE command. There are two other ways to do it, however: =20 one is to use the FOR-IN-EXECUTE loop form described in Section =20 35.7.4, and the other is to use a cursor with OPEN-FOR-EXECUTE, as =20 described in Section 35.8.2." http://www.postgresql.org/docs/8.0/static/plpgsql-=20 statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN So you've already hit upon one of your options. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i=99 http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) On Sep 29, 2005, at 1:16 PM, Mario Splivalo wrote: > I can assign a value to a variable in several ways: > > myVar :=3D (SELECT col FROM table WHERE somecondition...) > myVar :=3D col FROM table WHERE somecondtition... > SELECT col INTO myVar FROM table WHERE somecondition > > How do I do any of the above using EXECUTE? I need to be able to =20 > assign > the value to a variable, a value returned by a querry on a temporary > table. > > So far I have found workaround like this: > > myRec record; > > FOR rec IN EXECUTE ''SELECT col FROM table WHERE somecondition'' > LOOP > myVar :=3D rec > END LOOP > > Obviously, the above SELECT query returns only one row. Still, if is > realy inconvinient to have FOR...LOOP...END LOOP construct for =20 > assigning > the value to a variable 'read' from the temporary table. > > Mario