Thread: 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@aaanet.ru
> http://tolikus.hq.aaanet.ru:8080 Phone:
> (8632)-710071
>
<snip> > > 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: <snip> Ya know, i already read this one. That's what got me on to 7.1 and using EXECUTE :) ARgh! Apparenty I've opened up a real can of worms with wanting cool general functions, with a bit of dynamism and business logic. However.... OK dumped pl/pgsql, thinking pl/tcl is more my bag (didn't fancy recompiling perl to get the shared lib, and didn't want to waste much time struggling to somehow see if PHP could be used, since PHP is my current 'main' lang). Pl/tcl supports dynamic queries - great !. However it introduced it's own little wrinkles. Now for starters I've no knowledge of tcl, however doing my job means learning loads of exteranous crap, and what another lang... I reckon I can do it, just need a little help. So here goes. found out some things too - trigger functions must return opaque (ok not 100% on what opaque is, but I'm not worrying about that just yet), also can't have parameters in the function name - odd, but this lead on to - how the hell would you pass the parameters to the func anyway if it's attached to a trigger - like INSERT - and you merely type the SQL : insert into tablename values bla blabla ; and invoke the trigger, which inturn invokes the function ... erm there - wheres the transport to passing the parameters (sorry i know my programmers lingo isn't 100%). here's my tcl'ed function, which i attached to my main table on INSERT. It's supposed to log the action to another table passed to it - the idea is i've generalised some logging tables to various 'main' tables, and these functions are supposed to record various db and system level events. The function represents a bit of copying and playing - duno what TPL is however it works. I'll address further issues, error checking, processing etc when I've got it actually doing something : create function update_trans () returns opaque as ' spi_exec -array TPL "insert into $1 (objid, objtbl, et, event, time, reason, owner) values (\'$2\', \'$3\', \'$4\', \'$5\', now(), \'$6\', \'$7\')" ' language 'pltcl' ; which works !! well, gets invoked however it doesn't know what 'text' is. Not sure where that got picked up from, but it obviously didn't work - however the insert did. Now I found out there's a parameter array tgargs or something, but how does this get set ? How does it then get accessed in the function ? OK, being the resourceful chap I am (relatively) how about this, a slight diversion. Why not just make up some function which do the main insert, business logic, event logging stuff explicity and sack off doing sql inserts/update etcs. (can't return opaque here, but that's no bother, i think (hope)) New function create function update_trans (text, int4, text, text, text, text, text) returns boolean as ' spi_exec -array TPL "insert into $1 (objid, objtbl, et, event, time, reason, owner) values (\'$2\', \'$3\', \'$4\', \'$5\', now(), \'$6\', \'$7\')" return1 ' language 'pltcl' ; then call these from my code like select update_trans (bla, 1, bla, bla blabl) ; which works also. I get to pass all the parameters i want, and have full control over execution. OK this looses part of the reason for doing this in the first place - tracking people who side track the app code by modifying the db directly, however since noone should be doing that anyway, no problem. (note should). Again apologies for the verbose message - i feel the 'fuller picture' is more useful in the long run, rather than diconnected questions. Well it is to me when I'm on your side of the fence. Thanks for the reply BTW. Oh, and why is this news group only accessible during late afternoon from 3pm'ish GMT. I'm access it from the UK. All morning, for two days, I couldn't get on - server busy errors. Oh and before I forget - over several months of news group postings there has been the recognision of the need for examples for us newies, and some mention of people compiling various docs for just such a purpose - anyone get anywhere with any of these, as they were several months ago. I'm certainly gaining some real gotcha type info on all of this :) Now I've had it. Burned out. So off to the pub and henceforth become as drunk as a skunk ! Regards Rob
Tcl is my bread and butter but, coincidentally, I have just started considering pl/tcl 2 days ago as the choice for server side pg programming. I do it in microsoft t-sql right now, and plsql is pretty close to that. However, tcl is like English to me, so I think I will go that way unless someone can tell me why I shouldn't. I found that the docs are actually pretty excellent, although brief, on pl/tcl. Tcl is a strange animal, so you will have to get used to 'lists' and occasional unwanted/unexpected variable substitution until you get the hang of it. But if you scan the docs you will find some interesting stuff. It seems they have provided everything you and I will ever need, we just need to figure out how to use it. The docs are in USER versus PROGRAMMER. Go figure. There is a newsgroup called 'novice' that is useful from time to time. I don't know why the server has had such availability issues the last couple days, it is usually very good. Have fun, Ian rob wrote: > <snip> > > > > 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: > <snip> > Ya know, i already read this one. That's what got me on to 7.1 and using > EXECUTE :) > > ARgh! Apparenty I've opened up a real can of worms with wanting cool > general functions, with a bit of dynamism and business logic. > However.... > > OK dumped pl/pgsql, thinking pl/tcl is more my bag (didn't fancy > recompiling perl to get the shared lib, and didn't want to waste much > time struggling to somehow see if PHP could be used, since PHP is my > current 'main' lang). Pl/tcl supports dynamic queries - great !. However > it introduced it's own little wrinkles. Now for starters I've no > knowledge of tcl, however doing my job means learning loads of > exteranous crap, and what another lang... I reckon I can do it, just > need a little help. So here goes. > > found out some things too - trigger functions must return opaque (ok not > 100% on what opaque is, but I'm not worrying about that just yet), also > can't have parameters in the function name - odd, but this lead on to - > how the hell would you pass the parameters to the func anyway if it's > attached to a trigger - like INSERT - and you merely type the SQL : > > insert into tablename values bla blabla ; > > and invoke the trigger, which inturn invokes the function ... erm there > - wheres the transport to passing the parameters (sorry i know my > programmers lingo isn't 100%). > > here's my tcl'ed function, which i attached to my main table on INSERT. > It's supposed to log the action to another table passed to it - the idea > is i've generalised some logging tables to various 'main' tables, and > these functions are supposed to record various db and system level > events. The function represents a bit of copying and playing - duno what > TPL is however it works. I'll address further issues, error checking, > processing etc when I've got it actually doing something : > > create function update_trans () returns opaque as ' > spi_exec -array TPL "insert into $1 (objid, objtbl, et, event, time, > reason, owner) values (\'$2\', \'$3\', \'$4\', \'$5\', now(), \'$6\', > \'$7\')" > ' language 'pltcl' ; > > which works !! well, gets invoked however it doesn't know what 'text' > is. Not sure where that got picked up from, but it obviously didn't work > - however the insert did. Now I found out there's a parameter array > tgargs or something, but how does this get set ? How does it then get > accessed in the function ? > > OK, being the resourceful chap I am (relatively) how about this, a > slight diversion. Why not just make up some function which do the main > insert, business logic, event logging stuff explicity and sack off doing > sql inserts/update etcs. > > (can't return opaque here, but that's no bother, i think (hope)) > > New function > > create function update_trans (text, int4, text, text, text, text, text) > returns boolean as ' > spi_exec -array TPL "insert into $1 (objid, objtbl, et, event, time, > reason, owner) values (\'$2\', \'$3\', \'$4\', \'$5\', now(), \'$6\', > \'$7\')" > return1 > ' language 'pltcl' ; > > then call these from my code like > > select update_trans (bla, 1, bla, bla blabl) ; > > which works also. I get to pass all the parameters i want, and have full > control over execution. OK this looses part of the reason for doing this > in the first place - tracking people who side track the app code by > modifying the db directly, however since noone should be doing that > anyway, no problem. (note should). > > Again apologies for the verbose message - i feel the 'fuller picture' is > more useful in the long run, rather than diconnected questions. Well it > is to me when I'm on your side of the fence. > > Thanks for the reply BTW. Oh, and why is this news group only accessible > during late afternoon from 3pm'ish GMT. I'm access it from the UK. All > morning, for two days, I couldn't get on - server busy errors. > > Oh and before I forget - over several months of news group postings > there has been the recognision of the need for examples for us newies, > and some mention of people compiling various docs for just such a > purpose - anyone get anywhere with any of these, as they were several > months ago. I'm certainly gaining some real gotcha type info on all of > this :) > > Now I've had it. Burned out. So off to the pub and henceforth become as > drunk as a skunk ! > > Regards > > Rob