plpgsql grief - Mailing list pgsql-sql
From | rob |
---|---|
Subject | plpgsql grief |
Date | |
Msg-id | 3A82CDB5.7C8653FC@dsvr.net Whole thread Raw |
Responses |
Re: plpgsql grief
|
List | pgsql-sql |
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