Thread: 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
<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>
Rob,
> 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)
<rant>
Keep in mind that Open Source usually means DIY as well, or
it wouldn't be free. If you have mission-critical problems,
pay-for support is available from two companies.
As for the PL/pgSQL documentation, everyone acknowledges
it's skimpy at best. Several of us PL/pgSQL users plan to
write up more extensive docs *when we have time*. The doc
writers will be volunteers, so don't hold your breath.
And, as another developer pointed out, the EXECUTE
functionality already goes beyond the scope of Microsoft's
Transact SQL, a $1000=$10,000 + product.
</rant>
In the meantime:
> 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' ;
SELECT INTO functionality is being dropped from EXECUTE. If
you're interested in the reasons why, we've been discussing
it on the list for the last 2 weeks; leaf throud the
archives.
The main restriction is this: EXECUTE passes the query to a
seperate sub-process, and as such you may not pass *any*
unexpanded variables into the EXECUTE statement. Within
EXECUTE, those variables are out of scope.
Thus your only way to get stuff back from EXECUTE is to save
the results you want to a temporary table (using CREATE
TABLE AS ...), and read them back using a query. Not
high-performance, but it gets the job done.
Tom Lane and Jan Wieck have suggested that we might have
more flexible dynamic query generation for 7.2, but that's a
ways off.
Thus, your second function should be:
> 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 || '', current_timestamp, '' || $6 || '',
'' || $7 || '')'';
> return TRUE;
> end;
> ' language 'plpgsql' ;
With adjustments made to the syntax for data type delimiters
and replacing any nulls with the work NULL (and keep in mind
that Postgres functions currently have trouble with NULLS as
input parameters).
The rest is up to you ... or hire an expert.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
> Thus your only way to get stuff back from EXECUTE is to save > the results you want to a temporary table (using CREATE > TABLE AS ...), and read them back using a query. Not > high-performance, but it gets the job done. > I believe this statement is incorrect, quoting Michael Ansley <Michael.Ansley@intec-telecom-systems.com> from a previous e-mail: <QUOTE> 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 ;-) </QUOTE> I believe this was the consensus reached on the hacker's list..... Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio
"Josh Berkus" <josh@agliodbs.com> writes:
> Thus your only way to get stuff back from EXECUTE is to save
> the results you want to a temporary table (using CREATE
> TABLE AS ...), and read them back using a query. Not
> high-performance, but it gets the job done.
That's not the only way; you can also use FOR ... EXECUTE, as Jan
pointed out awhile back in the other thread.
What does not work at the moment is to EXECUTE a 'SELECT INTO',
because EXECUTE just hands the string off to the main SQL parser
which knows nothing about plpgsql variables. We'll try to improve
this for 7.2, but it's far too late to get it done for 7.1.
> Thus, your second function should be:
>> 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 || '', current_timestamp, '' || $6 || '',
> '' || $7 || '')'';
>> return TRUE;
>> end;
>> ' language 'plpgsql' ;
> With adjustments made to the syntax for data type delimiters
> and replacing any nulls with the work NULL.
Hm, good point; coping with NULLs in this context will require some
explicit programming. Yech. I'd recommend using quote_string for
the TEXT parameters, but that doesn't help any for the NULL case.
(I wonder if quote_string should be defined to return 'NULL' for
a NULL input? Again, too late for 7.1, but seems like a good future
improvement.)
> (and keep in mind
> that Postgres functions currently have trouble with NULLS as
> input parameters).
Not in 7.1 they don't ...
regards, tom lane
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
Rob, >Just need the info - i can do the rest. I also, due to my > business requirements, need to do this as quickly as possible - maybe > not the expert you perhaps are <grin> No expert at all, according to Tom Lane. > As it goes I've implemented most of what I wanted in pl/tcl (having > learned tcl today). That's all i need, and it makes me most happy :). That's great! If I need to hire a pl/tcl expert, I now know who to e-mail ... While you're at it, how about writing a 2-5 page pl/tcl HOWTO to help others who follow in your (frustrated) footsteps? > Just thought the built in procedure implmentation would be quite mature > by now. Implementation is in its adolescence/young adulthood. Documentation is still in its early childhood. We (the user base) will fix that, but not this month. -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
<p><font size="2">I thought that the discussion on this topic resolved that the AS syntax would work as I described, andthe INTO syntax would be removed because of ambiguity, to be redeveloped at a later date?</font><p><font size="2">-----OriginalMessage-----</font><br /><font size="2">From: Tom Lane [<a href="mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</a>]</font><br/><font size="2">Sent: 12 February 2001 16:46</font><br/><font size="2">To: Michael Ansley</font><br /><font size="2">Cc: 'rob'; pgsql-sql@postgresql.org</font><br/><font size="2">Subject: Re: [SQL] plpgsql grief </font><br /><p><font size="2">MichaelAnsley <Michael.Ansley@intec-telecom-systems.com> writes:</font><br /><font size="2">> create functiontestfunc (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 ASsyntax rather than INTO.</font><p><font size="2">That won't work :-(</font><p> <font size="2">regards,tom lane</font><code><font size="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>
Tom, > > (and keep in mind > > that Postgres functions currently have trouble with NULLS as > > input parameters). > > Not in 7.1 they don't ... Really? Terrific. Sadly, I have 25+ PL/pgSQL functions not set up to accept NULLs ... Can you point me to (or tell me where to search) the developer notes on what improvements have been made in the function parser so that I can take advantage of them? -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Michael, > 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? ************************* Tom (I believe) was referring to CREATE TABLE AS as a way to insert queries into temporary tables. For better results, check out Jan's syntax for FOR ... EXECUTE in his last post. -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Michael Ansley <Michael.Ansley@intec-telecom-systems.com> writes:
> 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?
INTO has indeed been removed. However, AS does not do what you seem
to think it does; in fact, it is entirely a noise clause in this
context.
regards, tom lane