Re: plpgsql grief - Mailing list pgsql-sql

rob wrote:

> Hi, I'm having some real headache problems here. Apologies for the
> length, i just want to get it all out now :)
>
> I figured moving some 'simple' db code from my application to it's more
> natural home in the db would work out. Bummer. Not only do i have to run
> 7.1 (beta 4) to be able to dynamically generate queries, I'm finding it
> *extrememly* difficult to get to get my simple functions to work (plus
> for the 'widest used open source db' i'm finding examples very hard to
> come by)
>
> Before I start if anyone has any pointers to coding examples (and I mean
> a little more than the standard postgres docs :) I'd be eternally
> greatful. Failing that, can anyone help with these two simple (ahem)
> codelets :
>
> Example 1 :
>
> create function testfunc (text) returns int4 as '
> declare
>   sql varchar;
>   res int4;
> begin
>   sql=''SELECT INTO res2 id FROM ''||$1 ;
>   execute sql ;
>   return res;
> end;
> ' language 'plpgsql' ;
>
> simple function to return the id field of a table (passed to the
> function). ok, not a real world example, however i do this :
>
> #select testfunc('tablenam') ;
> and i get
> ERROR:  parser: parse error at or near "into"
>
> ok this is actually first things last. I'm not really bothered about
> returing values into local variables and then returning them, it's just
> a run through. If I can't get this right, what chance have i got at
> sorting out the real work i want to do.
>
> Example 2 :
>
> create function update_trans (text, integer, text, text, text, text,
> text) returns boolean as '
> declare
>   tbl alias for $1 ;
> begin
>   execute ''insert into tbl (objid, objtbl, et, event, time, reason,
> owner) values ($2, $3, $4, $5, now(), $6, $7)'';
>   return 0;
> end;
> ' language 'plpgsql' ;
>
> # select update_trans('tablname','1'
> ,'sometext','sometext','sometext','sometext','sometext') ;
> ERROR:  Relation 'tbl' does not exist
>
> dur. yeah i know it doesn't exist cause i want to pass it in parameter
> 1.  Tried substituting tbl with $1 and quote_ident($1) and
> quote_ident(tbl) in the sql string, but that didn't work either. (BTW
> anyone know of any GUI interfaces that support 7.1 - phpPgAdmin 2.1,
> 2.2.1 and 2.3 seem to balk on functions)
>
> Example 2 is prelude to a larger function (not much larger - but then
> this is relavitve to how easy to code it is) to monitor the changes made
> by a user, what they change from and to and who/when/why.... this is
> already implemented in my app code - PHP - and checking out the features
> available in postgres i figured i could do some kind of looping through
> the OLD and NEW dataset-array things, comparing them against each other,
> sorta like this :
>
> for ($i = 0 ; $i < count($NEW) ; $i++) {
> /* since $NEW and $OLD are essentially the same we can do this */
>   if ($OLD[$i] != $NEW[$i])
>     record the change bla bla bla
>
> }
> I'm really hoping I can, as at this rate I've spent the better part of
> three days trying to figure the simple things above out and the only
> thing i'm about to reach is breaking point...
>
> Sorry for the sarcasm, I'm about to pop.
>
> Rob

I feel your pain;^)

Here is the text of a post from Tuesday... I think it answers your question
which is that you cannot do variable subsititution for table or field names
inside procedures.  This is not a Postgres specific limitation, MS SQL
Server has the same issue.


> >>>>> "DR" == David Richter
>  writes:
>
>  DR> Folks,
>  DR> I wrote that function, wich doesn't
> work. I want to hand over the name
>  DR> of the tables(relation_table,
> update_table) and a
>  DR> column(column_to_fill). The intention
> is, to use the function also with
>  DR> 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 the
>  DR> Docu of www.postgresql.org and Bruce
> Monjiam's Book) about parameters in
>  DR> PL/PGSQL - functions.
>  DR> I would be no less grateful if anybody
> give detailed suggestions.
>
>  DR> CREATE FUNCTION
> patient_study_restructure (text,text,text)
> RETURNS
>  DR> 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> bound integer;
>  DR> i integer := 0;
>
>  DR> BEGIN
>  DR> FOR psr_rec IN SELECT * FROM
> relation_table LOOP
>  DR> UPDATE update_table
>  DR> SET column_to_fill = psr_rec.parentoid
>  DR> WHERE chilioid = psr_rec.childoid;
>  DR> i := i + 1;
>  DR> END LOOP;
>  DR> IF NOT FOUND THEN RETURN 1;
>  DR> ELSE RETURN i;
>  DR> END IF;
>  DR> END;
>
>  DR> ' LANGUAGE 'plpgsql';
>
>  DR> SELECT
>  DR>
> patient_study_restructure('relpatient_study000','study','patientoid');
>
>
>  DR> Anybody (Jan Wieck?) who can make some
> sugestions on
>  DR> the above will
>  DR> 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 during the life of
> the server. Instead, the query is prepared
> each time the
>     statement is run. The query-string can be
> dynamically created 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 table identifiers should be passed
> to
>     function quote_ident(). Variables
> containing literal elements of the dynamic
> query string
>     should be passed to quote_literal(). Both
> take the appropriate steps to return the
> input
>     text enclosed in single 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
>