pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql grief
14+ messages / 6 participants
[nested] [flat]
* plpgsql grief
@ 2001-02-08 16:47 rob <rob@dsvr.net>
2001-02-09 02:00 ` Re: plpgsql grief Ian Harding <iharding@pakrat.com>
2001-02-12 16:05 ` Re: plpgsql grief Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 14+ messages in thread
From: rob @ 2001-02-08 16:47 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql grief
2001-02-08 16:47 plpgsql grief rob <rob@dsvr.net>
@ 2001-02-09 02:00 ` Ian Harding <iharding@pakrat.com>
2001-02-09 17:14 ` Re: plpgsql grief rob <rob@dsvr.net>
1 sibling, 1 reply; 14+ messages in thread
From: Ian Harding @ 2001-02-09 02:00 UTC (permalink / raw)
To: pgsql-sql
rob wrote:
> 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
I feel your pain;^)
Here is the text of a post from Tuesday... I think it answers your question
which is that you cannot do variable subsititution for table or field names
inside procedures. This is not a Postgres specific limitation, MS SQL
Server has the same issue.
> >>>>> "DR" == David Richter
> writes:
>
> DR> Folks,
> DR> I wrote that function, wich doesn't
> work. I want to hand over the name
> DR> of the tables(relation_table,
> update_table) and a
> DR> column(column_to_fill). The intention
> is, to use the function also with
> DR> other tables(not hard coded).
>
> DR> BUT this error appears :
> DR> psql:restructure.sql:32: ERROR: parser:
> parse error at or near "$1"
>
> DR> I didn't found any solution.
> DR> I would be grateful , if I could get
> some more Examples(more than in the
> DR> Docu of www.postgresql.org and Bruce
> Monjiam's Book) about parameters in
> DR> PL/PGSQL - functions.
> DR> I would be no less grateful if anybody
> give detailed suggestions.
>
> DR> CREATE FUNCTION
> patient_study_restructure (text,text,text)
> RETURNS
> DR> integer AS '
> DR> DECLARE
>
> DR> relation_table ALIAS FOR $1;
> DR> update_table ALIAS FOR $2;
> DR> column_to_fill ALIAS FOR $3;
> DR> psr_rec record;
> DR> bound integer;
> DR> i integer := 0;
>
> DR> BEGIN
> DR> FOR psr_rec IN SELECT * FROM
> relation_table LOOP
> DR> UPDATE update_table
> DR> SET column_to_fill = psr_rec.parentoid
> DR> WHERE chilioid = psr_rec.childoid;
> DR> i := i + 1;
> DR> END LOOP;
> DR> IF NOT FOUND THEN RETURN 1;
> DR> ELSE RETURN i;
> DR> END IF;
> DR> END;
>
> DR> ' LANGUAGE 'plpgsql';
>
> DR> SELECT
> DR>
> patient_study_restructure('relpatient_study000','study','patientoid');
>
>
> DR> Anybody (Jan Wieck?) who can make some
> sugestions on
> DR> the above will
> DR> receive my enthusiastic gratitude.
>
> DR> David
>
> You _cannot_ use parameters value as table or
> column name inside
> plpgsql function. So your construct SELECT *
> FROM relation_table (and
> others similar) is wrong. The same in other
> words: you cannot make
> dynamic queries by plpgsql. BUT! You can use
> EXECUTE statement which
> exists in 7.1. Here is some doc:
>
> EXECUTE {query-string}
>
> where query-string is a string of type
> TEXT containing the query to be executed.
>
> Unlike all other queries in PL/pgSQL, a
> query run by an EXECUTE statement is not
> prepared
> and saved just once during the life of
> the server. Instead, the query is prepared
> each time the
> statement is run. The query-string can be
> dynamically created within the procedure to
> perform actions on variable tables and
> fields.
>
> The results from SELECT queries are
> discarded by EXECUTE unless SELECT INTO is
> used to
> save the results into a table.
>
> An example:
>
> EXECUTE ''UPDATE tbl SET ''
> || quote_ident(fieldname)
> || '' = ''
> || quote_literal(newvalue)
> || '' WHERE ...'';
>
> This example shows use of the functions
> quote_ident(TEXT) and
> quote_literal(TEXT). Variables containing
> field and table identifiers should be passed
> to
> function quote_ident(). Variables
> containing literal elements of the dynamic
> query string
> should be passed to quote_literal(). Both
> take the appropriate steps to return the
> input
> text enclosed in single or double quotes
> and with any embedded special characters
> intact.
>
> --
> Anatoly K. Lasareff Email:
> tolik@aaanet.ru
> http://tolikus.hq.aaanet.ru:8080 Phone:
> (8632)-710071
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql grief
2001-02-08 16:47 plpgsql grief rob <rob@dsvr.net>
2001-02-09 02:00 ` Re: plpgsql grief Ian Harding <iharding@pakrat.com>
@ 2001-02-09 17:14 ` rob <rob@dsvr.net>
2001-02-11 03:19 ` Re: plpgsql grief Ian Harding <iharding@pakrat.com>
0 siblings, 1 reply; 14+ messages in thread
From: rob @ 2001-02-09 17:14 UTC (permalink / raw)
To: pgsql-sql
<snip>
>
> I feel your pain;^)
>
> Here is the text of a post from Tuesday... I think it answers your question
> which is that you cannot do variable subsititution for table or field names
> inside procedures. This is not a Postgres specific limitation, MS SQL
> Server has the same issue.
>
> > >>>>> "DR" == David Richter
> > writes:
<snip>
Ya know, i already read this one. That's what got me on to 7.1 and using
EXECUTE :)
ARgh! Apparenty I've opened up a real can of worms with wanting cool
general functions, with a bit of dynamism and business logic.
However....
OK dumped pl/pgsql, thinking pl/tcl is more my bag (didn't fancy
recompiling perl to get the shared lib, and didn't want to waste much
time struggling to somehow see if PHP could be used, since PHP is my
current 'main' lang). Pl/tcl supports dynamic queries - great !. However
it introduced it's own little wrinkles. Now for starters I've no
knowledge of tcl, however doing my job means learning loads of
exteranous crap, and what another lang... I reckon I can do it, just
need a little help. So here goes.
found out some things too - trigger functions must return opaque (ok not
100% on what opaque is, but I'm not worrying about that just yet), also
can't have parameters in the function name - odd, but this lead on to -
how the hell would you pass the parameters to the func anyway if it's
attached to a trigger - like INSERT - and you merely type the SQL :
insert into tablename values bla blabla ;
and invoke the trigger, which inturn invokes the function ... erm there
- wheres the transport to passing the parameters (sorry i know my
programmers lingo isn't 100%).
here's my tcl'ed function, which i attached to my main table on INSERT.
It's supposed to log the action to another table passed to it - the idea
is i've generalised some logging tables to various 'main' tables, and
these functions are supposed to record various db and system level
events. The function represents a bit of copying and playing - duno what
TPL is however it works. I'll address further issues, error checking,
processing etc when I've got it actually doing something :
create function update_trans () returns opaque as '
spi_exec -array TPL "insert into $1 (objid, objtbl, et, event, time,
reason, owner) values (\'$2\', \'$3\', \'$4\', \'$5\', now(), \'$6\',
\'$7\')"
' language 'pltcl' ;
which works !! well, gets invoked however it doesn't know what 'text'
is. Not sure where that got picked up from, but it obviously didn't work
- however the insert did. Now I found out there's a parameter array
tgargs or something, but how does this get set ? How does it then get
accessed in the function ?
OK, being the resourceful chap I am (relatively) how about this, a
slight diversion. Why not just make up some function which do the main
insert, business logic, event logging stuff explicity and sack off doing
sql inserts/update etcs.
(can't return opaque here, but that's no bother, i think (hope))
New function
create function update_trans (text, int4, text, text, text, text, text)
returns boolean as '
spi_exec -array TPL "insert into $1 (objid, objtbl, et, event, time,
reason, owner) values (\'$2\', \'$3\', \'$4\', \'$5\', now(), \'$6\',
\'$7\')"
return1
' language 'pltcl' ;
then call these from my code like
select update_trans (bla, 1, bla, bla blabl) ;
which works also. I get to pass all the parameters i want, and have full
control over execution. OK this looses part of the reason for doing this
in the first place - tracking people who side track the app code by
modifying the db directly, however since noone should be doing that
anyway, no problem. (note should).
Again apologies for the verbose message - i feel the 'fuller picture' is
more useful in the long run, rather than diconnected questions. Well it
is to me when I'm on your side of the fence.
Thanks for the reply BTW. Oh, and why is this news group only accessible
during late afternoon from 3pm'ish GMT. I'm access it from the UK. All
morning, for two days, I couldn't get on - server busy errors.
Oh and before I forget - over several months of news group postings
there has been the recognision of the need for examples for us newies,
and some mention of people compiling various docs for just such a
purpose - anyone get anywhere with any of these, as they were several
months ago. I'm certainly gaining some real gotcha type info on all of
this :)
Now I've had it. Burned out. So off to the pub and henceforth become as
drunk as a skunk !
Regards
Rob
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql grief
2001-02-08 16:47 plpgsql grief rob <rob@dsvr.net>
2001-02-09 02:00 ` Re: plpgsql grief Ian Harding <iharding@pakrat.com>
2001-02-09 17:14 ` Re: plpgsql grief rob <rob@dsvr.net>
@ 2001-02-11 03:19 ` Ian Harding <iharding@pakrat.com>
0 siblings, 0 replies; 14+ messages in thread
From: Ian Harding @ 2001-02-11 03:19 UTC (permalink / raw)
To: pgsql-sql
Tcl is my bread and butter but, coincidentally, I have just started considering
pl/tcl 2 days ago as the choice for server side pg programming. I do it in
microsoft t-sql right now, and plsql is pretty close to that. However, tcl is
like English to me, so I think I will go that way unless someone can tell me why
I shouldn't.
I found that the docs are actually pretty excellent, although brief, on pl/tcl.
Tcl is a strange animal, so you will have to get used to 'lists' and occasional
unwanted/unexpected variable substitution until you get the hang of it. But if
you scan the docs you will find some interesting stuff. It seems they have
provided everything you and I will ever need, we just need to figure out how to
use it.
The docs are in USER versus PROGRAMMER. Go figure.
There is a newsgroup called 'novice' that is useful from time to time. I don't
know why the server has had such availability issues the last couple days, it is
usually very good.
Have fun,
Ian
rob wrote:
> <snip>
> >
> > I feel your pain;^)
> >
> > Here is the text of a post from Tuesday... I think it answers your question
> > which is that you cannot do variable subsititution for table or field names
> > inside procedures. This is not a Postgres specific limitation, MS SQL
> > Server has the same issue.
> >
> > > >>>>> "DR" == David Richter
> > > writes:
> <snip>
> Ya know, i already read this one. That's what got me on to 7.1 and using
> EXECUTE :)
>
> ARgh! Apparenty I've opened up a real can of worms with wanting cool
> general functions, with a bit of dynamism and business logic.
> However....
>
> OK dumped pl/pgsql, thinking pl/tcl is more my bag (didn't fancy
> recompiling perl to get the shared lib, and didn't want to waste much
> time struggling to somehow see if PHP could be used, since PHP is my
> current 'main' lang). Pl/tcl supports dynamic queries - great !. However
> it introduced it's own little wrinkles. Now for starters I've no
> knowledge of tcl, however doing my job means learning loads of
> exteranous crap, and what another lang... I reckon I can do it, just
> need a little help. So here goes.
>
> found out some things too - trigger functions must return opaque (ok not
> 100% on what opaque is, but I'm not worrying about that just yet), also
> can't have parameters in the function name - odd, but this lead on to -
> how the hell would you pass the parameters to the func anyway if it's
> attached to a trigger - like INSERT - and you merely type the SQL :
>
> insert into tablename values bla blabla ;
>
> and invoke the trigger, which inturn invokes the function ... erm there
> - wheres the transport to passing the parameters (sorry i know my
> programmers lingo isn't 100%).
>
> here's my tcl'ed function, which i attached to my main table on INSERT.
> It's supposed to log the action to another table passed to it - the idea
> is i've generalised some logging tables to various 'main' tables, and
> these functions are supposed to record various db and system level
> events. The function represents a bit of copying and playing - duno what
> TPL is however it works. I'll address further issues, error checking,
> processing etc when I've got it actually doing something :
>
> create function update_trans () returns opaque as '
> spi_exec -array TPL "insert into $1 (objid, objtbl, et, event, time,
> reason, owner) values (\'$2\', \'$3\', \'$4\', \'$5\', now(), \'$6\',
> \'$7\')"
> ' language 'pltcl' ;
>
> which works !! well, gets invoked however it doesn't know what 'text'
> is. Not sure where that got picked up from, but it obviously didn't work
> - however the insert did. Now I found out there's a parameter array
> tgargs or something, but how does this get set ? How does it then get
> accessed in the function ?
>
> OK, being the resourceful chap I am (relatively) how about this, a
> slight diversion. Why not just make up some function which do the main
> insert, business logic, event logging stuff explicity and sack off doing
> sql inserts/update etcs.
>
> (can't return opaque here, but that's no bother, i think (hope))
>
> New function
>
> create function update_trans (text, int4, text, text, text, text, text)
> returns boolean as '
> spi_exec -array TPL "insert into $1 (objid, objtbl, et, event, time,
> reason, owner) values (\'$2\', \'$3\', \'$4\', \'$5\', now(), \'$6\',
> \'$7\')"
> return1
> ' language 'pltcl' ;
>
> then call these from my code like
>
> select update_trans (bla, 1, bla, bla blabl) ;
>
> which works also. I get to pass all the parameters i want, and have full
> control over execution. OK this looses part of the reason for doing this
> in the first place - tracking people who side track the app code by
> modifying the db directly, however since noone should be doing that
> anyway, no problem. (note should).
>
> Again apologies for the verbose message - i feel the 'fuller picture' is
> more useful in the long run, rather than diconnected questions. Well it
> is to me when I'm on your side of the fence.
>
> Thanks for the reply BTW. Oh, and why is this news group only accessible
> during late afternoon from 3pm'ish GMT. I'm access it from the UK. All
> morning, for two days, I couldn't get on - server busy errors.
>
> Oh and before I forget - over several months of news group postings
> there has been the recognision of the need for examples for us newies,
> and some mention of people compiling various docs for just such a
> purpose - anyone get anywhere with any of these, as they were several
> months ago. I'm certainly gaining some real gotcha type info on all of
> this :)
>
> Now I've had it. Burned out. So off to the pub and henceforth become as
> drunk as a skunk !
>
> Regards
>
> Rob
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql grief
2001-02-08 16:47 plpgsql grief rob <rob@dsvr.net>
@ 2001-02-12 16:05 ` Josh Berkus <josh@agliodbs.com>
2001-02-12 16:28 ` Re: plpgsql grief Michael Fork <mfork@toledolink.com>
2001-02-12 16:45 ` Re: plpgsql grief Tom Lane <tgl@sss.pgh.pa.us>
2001-02-12 17:09 ` Re: plpgsql grief Josh Berkus <josh@agliodbs.com>
1 sibling, 3 replies; 14+ messages in thread
From: Josh Berkus @ 2001-02-12 16:05 UTC (permalink / raw)
To: rob <rob@dsvr.net>; +Cc: pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql grief
2001-02-08 16:47 plpgsql grief rob <rob@dsvr.net>
2001-02-12 16:05 ` Re: plpgsql grief Josh Berkus <josh@agliodbs.com>
@ 2001-02-12 16:28 ` Michael Fork <mfork@toledolink.com>
2 siblings, 0 replies; 14+ messages in thread
From: Michael Fork @ 2001-02-12 16:28 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: rob <rob@dsvr.net>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql grief
2001-02-08 16:47 plpgsql grief rob <rob@dsvr.net>
2001-02-12 16:05 ` Re: plpgsql grief Josh Berkus <josh@agliodbs.com>
@ 2001-02-12 16:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-12 17:12 ` Re: plpgsql grief Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2001-02-12 16:45 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: rob <rob@dsvr.net>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql grief
2001-02-08 16:47 plpgsql grief rob <rob@dsvr.net>
2001-02-12 16:05 ` Re: plpgsql grief Josh Berkus <josh@agliodbs.com>
2001-02-12 16:45 ` Re: plpgsql grief Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-02-12 17:12 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 14+ messages in thread
From: Josh Berkus @ 2001-02-12 17:12 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql grief
2001-02-08 16:47 plpgsql grief rob <rob@dsvr.net>
2001-02-12 16:05 ` Re: plpgsql grief Josh Berkus <josh@agliodbs.com>
@ 2001-02-12 17:09 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 14+ messages in thread
From: Josh Berkus @ 2001-02-12 17:09 UTC (permalink / raw)
To: rob <rob@dsvr.net>; pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: plpgsql grief
@ 2001-02-12 15:57 Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
2001-02-12 16:46 ` Re: plpgsql grief Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Michael Ansley @ 2001-02-12 15:57 UTC (permalink / raw)
To: 'rob' <rob@dsvr.net>; +Cc: pgsql-sql
Hi, Rob,
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql grief
2001-02-12 15:57 RE: plpgsql grief Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
@ 2001-02-12 16:46 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 14+ messages in thread
From: Tom Lane @ 2001-02-12 16:46 UTC (permalink / raw)
To: Michael Ansley <Michael.Ansley@intec-telecom-systems.com>; +Cc: 'rob' <rob@dsvr.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: plpgsql grief
@ 2001-02-12 17:19 Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
2001-02-12 17:20 ` Re: plpgsql grief Josh Berkus <josh@agliodbs.com>
2001-02-12 17:40 ` Re: plpgsql grief Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 14+ messages in thread
From: Michael Ansley @ 2001-02-12 17:19 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
**********************************************************************
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql grief
2001-02-12 17:19 RE: plpgsql grief Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
@ 2001-02-12 17:20 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 14+ messages in thread
From: Josh Berkus @ 2001-02-12 17:20 UTC (permalink / raw)
To: pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql grief
2001-02-12 17:19 RE: plpgsql grief Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
@ 2001-02-12 17:40 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 14+ messages in thread
From: Tom Lane @ 2001-02-12 17:40 UTC (permalink / raw)
To: Michael Ansley <Michael.Ansley@intec-telecom-systems.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2001-02-12 17:40 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-08 16:47 plpgsql grief rob <rob@dsvr.net>
2001-02-09 02:00 ` Ian Harding <iharding@pakrat.com>
2001-02-09 17:14 ` rob <rob@dsvr.net>
2001-02-11 03:19 ` Ian Harding <iharding@pakrat.com>
2001-02-12 16:05 ` Josh Berkus <josh@agliodbs.com>
2001-02-12 16:28 ` Michael Fork <mfork@toledolink.com>
2001-02-12 16:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-12 17:12 ` Josh Berkus <josh@agliodbs.com>
2001-02-12 17:09 ` Josh Berkus <josh@agliodbs.com>
2001-02-12 15:57 RE: plpgsql grief Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
2001-02-12 16:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-12 17:19 RE: plpgsql grief Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
2001-02-12 17:20 ` Josh Berkus <josh@agliodbs.com>
2001-02-12 17:40 ` Tom Lane <tgl@sss.pgh.pa.us>
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