Thread: PL/PGSQL function with parameters

PL/PGSQL function with parameters

From
David Richter
Date:
Folks,

I wrote that function, wich doesn't work. I want to hand over the name
of the tables(relation_table, update_table) and a
column(column_to_fill). The intention is, to use the function also with
other tables(not hard coded). 

BUT this error appears :psql:restructure.sql:32: ERROR:  parser: parse error at or near "$1"

I didn't found any solution. 
I would be grateful , if I could get some more Examples(more than in the
Docu of www.postgresql.org and Bruce Monjiam's Book) about parameters in
PL/PGSQL - functions.
I would be no less grateful if anybody give detailed suggestions.

CREATE FUNCTION patient_study_restructure (text,text,text) RETURNS
integer AS '       DECLARE                                       relation_table ALIAS FOR $1;
update_tableALIAS FOR $2;               column_to_fill ALIAS FOR $3;               psr_rec record;               bound
integer;              i integer := 0;
 

BEGIN        FOR psr_rec IN SELECT * FROM relation_table LOOP               UPDATE update_table
SETcolumn_to_fill = psr_rec.parentoid                       WHERE chilioid = psr_rec.childoid;               i := i +
1;      END LOOP;       IF NOT FOUND THEN RETURN 1;                ELSE RETURN i;       END IF;
 
END;

' LANGUAGE 'plpgsql';

SELECT
patient_study_restructure('relpatient_study000','study','patientoid');


Anybody (Jan Wieck?) who can make some sugestions on
the above will
receive my enthusiastic gratitude.

David


Re: PL/PGSQL function with parameters

From
"Josh Berkus"
Date:
Mr. Richter,

> I wrote that function, wich doesn't work. I want to hand
> over the name
> of the tables(relation_table, update_table) and a
> column(column_to_fill). The intention is, to use the
> function also with
> other tables(not hard coded). 

1. Try using type VARCHAR instead of TEXT for the
parameters.  PL/pgSQL may be objecting to the "undefined"
size of TEXT.

2. I don't believe that you can supply variables in the
place of object names in PL/pgSQL, only in place of values.
Thus, "SELECT * FROM table_name" would be invalid.
(Jan, please tall me if I'm wrong in this; I could really
use the functionality if it *is* possible.)
You could use some creative manipulation of the system
tables to achieve the same result, however.

-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
 


Re: PL/PGSQL function with parameters

From
Josh Berkus
Date:
Mike, Jan,

> Michael Ansley wrote:
> 
> With the latest release, I think you can do:
> 
> EXEC ''SELECT * FROM '' || $1;
> 
> or
> 
> DECLARE SQL VARCHAR;
> ...
> SQL = ''SELECT * FROM '' || $1;
> EXEC SQL;
> 
> or something similar (it may be EXECUTE), which uses the dynamic sql
> elements of plpgsql.  I think.

Is this true, Jan?  Does anyone have more specific documentation?
                -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
 


Re: PL/PGSQL function with parameters

From
Kovacs Zoltan
Date:
Unfortunately you cannot use parameters as you like. The FROM clause
cannot contain a parameter. It must be constant.

Zoltan

--                         Kov\'acs, Zolt\'an                        kovacsz@pc10.radnoti-szeged.sulinet.hu
          http://www.math.u-szeged.hu/~kovzol                        ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz
 




RE: PL/PGSQL function with parameters

From
Michael Ansley
Date:
<p><font size="2">Just for the record:</font><p><font size="2">DROP FUNCTION table_count(varchar);</font><br /><font
size="2">CREATEFUNCTION table_count(varchar) RETURNS integer AS '</font><br /><font size="2">DECLARE</font><br
/>       <font size="2">SQL varchar;</font><br />        <font size="2">RES integer;</font><br /><font
size="2">BEGIN</font><br/>        <font size="2">SQL = ''SELECT * INTO temp1 FROM '' || $1;</font><br />        <font
size="2">EXECUTESQL;</font><br />        <font size="2">SELECT count(*) INTO RES FROM temp1;</font><br />        <font
size="2">RETURN(RES)</font><br/><font size="2">END;</font><br /><font size="2">'</font><br /><font size="2">LANGUAGE
'plpgsql';</font><p><fontsize="2">...</font><br /><p><font size="2">dev=> select table_count('switch');</font><br
/><fontsize="2"> test</font><br /><font size="2">------</font><br /><font size="2">    6</font><br /><font size="2">(1
row)</font><br/><p><font size="2">This function produces exactly what you would hope for, a count of rows in the
specifiedtable.  It's particularly inefficient at doing it, because it does a table copy (and doesn't bother to clean
upafter itself ;-(), so don't do this on a large table ;-) but it shows the principle.</font><p><font size="2">What I
couldn'tget it to do was to select directly into the variable RES.  Perhaps someone could enlighten me.</font><p><font
size="2">Cheers...</font><br/><p><font size="2">MikeA</font><p><font size="2">-----Original Message-----</font><br
/><fontsize="2">From: David Richter [<a
href="mailto:D.Richter@DKFZ-heidelberg.de">mailto:D.Richter@DKFZ-heidelberg.de</a>]</font><br/><font size="2">Sent: 06
February2001 09:39</font><br /><font size="2">To: Michael Ansley</font><br /><font size="2">Subject: Re: [SQL] PL/PGSQL
functionwith parameters</font><br /><p><font size="2">Hello!</font><p><font size="2">Thanks a lot for Your
answer!</font><p><fontsize="2">But with my version 7.0.2. this suggestion doesn't work:</font><p><font size="2">It
appears:parser: parse error at or near "exec" or </font><br /><font size="2">parser: parse error at or near
"execute"</font><p><fontsize="2">And how should i design the update command in the suggested way e.g.?</font><p><font
size="2">EXEC''UPDATE '' ||$1 </font><br /><font size="2">                      ''SET '' || $2 '' =
psr_rec.parentoid</font><br/><font size="2">                        WHERE chilioid = psr_rec.childoid;''</font><br
/><p><fontsize="2">Wich exact release I will need to use this feature?</font><br /><font size="2">Wich one are You
using?</font><p><fontsize="2">Greetings</font><p><font size="2">David</font><code><font size="3"><br /><br />
**********************************************************************<br/> This email and any files transmitted with
itare confidential and<br /> intended solely for the use of the individual or entity to whom they<br /> are addressed.
Ifyou have received this email in error please notify<br /> Nick West - Global Infrastructure Manager.<br /><br /> This
footnotealso confirms that this email message has been swept by<br /> MIMEsweeper for the presence of computer
viruses.<br/><br /> www.mimesweeper.com<br /> **********************************************************************<br
/></font></code>

Re: PL/PGSQL function with parameters

From
Tom Lane
Date:
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


RE: PL/PGSQL function with parameters

From
Michael Ansley
Date:
<p><font size="2">Yes, that was why I wrote it in the way that I did.  The table is effectively given a constant name,
andthe count is got from the table with a known name.  But of a kludge, but in 45sec, that was all I could come up with
;-)</font><p><fontsize="2">It would be VERY useful to see it fixed.</font><p><font size="2">Cheers...</font><br
/><p><fontsize="2">MikeA</font><br /><font size="2"> </font><p><font size="2">-----Original Message-----</font><br
/><fontsize="2">From: Tom Lane [<a href="mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</a>]</font><br /><font
size="2">Sent:06 February 2001 16:16</font><br /><font size="2">To: Michael Ansley</font><br /><font size="2">Cc: Jan
Wieck;sqllist; pgsql-hackers@postgresql.org</font><br /><font size="2">Subject: Re: [SQL] PL/PGSQL function with
parameters</font><br /><p><font size="2">Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
writes:</font><br/><font size="2">> CREATE FUNCTION table_count(varchar) RETURNS integer AS '</font><br /><font
size="2">>DECLARE</font><br /><font size="2">>       SQL varchar;</font><br /><font size="2">>       RES
integer;</font><br/><font size="2">> BEGIN</font><br /><font size="2">>       SQL = ''SELECT * INTO temp1 FROM ''
||$1;</font><br /><font size="2">>       EXECUTE SQL;</font><br /><font size="2">>       SELECT count(*) INTO RES
FROMtemp1;</font><br /><font size="2">>       RETURN(RES)</font><br /><font size="2">> END;</font><br /><font
size="2">>'</font><br /><font size="2">> LANGUAGE 'plpgsql';</font><p><font size="2">> What I couldn't get it
todo was to select directly into the variable RES.</font><p><font size="2">I tried this, and it seems that "SELECT ...
INTOfoo" is not executed</font><br /><font size="2">correctly by EXECUTE --- the INTO is handled as an ordinary
select-into-</font><br/><font size="2">table construct rather than plpgsql's select-into-variable.</font><p><font
size="2">WhileI have not looked closely, I seem to recall that plpgsql handles</font><br /><font size="2">INTO by
strippingthat clause out of the statement before it's passed to</font><br /><font size="2">the SQL engine.  Evidently
that'snot happening in the EXECUTE case.</font><p><font size="2">Jan, do you agree this is a bug?  Is it reasonable to
tryto repair it</font><br /><font size="2">for 7.1?  If we do not change the behavior of EXECUTE now, I fear
it</font><br/><font size="2">will be too late --- some people will come to depend on the existing</font><br /><font
size="2">behavior.</font><p>                       <font size="2">regards, tom lane</font><code><font size="3"><br
/><br/> **********************************************************************<br /> This email and any files
transmittedwith it are confidential and<br /> intended solely for the use of the individual or entity to whom they<br
/>are addressed. If you have received this email in error please notify<br /> Nick West - Global Infrastructure
Manager.<br/><br /> This footnote also confirms that this email message has been swept by<br /> MIMEsweeper for the
presenceof computer viruses.<br /><br /> www.mimesweeper.com<br />
**********************************************************************<br/></font></code> 

Re: PL/PGSQL function with parameters

From
Josh Berkus
Date:
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
 


Re: PL/PGSQL function with parameters

From
tolik@aaanet.ru (Anatoly K. Lasareff)
Date:
>>>>> "DR" == David Richter <d.richter@dkfz.de> writes:
DR> Folks,DR> I wrote that function, wich doesn't work. I want to hand over the nameDR> of the tables(relation_table,
update_table)and aDR> column(column_to_fill). The intention is, to use the function also withDR> other tables(not hard
coded).
 
