Re: plpgsql grief

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(at)aaanet(dot)ru
> http://tolikus.hq.aaanet.ru:8080 Phone:
> (8632)-710071
>