Re: PL/PGSQL function with parameters - Mailing list pgsql-sql
From | Jan Wieck |
---|---|
Subject | Re: PL/PGSQL function with parameters |
Date | |
Msg-id | 200102081137.GAA03502@jupiter.greatbridge.com Whole thread Raw |
In response to | Re: PL/PGSQL function with parameters (Josh Berkus <josh@agliodbs.com>) |
Responses |
Re: [HACKERS] Re: PL/PGSQL function with parameters
|
List | pgsql-sql |
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 mindare - 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 planevery 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