RE: plpgsql grief - Mailing list pgsql-sql

<p><font size="2">Hi, Rob,</font><p><font size="2">From the conversation taht Tom Lane got involved with earlier after
mylast posting on this toping I think that you need to change your first function to this:</font><p><font
size="2">createfunction testfunc (text) returns int4 as '</font><br /><font size="2">declare</font><br /><font
size="2"> sql varchar;</font><br /><font size="2">begin</font><br /><font size="2">  sql=''SELECT id AS res2 FROM
''||$1;</font><br /><font size="2">  execute sql ;</font><br /><font size="2">  return res2;</font><br /><font
size="2">end;</font><br/><font size="2">' language 'plpgsql' ;</font><p><font size="2">Please note the AS syntax rather
thanINTO.  This was a misconception on my part, which has obviously lasted longer than I'd hoped ;-)</font><p><font
size="2">createfunction update_trans (text, integer, text, text, text, text,</font><br /><font size="2">text) returns
booleanas '</font><br /><font size="2">declare</font><br /><font size="2">  sql varchar;</font><br /><font size="2"> 
tblalias for $1 ;</font><br /><font size="2">begin</font><br /><font size="2">  sql = ''insert into ''||$tbl||''(objid,
objtbl,et, event, time, reason,</font><br /><font size="2">owner) values (''||$2||'', ''||$3||'', ''||$4||'',
''||$5||'',now(), ''||$6||'', ''||$7||'')'';</font><br /><font size="2">  execute sql;</font><br /><font size="2"> 
return0;</font><br /><font size="2">end;</font><br /><font size="2">' language 'plpgsql' ;</font><br /><p><font
size="2">You'reforgetting that you have to unquote your variables, because you are constructing a string, and then
executingthat.  Using a varchar called SQL is a good habit for debugging.  It separates constructing the string from
executingthe query.</font><p><font size="2">Cheers...</font><br /><p><font size="2">MikeA</font><br /><br /><p><font
size="2">-----OriginalMessage-----</font><br /><font size="2">From: rob [<a
href="mailto:rob@dsvr.net">mailto:rob@dsvr.net</a>]</font><br/><font size="2">Sent: 08 February 2001 16:48</font><br
/><fontsize="2">To: pgsql-sql@postgresql.org</font><br /><font size="2">Subject: [SQL] plpgsql grief</font><br
/><p><fontsize="2">Hi, I'm having some real headache problems here. Apologies for the</font><br /><font
size="2">length,i just want to get it all out now :)</font><p><font size="2">I figured moving some 'simple' db code
frommy application to it's more</font><br /><font size="2">natural home in the db would work out. Bummer. Not only do i
haveto run</font><br /><font size="2">7.1 (beta 4) to be able to dynamically generate queries, I'm finding it</font><br
/><fontsize="2">*extrememly* difficult to get to get my simple functions to work (plus</font><br /><font size="2">for
the'widest used open source db' i'm finding examples very hard to</font><br /><font size="2">come by)</font><p><font
size="2">BeforeI start if anyone has any pointers to coding examples (and I mean</font><br /><font size="2">a little
morethan the standard postgres docs :) I'd be eternally</font><br /><font size="2">greatful. Failing that, can anyone
helpwith these two simple (ahem)</font><br /><font size="2">codelets :</font><p><font size="2">Example 1
:</font><p><fontsize="2">create function testfunc (text) returns int4 as '</font><br /><font size="2">declare</font><br
/><fontsize="2">  sql varchar;</font><br /><font size="2">  res int4;</font><br /><font size="2">begin</font><br
/><fontsize="2">  sql=''SELECT INTO res2 id FROM ''||$1 ;</font><br /><font size="2">  execute sql ;</font><br /><font
size="2"> return res;</font><br /><font size="2">end;</font><br /><font size="2">' language 'plpgsql' ;</font><p><font
size="2">simplefunction to return the id field of a table (passed to the</font><br /><font size="2">function). ok, not
areal world example, however i do this :</font><p><font size="2">#select testfunc('tablenam') ;</font><br /><font
size="2">andi get</font><br /><font size="2">ERROR:  parser: parse error at or near "into"</font><p><font size="2">ok
thisis actually first things last. I'm not really bothered about</font><br /><font size="2">returing values into local
variablesand then returning them, it's just</font><br /><font size="2">a run through. If I can't get this right, what
chancehave i got at</font><br /><font size="2">sorting out the real work i want to do. </font><p><font size="2">Example
2:</font><p><font size="2">create function update_trans (text, integer, text, text, text, text,</font><br /><font
size="2">text)returns boolean as '</font><br /><font size="2">declare</font><br /><font size="2">  tbl alias for $1
;</font><br/><font size="2">begin</font><br /><font size="2">  execute ''insert into tbl (objid, objtbl, et, event,
time,reason,</font><br /><font size="2">owner) values ($2, $3, $4, $5, now(), $6, $7)'';</font><br /><font size="2"> 
return0;</font><br /><font size="2">end;</font><br /><font size="2">' language 'plpgsql' ;</font><br /><p><font
size="2">#select update_trans('tablname','1' </font><br /><font
size="2">,'sometext','sometext','sometext','sometext','sometext');</font><br /><font size="2">ERROR:  Relation 'tbl'
doesnot exist</font><p><font size="2">dur. yeah i know it doesn't exist cause i want to pass it in parameter</font><br
/><fontsize="2">1.  Tried substituting tbl with $1 and quote_ident($1) and</font><br /><font size="2">quote_ident(tbl)
inthe sql string, but that didn't work either. (BTW</font><br /><font size="2">anyone know of any GUI interfaces that
support7.1 - phpPgAdmin 2.1,</font><br /><font size="2">2.2.1 and 2.3 seem to balk on functions)</font><p><font
size="2">Example2 is prelude to a larger function (not much larger - but then</font><br /><font size="2">this is
relavitveto how easy to code it is) to monitor the changes made</font><br /><font size="2">by a user, what they change
fromand to and who/when/why.... this is</font><br /><font size="2">already implemented in my app code - PHP - and
checkingout the features</font><br /><font size="2">available in postgres i figured i could do some kind of looping
through</font><br/><font size="2">the OLD and NEW dataset-array things, comparing them against each other,</font><br
/><fontsize="2">sorta like this :</font><p><font size="2">for ($i = 0 ; $i < count($NEW) ; $i++) {</font><br /><font
size="2">/*since $NEW and $OLD are essentially the same we can do this */</font><br /><font size="2">  if ($OLD[$i] !=
$NEW[$i])</font><br/><font size="2">    record the change bla bla bla</font><p><font size="2">}</font><br /><font
size="2">I'mreally hoping I can, as at this rate I've spent the better part of</font><br /><font size="2">three days
tryingto figure the simple things above out and the only</font><br /><font size="2">thing i'm about to reach is
breakingpoint...</font><p><font size="2">Sorry for the sarcasm, I'm about to pop.</font><p><font
size="2">Rob</font><code><fontsize="3"><br /><br />
**********************************************************************<br/> This email and any files transmitted with
itare confidential and<br /> intended solely for the use of the individual or entity to whom they<br /> are addressed.
Ifyou have received this email in error please notify<br /> Nick West - Global Infrastructure Manager.<br /><br /> This
footnotealso confirms that this email message has been swept by<br /> MIMEsweeper for the presence of computer
viruses.<br/><br /> www.mimesweeper.com<br /> **********************************************************************<br
/></font></code>