RE: [SQL] PL/PGSQL function with parameters - Mailing list pgsql-hackers

<p><font size="2">Yes, that was why I wrote it in the way that I did.  The table is effectively given a constant name,
andthe count is got from the table with a known name.  But of a kludge, but in 45sec, that was all I could come up with
;-)</font><p><fontsize="2">It would be VERY useful to see it fixed.</font><p><font size="2">Cheers...</font><br
/><p><fontsize="2">MikeA</font><br /><font size="2"> </font><p><font size="2">-----Original Message-----</font><br
/><fontsize="2">From: Tom Lane [<a href="mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</a>]</font><br /><font
size="2">Sent:06 February 2001 16:16</font><br /><font size="2">To: Michael Ansley</font><br /><font size="2">Cc: Jan
Wieck;sqllist; pgsql-hackers@postgresql.org</font><br /><font size="2">Subject: Re: [SQL] PL/PGSQL function with
parameters</font><br /><p><font size="2">Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
writes:</font><br/><font size="2">> CREATE FUNCTION table_count(varchar) RETURNS integer AS '</font><br /><font
size="2">>DECLARE</font><br /><font size="2">>       SQL varchar;</font><br /><font size="2">>       RES
integer;</font><br/><font size="2">> BEGIN</font><br /><font size="2">>       SQL = ''SELECT * INTO temp1 FROM ''
||$1;</font><br /><font size="2">>       EXECUTE SQL;</font><br /><font size="2">>       SELECT count(*) INTO RES
FROMtemp1;</font><br /><font size="2">>       RETURN(RES)</font><br /><font size="2">> END;</font><br /><font
size="2">>'</font><br /><font size="2">> LANGUAGE 'plpgsql';</font><p><font size="2">> What I couldn't get it
todo was to select directly into the variable RES.</font><p><font size="2">I tried this, and it seems that "SELECT ...
INTOfoo" is not executed</font><br /><font size="2">correctly by EXECUTE --- the INTO is handled as an ordinary
select-into-</font><br/><font size="2">table construct rather than plpgsql's select-into-variable.</font><p><font
size="2">WhileI have not looked closely, I seem to recall that plpgsql handles</font><br /><font size="2">INTO by
strippingthat clause out of the statement before it's passed to</font><br /><font size="2">the SQL engine.  Evidently
that'snot happening in the EXECUTE case.</font><p><font size="2">Jan, do you agree this is a bug?  Is it reasonable to
tryto repair it</font><br /><font size="2">for 7.1?  If we do not change the behavior of EXECUTE now, I fear
it</font><br/><font size="2">will be too late --- some people will come to depend on the existing</font><br /><font
size="2">behavior.</font><p>                       <font size="2">regards, tom lane</font><code><font size="3"><br
/><br/> **********************************************************************<br /> This email and any files
transmittedwith it are confidential and<br /> intended solely for the use of the individual or entity to whom they<br
/>are addressed. If you have received this email in error please notify<br /> Nick West - Global Infrastructure
Manager.<br/><br /> This footnote also confirms that this email message has been swept by<br /> MIMEsweeper for the
presenceof computer viruses.<br /><br /> www.mimesweeper.com<br />
**********************************************************************<br/></font></code>