Just for the record:

DROP FUNCTION table_count(varchar);
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';

...


dev=> select table_count('switch');
 test
------
    6
(1 row)


This function produces exactly what you would hope for, a count of rows in
the specified table.  It's particularly inefficient at doing it, because it
does a table copy (and doesn't bother to clean up after itself ;-(), so
don't do this on a large table ;-) but it shows the principle.

What I couldn't get it to do was to select directly into the variable RES.
Perhaps someone could enlighten me.

Cheers...


MikeA

-----Original Message-----
From: David Richter [mailto:D.Richter@DKFZ-heidelberg.de]
Sent: 06 February 2001 09:39
To: Michael Ansley
Subject: Re: [SQL] PL/PGSQL function with parameters


Hello!

Thanks a lot for Your answer!

But with my version 7.0.2. this suggestion doesn't work:

It appears: parser: parse error at or near "exec" or 
parser: parse error at or near "execute"

And how should i design the update command in the suggested way e.g.?

EXEC ''UPDATE '' ||$1 
                      ''SET '' || $2 '' = psr_rec.parentoid
                        WHERE chilioid = psr_rec.childoid;''


Wich exact release I will need to use this feature?
Wich one are You using?

Greetings

David


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************





RE: [SQL] PL/PGSQL function with parameters


Just for the record:

DROP FUNCTION table_count(varchar);
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';

...


dev=> select table_count('switch');
 test
------
    6
(1 row)


This function produces exactly what you would hope for, a count of rows in the specified table.  It's particularly inefficient at doing it, because it does a table copy (and doesn't bother to clean up after itself ;-(), so don't do this on a large table ;-) but it shows the principle.
What I couldn't get it to do was to select directly into the variable RES.  Perhaps someone could enlighten me.

Cheers...


MikeA

-----Original Message-----
From: David Richter [mailto:D.Richter@DKFZ-heidelberg.de]
Sent: 06 February 2001 09:39
To: Michael Ansley
Subject: Re: [SQL] PL/PGSQL function with parameters


Hello!

Thanks a lot for Your answer!

But with my version 7.0.2. this suggestion doesn't work:

It appears: parser: parse error at or near "exec" or 
parser: parse error at or near "execute"

And how should i design the update command in the suggested way e.g.?

EXEC ''UPDATE '' ||$1 
                      ''SET '' || $2 '' = psr_rec.parentoid
                        WHERE chilioid = psr_rec.childoid;''


Wich exact release I will need to use this feature?
Wich one are You using?

Greetings

David



**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************
Tue, 6 Feb 2001 13:16:06 -0000 
Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
RE: 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

Tue, 06 Feb 2001 11:16:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [SQL] PL/PGSQL function with parameters 
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 information technology      josh@agliodbs.com
    and data management solutions       (415) 565-7293
   for law firms, small businesses       fax  621-2533
    and non-profit organizations.       San Francisco

Tue, 06 Feb 2001 09:45:58 -0800
Josh Berkus <josh@agliodbs.com>
Re: [SQL] PL/PGSQL function with parameters
> > 	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 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.


Cheers,

Florent


-- 
florent.guillaume@mail.com

Tue, 6 Feb 2001 22:15:22 +0100
Florent Guillaume <efgeor@noos.fr>
Re: [SQL] PL/PGSQL function with parameters
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

Tue, 06 Feb 2001 19:43:10 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: [SQL] PL/PGSQL function with parameters 
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


Thu, 8 Feb 2001 06:37:22 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: [SQL] PL/PGSQL function with parameters
Tom Lane wrote:
> 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.

    EXECUTE simply takes the string expression and throws it into
    SPI_exec() without parsing. Changing that for  7.1  is  *not*
    possible.

    The above can be accomplished by

        DECLARE
          ROW record;
          RES integer;
        BEGIN
          FOR ROW IN EXECUTE
              ''SELECT count(*) AS N FROM '' || $1
          LOOP
            RES := N;
          END LOOP;
          RETURN RES;
        END;

    Not  as  elegant  as  it  should  be,  but at least possible.
    There's much to be done for a future version of PL/pgSQL, but
    better  support  for  dynamic SQL needs alot of functionality
    added to the main parser and the SPI  manager  in  the  first
    place.


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


Thu, 8 Feb 2001 07:22:13 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: [HACKERS] Re: PL/PGSQL function with parameters
> 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

Thu, 08 Feb 2001 10:10:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: [SQL] PL/PGSQL function with parameters 
Tom, Jan,

> 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 ...

Well, given that between you and Jan you have addressed dynamic
querying, it seems that there is no point in tinkering further.  Always
great to find that a problem has already been solved.

If I wasn't up to my hairline in behind-schedule projects, I'd offer to
write this up for the User's Manual.  Actually, consider that a
medium-term commitment ... before the end of the year, I'll write a much
longer PL/pgSQL chapter which Jan can review & correct.  (I think I'm in
a postion to do so, as the current app uses a large assortment of
PL/pgSQL functions as pseudo-middleware).

-Josh Berkus

-- 
______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology      josh@agliodbs.com
    and data management solutions       (415) 565-7293
   for law firms, small businesses       fax  621-2533
    and non-profit organizations.       San Francisco

Thu, 08 Feb 2001 08:45:23 -0800
Josh Berkus <josh@agliodbs.com>
Re: PL/PGSQL function with parameters