Re: plpgsql grief

Hi, Rob,

From the conversation taht Tom Lane got involved with earlier after my last posting on this toping I think that you need to change your first function to this:

create function testfunc (text) returns int4 as '
declare
  sql varchar;
begin
  sql=''SELECT id AS res2 FROM ''||$1 ;
  execute sql ;
  return res2;
end;
' language 'plpgsql' ;

Please note the AS syntax rather than INTO.  This was a misconception on my part, which has obviously lasted longer than I'd hoped ;-)

create function update_trans (text, integer, text, text, text, text,
text) returns boolean as '
declare
  sql varchar;
  tbl alias for $1 ;
begin
  sql = ''insert into ''||$tbl||''(objid, objtbl, et, event, time, reason,
owner) values (''||$2||'', ''||$3||'', ''||$4||'', ''||$5||'', now(), ''||$6||'', ''||$7||'')'';
  execute sql;
  return 0;
end;
' language 'plpgsql' ;

You're forgetting that you have to unquote your variables, because you are constructing a string, and then executing that.  Using a varchar called SQL is a good habit for debugging.  It separates constructing the string from executing the query.

Cheers...

MikeA

-----Original Message-----
From: rob [mailto:rob@dsvr.net]
Sent: 08 February 2001 16:48
To: pgsql-sql@postgresql.org
Subject: [SQL] plpgsql grief

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

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************


I thought that the discussion on this topic resolved that the AS syntax would work as I described, and the INTO syntax would be removed because of ambiguity, to be redeveloped at a later date?

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 12 February 2001 16:46
To: Michael Ansley
Cc: 'rob'; pgsql-sql@postgresql.org
Subject: Re: [SQL] plpgsql grief

Michael Ansley <Michael.Ansley@intec-telecom-systems.com> writes:
> create function testfunc (text) returns int4 as '
> declare
>   sql varchar;
> begin
>   sql=''SELECT id AS res2 FROM ''||$1 ;
>   execute sql ;
>   return res2;
> end;
> ' language 'plpgsql' ;

> Please note the AS syntax rather than INTO.

That won't work :-(

                        regards, tom lane

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************