pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: rob <rob@dsvr.net>
To: pgsql-sql@postgresql.org
Subject: plpgsql grief
Date: Thu, 08 Feb 2001 16:47:49 +0000
Message-ID: <3A82CDB5.7C8653FC@dsvr.net> (raw)

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



view thread (14+ messages)  latest in thread

Message-ID: <3A82CDB5.7C8653FC@dsvr.net>
Permalink:  ../3A82CDB5.7C8653FC@dsvr.net/
Also on:    postgresql.org/message-id/3A82CDB5.7C8653FC@dsvr.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: rob@dsvr.net
  Subject: Re: plpgsql grief
  In-Reply-To: <3A82CDB5.7C8653FC@dsvr.net>
  References: <3A82CDB5.7C8653FC@dsvr.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox