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 f18DJYx06771 for ; Thu, 8 Feb 2001 08:19:35 -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:26 -0000 X-Apparently-From: Received: (from janwieck@localhost) by jupiter.greatbridge.com (8.9.3/8.9.3) id IAA03675; Thu, 8 Feb 2001 08:29:49 -0500 From: Jan Wieck Message-Id: <200102081329.IAA03675@jupiter.greatbridge.com> Subject: Re: PL/pgsql EXECUTE 'SELECT INTO ...' In-Reply-To: <10497.981602102@sss.pgh.pa.us> from Tom Lane at "Feb 7, 2001 10:15:02 pm" To: Tom Lane Date: Thu, 8 Feb 2001 08:29:49 -0500 (EST) CC: Jan Wieck , pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org 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/269 X-Sequence-Number: 4400 Tom Lane wrote: > I have looked a little bit at what it'd take to make SELECT INTO inside > an EXECUTE work the same as it does in plain plpgsql --- that is, the > INTO should reference plpgsql variables, not a destination table. > It looks to me like this is possible but would require some nontrivial > re-engineering inside plpgsql. What I'm visualizing is that EXECUTE > should read its string argument not just as an SPI_exec() string, but > as an arbitrary plpgsql proc_stmt. This would offer some interesting > capabilities, like building a whole FOR-loop for dynamic execution. > But there are a number of problems to be surmounted, notably arranging > for the parsetree built by the plpgsql compiler not to be irretrievably > memory-leaked. (That ties into something I'd wanted to do anyway, > which is to have the plpgsql compiler build its trees in a memory > context associated with the function, not via malloc().) > > This does not look like something to be tackling when we're already > in late beta, unfortunately. So we have to decide what to do for 7.1. > If we do nothing now, and then implement this feature in 7.2, we will > have a backwards compatibility problem: EXECUTE 'SELECT INTO ...' > will completely change in meaning. > > I am inclined to keep our options open by forbidding EXECUTE 'SELECT > INTO ...' for now. That's more than a tad annoying, because that leaves > no useful way to do a dynamically-built SELECT, but if we don't forbid > it I think we'll regret it later. You can do something like FOR record_var IN EXECUTE LOOP ... END LOOP; In this case, the executed over SPI_exec() must return tuples (0-n). Otherwise you'll get a runtime error. Inside the loop you have access to the tuples via the record. Is that the dynamically-built SELECT capability you've been missing? There's not that much need for mucking with temp tables in EXECUTE as all this discussion looks to me. 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