DR> BUT this error appears :DR> psql:restructure.sql:32: ERROR:  parser: parse error at or near "$1"
DR> I didn't found any solution. DR> I would be grateful , if I could get some more Examples(more than in theDR> Docu
ofwww.postgresql.org and Bruce Monjiam's Book) about parameters inDR> PL/PGSQL - functions.DR> I would be no less
gratefulif anybody give detailed suggestions.
 
DR> CREATE FUNCTION patient_study_restructure (text,text,text) RETURNSDR> integer AS 'DR> DECLARE
DR> relation_table ALIAS FOR $1;DR> update_table ALIAS FOR $2;DR> column_to_fill ALIAS FOR $3;DR> psr_rec record;DR>
boundinteger;DR> i integer := 0;
 
DR> BEGIN DR> FOR psr_rec IN SELECT * FROM relation_table LOOPDR> UPDATE update_table DR> SET column_to_fill =
psr_rec.parentoidDR>WHERE chilioid = psr_rec.childoid;DR> i := i + 1;DR> END LOOP;DR> IF NOT FOUND THEN RETURN 1; DR>
ELSERETURN i;DR> END IF;DR> END;
 
DR> ' LANGUAGE 'plpgsql';
DR> SELECTDR> patient_study_restructure('relpatient_study000','study','patientoid');

DR> Anybody (Jan Wieck?) who can make some sugestions onDR> the above willDR> receive my enthusiastic gratitude.
DR> David

You _cannot_ use parameters value as table or column name inside
plpgsql function. So your construct SELECT * FROM relation_table (and
others similar) is wrong. The same in other words: you cannot make
dynamic queries by plpgsql. BUT! You can use EXECUTE statement which
exists in 7.1. Here is some doc:

EXECUTE {query-string}
   where query-string is a string of type TEXT containing the query to be executed. 
   Unlike all other queries in PL/pgSQL, a query run by an EXECUTE statement is not prepared   and saved just once
duringthe life of the server. Instead, the query is prepared each time the   statement is run. The query-string can be
dynamicallycreated within the procedure to   perform actions on variable tables and fields. 
 
   The results from SELECT queries are discarded by EXECUTE unless SELECT INTO is used to   save the results into a
table.
 
   An example: 
   EXECUTE ''UPDATE tbl SET ''           || quote_ident(fieldname)           || '' = ''           ||
quote_literal(newvalue)          || '' WHERE ...'';
 
   This example shows use of the functions quote_ident(TEXT) and   quote_literal(TEXT). Variables containing field and
tableidentifiers should be passed to   function quote_ident(). Variables containing literal elements of the dynamic
querystring   should be passed to quote_literal(). Both take the appropriate steps to return the input   text enclosed
insingle or double quotes and with any embedded special characters intact. 
 

-- 
Anatoly K. Lasareff                 Email:    tolik@aaanet.ru 
http://tolikus.hq.aaanet.ru:8080    Phone:      (8632)-710071


Re: [HACKERS] Re: PL/PGSQL function with parameters

From
Jan Wieck
Date:
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



Re: PL/PGSQL function with parameters

From
Jan Wieck
Date:
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



Re: [HACKERS] Re: PL/PGSQL function with parameters

From
Tom Lane
Date:
> 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


Re: PL/PGSQL function with parameters

From
Josh Berkus
Date:
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
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