Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0DGxwB21805 for ; Sun, 13 Jan 2002 11:59:58 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g0DGxXg26112; Sun, 13 Jan 2002 11:59:33 -0500 (EST) To: "Unnikrishnan Menon" cc: pgsql-sql@postgresql.org Subject: Re: Problems with PL/PGSQL - functions In-reply-to: <008801c19c3f$d588ce70$e90aa8c0@UNNIKRISHNAN> References: <008801c19c3f$d588ce70$e90aa8c0@UNNIKRISHNAN> Comments: In-reply-to "Unnikrishnan Menon" message dated "Sun, 13 Jan 2002 20:07:27 +0530" Date: Sun, 13 Jan 2002 11:59:33 -0500 Message-ID: <26109.1010941173@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200201/109 X-Sequence-Number: 6198 "Unnikrishnan Menon" writes: > b. query1 :=3D '' Select xyz as variable from S_''|| nextval(sequence)|= > |'' where abc =3D (some counter value) '' ; > Execute query1; You can't use EXECUTE for a SELECT (no way to retrieve the result) nor for a SELECT INTO (annoying implementation restriction that should be removed someday). The only way to get a result from a dynamically-constructed select statement is to use FOR ... EXECUTE. You do need to use EXECUTEs with any query involving a dynamically generated table name, so you'll just have to grin and bear it. But --- does the table actually have to be handled that way? If you need exactly one such temp table per session, you could use "CREATE TEMP TABLE" and give the table a fixed name. Then you could skip all the BS with EXECUTE. Note however that it has to be per session; dropping and recreating the table mid-session won't work since already-cached query plans in your plpgsql functions will still point to the old temp table. regards, tom lane