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 f18DK8x07063 for ; Thu, 8 Feb 2001 08:20:08 -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:20:03 -0000 X-Apparently-From: Received: (from janwieck@localhost) by jupiter.greatbridge.com (8.9.3/8.9.3) id GAA03502; Thu, 8 Feb 2001 06:37:22 -0500 From: Jan Wieck Message-Id: <200102081137.GAA03502@jupiter.greatbridge.com> Subject: Re: [SQL] PL/PGSQL function with parameters In-Reply-To: <3A803856.DB172929@agliodbs.com> from Josh Berkus at "Feb 6, 2001 09:45:58 am" To: josh@agliodbs.com Date: Thu, 8 Feb 2001 06:37:22 -0500 (EST) CC: sqllist , pgsql-hackers@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/270 X-Sequence-Number: 4401 Josh Berkus wrote: > Tom, Jan, Michael, > > > 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. > > If you think that's the best way. What we're really all wanting is a wy > in PL/pgSQL to pass a parameter as an object name. Doing it *without* > using EXECUTE would be even better than modifying EXECUTE to accomdate > SELECT ... INTO variable. > > If we can write queries that address tables by OID, that would give us a > quick workaround ... get the OID from pg_class, then pass it to the > query as variables of type OID: > > SELECT column1_oid, column2_oid FROM table_oid > WHERE column2_oid = variable1 > ORDER BY column1_oid; > > OF course, having PL/pgSQL do this automatically would be even better, > but I suspect would require a *lot* of extra programming by Jan. Couple of problems here: 1. The main parser, which is used in turn by the SPI stuff, doesn't allow parameters passed in for object- identifiers. 2. I'm not sure if *all* statements are really supported by SPI_prepare() plus SPI_execp(). EXECUTE currently uses SPI_exec() to directly invoke the querystring. 3. PL/pgSQL needs a clean way to identify statements that shall not be cached. First things that come to mind are - statements using temporary objects - statements invoking utility commands (or generally any DDL) - statements having parameters for object-identifiers If identified as such non-cacheable query, PL/pgSQL doesn't use SPI_saveplan() but recreates a new plan every time. 4. PL handlers in general should have a registering mechanism for a callback function. On any schema change (i.e. shared syscache invalidation) this function is called, causing the PL handler to invalidate *ALL* function bytecodes and cached plans. Keeping track of things like "var table.att%TYPE" used in a function would be a mess - so better throw away anything. Yes, that's a *lot* to do. 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