RE: Re: PL/pgsql EXECUTE 'SELECT INTO ...' - Mailing list pgsql-sql

<p><font size="2">What I wrote wasn't about temp tables, it was about selecting into plpgsql variables.  It would
appearthat Jan's syntax gets around this problem.</font><p><font size="2">MikeA</font><br /><p><font
size="2">-----OriginalMessage-----</font><br /><font size="2">From: Jan Wieck [<a
href="mailto:janwieck@Yahoo.com">mailto:janwieck@Yahoo.com</a>]</font><br/><font size="2">Sent: 08 February 2001
13:30</font><br/><font size="2">To: Tom Lane</font><br /><font size="2">Cc: Jan Wieck; pgsql-hackers@postgreSQL.org;
pgsql-sql@postgreSQL.org</font><br/><font size="2">Subject: [SQL] Re: PL/pgsql EXECUTE 'SELECT INTO ...'</font><br
/><p><fontsize="2">Tom Lane wrote:</font><br /><font size="2">> I have looked a little bit at what it'd take to make
SELECTINTO inside</font><br /><font size="2">> an EXECUTE work the same as it does in plain plpgsql --- that is,
the</font><br/><font size="2">> INTO should reference plpgsql variables, not a destination table.</font><br /><font
size="2">>It looks to me like this is possible but would require some nontrivial</font><br /><font size="2">>
re-engineeringinside plpgsql.  What I'm visualizing is that EXECUTE</font><br /><font size="2">> should read its
stringargument not just as an SPI_exec() string, but</font><br /><font size="2">> as an arbitrary plpgsql
proc_stmt. This would offer some interesting</font><br /><font size="2">> capabilities, like building a whole
FOR-loopfor dynamic execution.</font><br /><font size="2">> But there are a number of problems to be surmounted,
notablyarranging</font><br /><font size="2">> for the parsetree built by the plpgsql compiler not to be
irretrievably</font><br/><font size="2">> memory-leaked.  (That ties into something I'd wanted to do
anyway,</font><br/><font size="2">> which is to have the plpgsql compiler build its trees in a memory</font><br
/><fontsize="2">> context associated with the function, not via malloc().)</font><br /><font size="2">></font><br
/><fontsize="2">> This does not look like something to be tackling when we're already</font><br /><font
size="2">>in late beta, unfortunately.  So we have to decide what to do for 7.1.</font><br /><font size="2">> If
wedo nothing now, and then implement this feature in 7.2, we will</font><br /><font size="2">> have a backwards
compatibilityproblem: EXECUTE 'SELECT INTO ...'</font><br /><font size="2">> will completely change in
meaning.</font><br/><font size="2">></font><br /><font size="2">> I am inclined to keep our options open by
forbiddingEXECUTE 'SELECT</font><br /><font size="2">> INTO ...' for now.  That's more than a tad annoying, because
thatleaves</font><br /><font size="2">> no useful way to do a dynamically-built SELECT, but if we don't
forbid</font><br/><font size="2">> it I think we'll regret it later.</font><p><font size="2">    You can do
somethinglike</font><p><font size="2">        FOR record_var IN EXECUTE <string-expr> LOOP</font><br /><font
size="2">           ...</font><br /><font size="2">        END LOOP;</font><p><font size="2">    In this case, the
<string-expr>executed over SPI_exec() must</font><br /><font size="2">    return tuples (0-n). Otherwise you'll
geta runtime error.</font><p><font size="2">    Inside the loop you have access to the tuples via the record.</font><br
/><fontsize="2">    Is  that  the dynamically-built SELECT capability you've been</font><br /><font size="2">   
missing?</font><p><fontsize="2">    There's not that much need for mucking with  temp  tables  in</font><br /><font
size="2">   EXECUTE as all this discussion looks to me.</font><br /><p><font size="2">Jan</font><p><font
size="2">--</font><p><fontsize="2">#======================================================================#</font><br
/><fontsize="2"># It's easier to get forgiveness for being wrong than for being right. #</font><br /><font size="2">#
Let'sbreak this rule - forgive me.                                  #</font><br /><font
size="2">#==================================================JanWieck@Yahoo.com #</font><br /><br /><p><font
size="2">_________________________________________________________</font><br/><font size="2">Do You Yahoo!?</font><br
/><fontsize="2">Get your free @yahoo.com address at <a href="http://mail.yahoo.com"
target="_blank">http://mail.yahoo.com</a></font><code><fontsize="3"><br /><br />
**********************************************************************<br/> This email and any files transmitted with
itare confidential and<br /> intended solely for the use of the individual or entity to whom they<br /> are addressed.
Ifyou have received this email in error please notify<br /> Nick West - Global Infrastructure Manager.<br /><br /> This
footnotealso confirms that this email message has been swept by<br /> MIMEsweeper for the presence of computer
viruses.<br/><br /> www.mimesweeper.com<br /> **********************************************************************<br
/></font></code>