Thread: PL/pgSQL examples NOT involving functions
How can I write a few lines of PL/pgSQL which do not involve creating
a function? I can find no examples of this in the docs, but say I
would like to do something like
BEGIN
IF EXISTS (SELECT * FROM foo WHERE idx = 27)
THEN
UPDATE foo SET var='some value' WHERE idx=27;
ELSE
INSERT INTO foo (idx, var) VALUES (27, 'some value');
END IF
END;
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
Roland, Please do not cross-post to multiple lists. > How can I write a few lines of PL/pgSQL which do not involve creating > a function? I can find no examples of this in the docs, but say I > would like to do something like You can't. PL/pgSQL, like PL/Perl and PL/Tcl, may only be used in functions. -Josh ______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
>>>>> "Josh" == Josh Berkus <josh@agliodbs.com> writes: Josh> Please do not cross-post to multiple lists. Hmm, there were some complaints recently on the hackers list about things being posted on only one list. And a significant chunk of the mail I am getting from lists shows it was posted to more than one list. >> How can I write a few lines of PL/pgSQL which do not involve >> creating a function? I can find no examples of thisin the >> docs, but say I would like to do something like Josh> You can't. PL/pgSQL, like PL/Perl and PL/Tcl, may only be Josh> used in functions. So how would I convert an insert to an update when I detect a tuple is already present in the database? Do I have to write the whole thing in Perl/Tcl/whatever and do the conversion there? roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises roland@rlenter.com 76-15 113th Street, Apt 3B roland@astrofoto.org Forest Hills, NY 11375
Roland Roberts <roland@astrofoto.org> writes:
> "Josh" == Josh Berkus <josh@agliodbs.com> writes:
> Josh> Please do not cross-post to multiple lists.
> Hmm, there were some complaints recently on the hackers list about
> things being posted on only one list.
While it's easy to overdo cross-posting, I don't regard it as inherently
a bad thing. May I suggest (again) that folks who are subscribed to
multiple PG lists should select the "unique" subscription mode? If
you're set up that way then you only get one copy of cross-posted
messages, and everyone is happy.
It's been awhile since I did this, but I think sendingset ALL unique
to the majordomo server does the trick.
regards, tom lane
Well, to the best of my knowledge your question is a contradiction in terms: plpgsql is a procedural language to use it you must create a stored procedure or function... However you are probably able to do many of the things you may want with plain old SQL look it up in the docs especiallythe case structure. Regards, Aasmund. On 15 Nov 2001 11:49:12 -0500, Roland Roberts <roland@astrofoto.org> wrote: > How can I write a few lines of PL/pgSQL which do not involve creating > a function? I can find no examples of this in the docs, but say I > would like to do something like > > BEGIN > IF EXISTS (SELECT * FROM foo WHERE idx = 27) > THEN > UPDATE foo SET var='some value' WHERE idx=27; > ELSE > INSERT INTO foo (idx, var) VALUES (27, 'some value'); > END IF > END; > > roland > -- > PGP Key ID: 66 BC 3B CD > Roland B. Roberts, PhD RL Enterprises > roland@rlenter.com 76-15 113th Street, Apt 3B > roland@astrofoto.org Forest Hills, NY 11375 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
Roland, > Hmm, there were some complaints recently on the hackers list about > things being posted on only one list. And a significant chunk of the > mail I am getting from lists shows it was posted to more than one > list. Yeah, and you'll notice me saying "please don't cross-post" a lot. Basically I'm trying to encourage people to NOT cross-post because we end up with a lot of questions being answered 2 and 3 times on different lists. It's really not fair to the people who are trying to be helpful and answer questions. Hackers is one of the lists I am not subscribed to (I currently read SQL, NOVICE, PHP and ANNOUNCE). > So how would I convert an insert to an update when I detect a tuple > is > already present in the database? Do I have to write the whole thing > in Perl/Tcl/whatever and do the conversion there? Yes. Use the EXECUTE method. Or do it in your middleware. Or establish a unique index, do both an insert and an update, and ignore the error messages. Or ... if you wanna get really tricky ... write some triggers to do the substitution. Personally, I find that keeping track, on the client side, of whether you're adding a new record or editing an old one and acting appropriately is better than trying to second-guess it on the database level, but maybe that's just me. -Josh ______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
>>>>> "Aasmund" == Aasmund Midttun Godal <postgresql@envisity.com> writes:
Aasmund> Well, to the best of my knowledge your question is a
Aasmund> contradiction in terms: plpgsql is a procedural language
Aasmund> to use it you must create a stored procedure or
Aasmund> function...
Coming from an Oracle background, perhaps I don't see it the same
way. To merely *call* a PL/SQL procedure, I have to do something like
BEGIN
MYPROC (a, b, c)
END;
So I *could* create a pl/pgsql function and call it like that, but
since pl/pgsql procedures aren't compiled I wondered if I could do it
inline.
Aasmund> However you are probably able to do many of the things
Aasmund> you may want with plain old SQL look it up in the docs
Aasmund> especially the case structure.
Thanks for this suggestion; I'll have a look at the case structure....
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
>>>>> "Josh" == Josh Berkus <josh@agliodbs.com> writes: Josh> Personally, I find that keeping track, on the client side, Josh> of whether you're adding a new record or editingan old one Josh> and acting appropriately is better than trying to Josh> second-guess it on the database level,but maybe that's just Josh> me. Well, that's basically what I'm trying to do. But finding out whether I need to insert or update requires doing something like my pseudo-SQL example. I was just trying to figure out if I could do in in SQL pl/pgSQL. roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises roland@rlenter.com 76-15 113th Street, Apt 3B roland@astrofoto.org Forest Hills, NY 11375
On 18 Nov 2001 22:57:05 -0500, Roland Roberts <roland@astrofoto.org> wrote: > > Aasmund> Well, to the best of my knowledge your question is a > Aasmund> contradiction in terms: plpgsql is a procedural language > Aasmund> to use it you must create a stored procedure or > Aasmund> function... > > Coming from an Oracle background, perhaps I don't see it the same > way. To merely *call* a PL/SQL procedure, I have to do something like > > BEGIN > MYPROC (a, b, c) > END; > > So I *could* create a pl/pgsql function and call it like that, but > since pl/pgsql procedures aren't compiled I wondered if I could do it > inline. I do believe they are compiled. > > Aasmund> However you are probably able to do many of the things > Aasmund> you may want with plain old SQL look it up in the docs > Aasmund> especially the case structure. > > Thanks for this suggestion; I'll have a look at the case structure.... > > roland > -- > PGP Key ID: 66 BC 3B CD > Roland B. Roberts, PhD RL Enterprises > roland@rlenter.com 76-15 113th Street, Apt 3B > roland@astrofoto.org Forest Hills, NY 11375 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
>>>>> "Aasmund" == Aasmund Midttun Godal <postgresql@envisity.com> writes: >> So I *could* create a pl/pgsql function and call it like that, >> but since pl/pgsql procedures aren't compiled Iwondered if I >> could do it inline. Aasmund> I do believe they are compiled. Hmm, I'm going to have to look over the docs again. I missed that change; I'm pretty sure that as of 6.2 there were *not* compiled and that was clearly stated in the docs. roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises roland@rlenter.com 76-15 113th Street, Apt 3B roland@astrofoto.org Forest Hills, NY 11375
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 2001 November 19 09:42 am, Roland Roberts wrote: > >>>>> "Aasmund" == Aasmund Midttun Godal <postgresql@envisity.com> writes: > >> So I *could* create a pl/pgsql function and call it like that, > >> but since pl/pgsql procedures aren't compiled I wondered if I > >> could do it inline. > > Aasmund> I do believe they are compiled. > > Hmm, I'm going to have to look over the docs again. I missed that > change; I'm pretty sure that as of 6.2 there were *not* compiled and > that was clearly stated in the docs. You're right, they're interpreted at run time. If you want proof, just write a bogus function definition. It'll CREATE just fine, but barf with a syntax error when you try and execute it. - -- Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/ 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iEYEARECAAYFAjv8UFoACgkQCT73CrRXhLHSBwCeKEbDRUezLXS/Q1vP1VlXAdTD RVoAoI8qA8Q5C2hVdXwPGBt/3UnOKF4T =8+Us -----END PGP SIGNATURE-----
> > How can I write a few lines of PL/pgSQL which do not involve creating > > a function? I can find no examples of this in the docs, but say I > > would like to do something like > > > > BEGIN > > IF EXISTS (SELECT * FROM foo WHERE idx = 27) > > THEN > > UPDATE foo SET var='some value' WHERE idx=27; > > ELSE > > INSERT INTO foo (idx, var) VALUES (27, 'some value'); > > END IF > > END; Interesting. I see you are trying to simulate MySQL's 'REPLACE INTO' syntax. On an aside, I was recently asked to convert this MySQL code to PGSQL code for the GeekLog project. I gave up: REPLACE INTO $table ($fields) SELECT $values FROM $tablefrom; Evil! Chris
Well - it is not 'compiled' until the first time you run it - this is so that it will work with circular dependancies. Iam not sure how much it compiles it, but I think it at least stores parse-trees, because if you drop a table and recreateit and run the same function on it, it will cause an error a la 'can't find relation 11312'. Regards, Aasmund. On Wed, 21 Nov 2001 20:09:42 -0500, "Andrew G. Hammond" <drew@xyzzy.dhs.org> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 2001 November 19 09:42 am, Roland Roberts wrote: > > You're right, they're interpreted at run time. If you want proof, just write > a bogus function definition. It'll CREATE just fine, but barf with a syntax > error when you try and execute it. > > - -- > Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/ > 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481 > 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 > "To blow recursion you must first blow recur" -- me > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.0.6 (GNU/Linux) > Comment: For info see http://www.gnupg.org > > iEYEARECAAYFAjv8UFoACgkQCT73CrRXhLHSBwCeKEbDRUezLXS/Q1vP1VlXAdTD > RVoAoI8qA8Q5C2hVdXwPGBt/3UnOKF4T > =8+Us > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
Well - it is not 'compiled' until the first time you run it - this is so that it will work with circular dependancies. Iam not sure how much it compiles it, but I think it at least stores parse-trees, because if you drop a table and recreateit and run the same function on it, it will cause an error a la 'can't find relation 11312'. Regards, Aasmund. On Wed, 21 Nov 2001 20:09:42 -0500, "Andrew G. Hammond" <drew@xyzzy.dhs.org> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 2001 November 19 09:42 am, Roland Roberts wrote: > > You're right, they're interpreted at run time. If you want proof, just write > a bogus function definition. It'll CREATE just fine, but barf with a syntax > error when you try and execute it. > > - -- > Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/ > 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481 > 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 > "To blow recursion you must first blow recur" -- me > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.0.6 (GNU/Linux) > Comment: For info see http://www.gnupg.org > > iEYEARECAAYFAjv8UFoACgkQCT73CrRXhLHSBwCeKEbDRUezLXS/Q1vP1VlXAdTD > RVoAoI8qA8Q5C2hVdXwPGBt/3UnOKF4T > =8+Us > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
Aasmund, > Well - it is not 'compiled' until the first time you run it - this is > so that it will work with circular dependancies. I am not sure how > much it compiles it, but I think it at least stores parse-trees, > because if you drop a table and recreate it and run the same function > on it, it will cause an error a la 'can't find relation 11312'. This is incorrect. This happens with views, but not with functions. -Josh ______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
envisity=# create table temp (tempid integer); CREATE envisity=# INSERT INTO temp VALUES(1); CREATE FUNCTION test3() RETURNS INTEGER AS 'DECLARE id INTEGER; BEGIN SELECT INTO id tempid FROM temp LIMIT 1; RETURN id;END;' LANGUAGE 'plpgsql'; DROP CREATE envisity=# select test3();test3 ------- 1 (1 row) envisity=# DROP TABLE temp ; DROP envisity=# create table temp (tempid integer); CREATE envisity=# INSERT INTO temp VALUES(3); INSERT 150371 1 envisity=# select test3(); ERROR: Relation 150348 does not exist envisity=# Regards, Aasmund. On Mon, 26 Nov 2001 17:23:13 -0800, "Josh Berkus" <josh@agliodbs.com> wrote: > Aasmund, > > > This is incorrect. This happens with views, but not with functions. > > -Josh > > > ______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 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
Erm, sorry about the multiple postings, I just discovered a couple of interesting tidbits after I posted first... http://www.postgresql.org/idocs/index.php?plpgsql.html : "The PL/pgSQL call handler parses the function's source text and produces an internal binary instruction tree the first timethe function is called." On Mon, 26 Nov 2001 17:23:13 -0800, "Josh Berkus" <josh@agliodbs.com> wrote: > Aasmund, > > > This is incorrect. This happens with views, but not with functions. > > -Josh > > > ______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 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Offcourse, with language 'sql' it works fine, but we were talking about pl/pgsql... Regards, Aasmund. On Mon, 26 Nov 2001 17:23:13 -0800, "Josh Berkus" <josh@agliodbs.com> wrote: > Aasmund, > > > This is incorrect. This happens with views, but not with functions. > > -Josh > > > ______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 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
Aasmund, > envisity=# DROP TABLE temp ; > DROP > envisity=# create table temp (tempid integer); > CREATE > envisity=# INSERT INTO temp VALUES(3); > INSERT 150371 1 > envisity=# select test3(); > ERROR: Relation 150348 does not exist > envisity=# That's a new one on me. I've dropped and re-created dozens of tables, and while I have to re-build the views, I've never had to touch the functions for that reason. Either in PL/pgSQL or in SQL. What version are we talking about? -Josh ______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
On Tue, 27 Nov 2001, Josh Berkus wrote: > Aasmund, > > > envisity=# DROP TABLE temp ; > > DROP > > envisity=# create table temp (tempid integer); > > CREATE > > envisity=# INSERT INTO temp VALUES(3); > > INSERT 150371 1 > > envisity=# select test3(); > > ERROR: Relation 150348 does not exist > > envisity=# > > That's a new one on me. I've dropped and re-created dozens of tables, > and while I have to re-build the views, I've never had to touch the > functions for that reason. Either in PL/pgSQL or in SQL. > > What version are we talking about? 7.2b3 :) One thing that should be noted here is that after reconnecting you should get the 3 back out. IIRC parse tree stuff is done first time its called through a connection. So, if you're dropping and doing stuff via like editing files and psqling them and then starting a psql and trying your function you won't see it happen.
Hello! > envisity=# create table temp (tempid integer); > CREATE > envisity=# INSERT INTO temp VALUES(1); > > CREATE FUNCTION test3() RETURNS INTEGER AS 'DECLARE id INTEGER; BEGIN SELECT INTO id tempid FROM temp LIMIT 1; RETURN id; END;' LANGUAGE 'plpgsql'; > DROP > CREATE > envisity=# select test3(); > test3 > ------- > 1 > (1 row) > > envisity=# DROP TABLE temp ; > DROP > envisity=# create table temp (tempid integer); > CREATE > envisity=# INSERT INTO temp VALUES(3); > INSERT 150371 1 > envisity=# select test3(); > ERROR: Relation 150348 does not exist If I exit plpgsql and reenter, then "select test3()" works again. So I guess "the first time" in "The PL/pgSQL call handler parses the function's source text and produces an internal binary instruction tree the first time the function is called." might be referring to "for each client connection". CN -------------------------------------------------------- You too can have your own email address from Eurosport. http://www.eurosport.com
<cnliou@eurosport.com> writes: > If I exit plpgsql and reenter, then "select test3()" > works again. So I guess "the first time" in > "The PL/pgSQL call handler parses the function's > source text and produces an internal binary > instruction tree the first time the function is > called." > might be referring to "for each client connection". Yup. I have tried to improve this documentation for 7.2. See the current version at http://candle.pha.pa.us/main/writings/pgsql/sgml/plpgsql.html and let me know if it still needs work. regards, tom lane
Erm, sorry about the multiple postings, I just discovered a couple of interesting tidbits after I posted first... http://www.postgresql.org/idocs/index.php?plpgsql.html : "The PL/pgSQL call handler parses the function's source text and produces an internal binary instruction tree the first timethe function is called." On Mon, 26 Nov 2001 17:23:13 -0800, "Josh Berkus" <josh@agliodbs.com> wrote: > Aasmund, > > > This is incorrect. This happens with views, but not with functions. > > -Josh > > > ______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 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Aasmund Midttun Godal wrote: > Erm, sorry about the multiple postings, I just discovered a couple of interesting tidbits after I posted first... > > http://www.postgresql.org/idocs/index.php?plpgsql.html : > > "The PL/pgSQL call handler parses the function's source text and produces an internal binary instruction tree the firsttime the function is called." And that is still not entirely accurate. At the first function call the control language (IF, WHEN, etc.) is parsed. All expressions and queries are just remembered as strings with variables replaced by $n parameters. Now every statement will prepare a saved plan the first time it is hit. So when you have a function with conditional code and a specific branch of that code is never called, the statements in there will never be prepared. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Josh Berkus wrote: > Aasmund, > > > envisity=# DROP TABLE temp ; > > DROP > > envisity=# create table temp (tempid integer); > > CREATE > > envisity=# INSERT INTO temp VALUES(3); > > INSERT 150371 1 > > envisity=# select test3(); > > ERROR: Relation 150348 does not exist > > envisity=# > > That's a new one on me. I've dropped and re-created dozens of tables, > and while I have to re-build the views, I've never had to touch the > functions for that reason. Either in PL/pgSQL or in SQL. > > What version are we talking about? Any version. But a simple reconnect to the database fixes the problem, because you'll have a new backend and that guy will of course compile everything on first hit. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com