PL/pgSQL examples NOT involving functions

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 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

"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 this in 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

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 especially the 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 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 sending
set ALL unique
to the majordomo server does the trick.

regards, tom lane

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 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

"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 editing an 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 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.

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

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

"Christopher" == Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

Christopher> Interesting. I see you are trying to simulate
Christopher> MySQL's 'REPLACE INTO' syntax.

Actually, I've never used MySQL. Instead, this is the type of thing
I've seen done in Oracle.

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-----

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'.

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. 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'.

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 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

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

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

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 time the 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

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 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