Received: from smtp4.mail.yahoo.com (smtp4.mail.yahoo.com [128.11.69.101]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f18DK4x07057 for ; Thu, 8 Feb 2001 08:20:04 -0500 (EST) (envelope-from janwieck@yahoo.com) Received: from a5c54.pppool.de (HELO jupiter.greatbridge.com) (213.6.92.84) by smtp.mail.vip.suc.yahoo.com with SMTP; 8 Feb 2001 13:19:59 -0000 X-Apparently-From: Received: (from janwieck@localhost) by jupiter.greatbridge.com (8.9.3/8.9.3) id HAA03553; Thu, 8 Feb 2001 07:22:13 -0500 From: Jan Wieck Message-Id: <200102081222.HAA03553@jupiter.greatbridge.com> Subject: Re: [HACKERS] Re: PL/PGSQL function with parameters In-Reply-To: <25204.981476161@sss.pgh.pa.us> from Tom Lane at "Feb 6, 2001 11:16:01 am" To: Tom Lane Date: Thu, 8 Feb 2001 07:22:13 -0500 (EST) CC: Michael Ansley , Jan Wieck , sqllist , pgsql-hackers@postgresql.orgg X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200102/154 X-Sequence-Number: 1334 Tom Lane wrote: > Michael Ansley writes: > > CREATE FUNCTION table_count(varchar) RETURNS integer AS ' > > DECLARE > > SQL varchar; > > RES integer; > > BEGIN > > SQL = ''SELECT * INTO temp1 FROM '' || $1; > > EXECUTE SQL; > > SELECT count(*) INTO RES FROM temp1; > > RETURN(RES) > > END; > > ' > > LANGUAGE 'plpgsql'; > > > What I couldn't get it to do was to select directly into the variable RES. > > I tried this, and it seems that "SELECT ... INTO foo" is not executed > correctly by EXECUTE --- the INTO is handled as an ordinary select-into- > table construct rather than plpgsql's select-into-variable. > > While I have not looked closely, I seem to recall that plpgsql handles > INTO by stripping that clause out of the statement before it's passed to > the SQL engine. Evidently that's not happening in the EXECUTE case. > > Jan, do you agree this is a bug? Is it reasonable to try to repair it > for 7.1? If we do not change the behavior of EXECUTE now, I fear it > will be too late --- some people will come to depend on the existing > behavior. EXECUTE simply takes the string expression and throws it into SPI_exec() without parsing. Changing that for 7.1 is *not* possible. The above can be accomplished by DECLARE ROW record; RES integer; BEGIN FOR ROW IN EXECUTE ''SELECT count(*) AS N FROM '' || $1 LOOP RES := N; END LOOP; RETURN RES; END; Not as elegant as it should be, but at least possible. There's much to be done for a future version of PL/pgSQL, but better support for dynamic SQL needs alot of functionality added to the main parser and the SPI manager in the first place. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com