Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f16GG9x07161; Tue, 6 Feb 2001 11:16:09 -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.1/8.11.1) with ESMTP id f16GG1925207; Tue, 6 Feb 2001 11:16:01 -0500 (EST) To: Michael Ansley cc: Jan Wieck , sqllist , pgsql-hackers@postgresql.org Subject: Re: [SQL] PL/PGSQL function with parameters In-reply-to: <7F124BC48D56D411812500D0B747251480F3D4@FILESERVER002> References: <7F124BC48D56D411812500D0B747251480F3D4@FILESERVER002> Comments: In-reply-to Michael Ansley message dated "Tue, 06 Feb 2001 13:16:06 +0000" Date: Tue, 06 Feb 2001 11:16:01 -0500 Message-ID: <25204.981476161@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200102/176 X-Sequence-Number: 4308 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. regards, tom lane