Обсуждение: Re: [SQL] PL/PGSQL function with parameters
Michael Ansley <Michael.Ansley@intec-telecom-systems.com> writes:
> CREATE FUNCTION table_count(varchar) RETURNS integer AS '
> DECLARE
> SQL varchar;
> RES integer;
> BEGIN
> SQL = ''SELECT * INTO temp1 FROM '' || $1;
> EXECUTE SQL;
> SELECT count(*) INTO RES FROM temp1;
> RETURN(RES)
> END;
> '
> LANGUAGE 'plpgsql';
> What I couldn't get it to do was to select directly into the variable RES.
I tried this, and it seems that "SELECT ... INTO foo" is not executed
correctly by EXECUTE --- the INTO is handled as an ordinary select-into-
table construct rather than plpgsql's select-into-variable.
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.
regards, tom lane
> > SQL = ''SELECT * INTO temp1 FROM '' || $1;
>
> I tried this, and it seems that "SELECT ... INTO foo" is not executed
> correctly by EXECUTE --- the INTO is handled as an ordinary select-into-
> table construct rather than plpgsql's select-into-variable.
>
> 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.
From gram.y, yes it tries to strip the INTO by skipping over some stuff,
maybe the "*" trips it ?
Anyway the syntax SELECT INTO temp1 * FROM .... should work.
On this same subject, the plpgsql doc says to useSELECT expression INTO var FROM ...
but Bruce's book, in several examples ("PL/PGSQL Functions" for instance,
node203.html) usesSELECT INTO var expression FROM ...
Both should work, but there may be there's something to straighten up here.
Cheers,
Florent
--
florent.guillaume@mail.com
Florent Guillaume <efgeor@noos.fr> writes:
> On this same subject, the plpgsql doc says to use
> SELECT expression INTO var FROM ...
> but Bruce's book, in several examples ("PL/PGSQL Functions" for instance,
> node203.html) uses
> SELECT INTO var expression FROM ...
> Both should work, but there may be there's something to straighten up here.
IIRC, the plpgsql code is actually *very* lax about where you put the
INTO; it'll suck it out from almost anyplace in the query string ...
regards, tom lane
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. And all of this should be influenced by whatever you guys are planning to do about Stored Procedures. -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
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
> Josh Berkus wrote:
>> 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;
This is completely pointless, AFAICS. If you don't know what table
is to be selected from, then you can't do *any* semantic checking or
planning in advance, so you might as well just do the entire processing
at runtime. That's exactly what EXECUTE does. I don't see any
functional advantage in an intermediate step between plpgsql's normal
behavior (caching of query plans) and EXECUTE. If it bought some
readability over constructing a query string for EXECUTE, then maybe,
but dealing in table and column OIDs is not my idea of a pleasant or
readable way to program ...
regards, tom lane