plpgsql grief

From: rob <rob(at)dsvr(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: plpgsql grief
Date: 2001-02-08 16:47:49
Message-ID: 3A82CDB5.7C8653FC@dsvr.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Brice Ruth 2001-02-08 16:52:00 Re: [SQL] Query never returns ...
Previous Message Stephan Szabo 2001-02-08 16:47:38 Re: [SQL] Query never returns ...