pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/pgSQL examples NOT involving functions
21+ messages / 7 participants
[nested] [flat]
* PL/pgSQL examples NOT involving functions
@ 2001-11-15 16:49 Roland Roberts <roland@astrofoto.org>
0 siblings, 2 replies; 21+ messages in thread
From: Roland Roberts @ 2001-11-15 16:49 UTC (permalink / raw)
To: pgsql-sql@postgresql.org; pgsql-docs
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PL/pgSQL examples NOT involving functions
@ 2001-11-15 20:54 Josh Berkus <josh@agliodbs.com>
parent: Roland Roberts <roland@astrofoto.org>
1 sibling, 1 reply; 21+ messages in thread
From: Josh Berkus @ 2001-11-15 20:54 UTC (permalink / raw)
To: Roland Roberts <roland@astrofoto.org>; pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PL/pgSQL examples NOT involving functions
@ 2001-11-15 21:21 Roland Roberts <roland@astrofoto.org>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 21+ messages in thread
From: Roland Roberts @ 2001-11-15 21:21 UTC (permalink / raw)
To: pgsql-sql@postgresql.org
>>>>> "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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [SQL] PL/pgSQL examples NOT involving functions
@ 2001-11-15 21:50 Aasmund Midttun Godal <postgresql@envisity.com>
parent: Roland Roberts <roland@astrofoto.org>
1 sibling, 2 replies; 21+ messages in thread
From: Aasmund Midttun Godal @ 2001-11-15 21:50 UTC (permalink / raw)
To: roland@astrofoto.org; +Cc: pgsql-docs; pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PL/pgSQL examples NOT involving functions
@ 2001-11-16 00:19 Josh Berkus <josh@agliodbs.com>
parent: Roland Roberts <roland@astrofoto.org>
0 siblings, 1 reply; 21+ messages in thread
From: Josh Berkus @ 2001-11-16 00:19 UTC (permalink / raw)
To: Roland Roberts <roland@astrofoto.org>; pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [SQL] PL/pgSQL examples NOT involving functions
@ 2001-11-19 03:57 Roland Roberts <roland@astrofoto.org>
parent: Aasmund Midttun Godal <postgresql@envisity.com>
1 sibling, 1 reply; 21+ messages in thread
From: Roland Roberts @ 2001-11-19 03:57 UTC (permalink / raw)
To: pgsql-docs; pgsql-sql@postgresql.org
>>>>> "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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PL/pgSQL examples NOT involving functions
@ 2001-11-19 04:00 Roland Roberts <roland@astrofoto.org>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 21+ messages in thread
From: Roland Roberts @ 2001-11-19 04:00 UTC (permalink / raw)
To: pgsql-sql@postgresql.org
>>>>> "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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [SQL] PL/pgSQL examples NOT involving functions
@ 2001-11-19 13:51 Aasmund Midttun Godal <postgresql@envisity.com>
parent: Roland Roberts <roland@astrofoto.org>
0 siblings, 1 reply; 21+ messages in thread
From: Aasmund Midttun Godal @ 2001-11-19 13:51 UTC (permalink / raw)
To: roland@astrofoto.org; +Cc: pgsql-docs; pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PL/pgSQL examples NOT involving functions
@ 2001-11-19 14:42 Roland Roberts <roland@astrofoto.org>
parent: Aasmund Midttun Godal <postgresql@envisity.com>
0 siblings, 1 reply; 21+ messages in thread
From: Roland Roberts @ 2001-11-19 14:42 UTC (permalink / raw)
To: pgsql-sql@postgresql.org
>>>>> "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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [SQL] PL/pgSQL examples NOT involving functions
@ 2001-11-20 04:32 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Aasmund Midttun Godal <postgresql@envisity.com>
1 sibling, 1 reply; 21+ messages in thread
From: Christopher Kings-Lynne @ 2001-11-20 04:32 UTC (permalink / raw)
To: Aasmund Midttun Godal <postgresql@envisity.com>; roland@astrofoto.org; +Cc: pgsql-docs; pgsql-sql@postgresql.org
> > 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [DOCS] PL/pgSQL examples NOT involving functions
@ 2001-11-20 05:00 Roland Roberts <roland@astrofoto.org>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 21+ messages in thread
From: Roland Roberts @ 2001-11-20 05:00 UTC (permalink / raw)
To: pgsql-sql@postgresql.org
>>>>> "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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PL/pgSQL examples NOT involving functions
@ 2001-11-22 01:09 Andrew G. Hammond <drew@xyzzy.dhs.org>
parent: Roland Roberts <roland@astrofoto.org>
0 siblings, 1 reply; 21+ messages in thread
From: Andrew G. Hammond @ 2001-11-22 01:09 UTC (permalink / raw)
To: Roland Roberts <roland@astrofoto.org>; pgsql-sql@postgresql.org
-----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-----
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PL/pgSQL examples NOT involving functions
@ 2001-11-23 14:47 Aasmund Midttun Godal <postgresql@envisity.com>
parent: Andrew G. Hammond <drew@xyzzy.dhs.org>
0 siblings, 0 replies; 21+ messages in thread
From: Aasmund Midttun Godal @ 2001-11-23 14:47 UTC (permalink / raw)
To: pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PL/pgSQL examples NOT involving functions
@ 2001-11-27 01:23 Josh Berkus <josh@agliodbs.com>
0 siblings, 3 replies; 21+ messages in thread
From: Josh Berkus @ 2001-11-27 01:23 UTC (permalink / raw)
To: Aasmund Midttun Godal <postgresql@envisity.com>; +Cc: pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PL/pgSQL examples NOT involving functions
@ 2001-11-27 19:53 Aasmund Midttun Godal <postgresql@envisity.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 21+ messages in thread
From: Aasmund Midttun Godal @ 2001-11-27 19:53 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PL/pgSQL examples NOT involving functions
@ 2001-11-27 19:55 Aasmund Midttun Godal <postgresql@envisity.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 21+ messages in thread
From: Aasmund Midttun Godal @ 2001-11-27 19:55 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql@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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PL/pgSQL examples NOT involving functions
@ 2001-11-27 19:59 Aasmund Midttun Godal <postgresql@envisity.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 21+ messages in thread
From: Aasmund Midttun Godal @ 2001-11-27 19:59 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PL/pgSQL examples NOT involving functions
@ 2001-11-28 00:01 Josh Berkus <josh@agliodbs.com>
parent: Aasmund Midttun Godal <postgresql@envisity.com>
0 siblings, 2 replies; 21+ messages in thread
From: Josh Berkus @ 2001-11-28 00:01 UTC (permalink / raw)
To: Aasmund Midttun Godal <postgresql@envisity.com>; josh@agliodbs.com; +Cc: pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PL/pgSQL examples NOT involving functions
@ 2001-11-28 00:32 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 21+ messages in thread
From: Stephan Szabo @ 2001-11-28 00:32 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Aasmund Midttun Godal <postgresql@envisity.com>; pgsql-sql@postgresql.org
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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PL/pgSQL examples NOT involving functions
@ 2001-12-05 21:26 Jan Wieck <janwieck@yahoo.com>
parent: Aasmund Midttun Godal <postgresql@envisity.com>
0 siblings, 0 replies; 21+ messages in thread
From: Jan Wieck @ 2001-12-05 21:26 UTC (permalink / raw)
To: Aasmund Midttun Godal <postgresql@envisity.com>; +Cc: josh@agliodbs.com; pgsql-sql@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 first time 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: PL/pgSQL examples NOT involving functions
@ 2001-12-05 21:29 Jan Wieck <janwieck@yahoo.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 21+ messages in thread
From: Jan Wieck @ 2001-12-05 21:29 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Aasmund Midttun Godal <postgresql@envisity.com>; pgsql-sql@postgresql.org
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
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2001-12-05 21:29 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-15 16:49 PL/pgSQL examples NOT involving functions Roland Roberts <roland@astrofoto.org>
2001-11-15 20:54 ` Josh Berkus <josh@agliodbs.com>
2001-11-15 21:21 ` Roland Roberts <roland@astrofoto.org>
2001-11-16 00:19 ` Josh Berkus <josh@agliodbs.com>
2001-11-19 04:00 ` Roland Roberts <roland@astrofoto.org>
2001-11-15 21:50 ` Aasmund Midttun Godal <postgresql@envisity.com>
2001-11-19 03:57 ` Roland Roberts <roland@astrofoto.org>
2001-11-19 13:51 ` Aasmund Midttun Godal <postgresql@envisity.com>
2001-11-19 14:42 ` Roland Roberts <roland@astrofoto.org>
2001-11-22 01:09 ` Andrew G. Hammond <drew@xyzzy.dhs.org>
2001-11-23 14:47 ` Aasmund Midttun Godal <postgresql@envisity.com>
2001-11-20 04:32 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2001-11-20 05:00 ` Roland Roberts <roland@astrofoto.org>
2001-11-27 01:23 Re: PL/pgSQL examples NOT involving functions Josh Berkus <josh@agliodbs.com>
2001-11-27 19:53 ` Aasmund Midttun Godal <postgresql@envisity.com>
2001-11-28 00:01 ` Josh Berkus <josh@agliodbs.com>
2001-11-28 00:32 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-05 21:29 ` Jan Wieck <janwieck@yahoo.com>
2001-11-27 19:55 ` Aasmund Midttun Godal <postgresql@envisity.com>
2001-11-27 19:59 ` Aasmund Midttun Godal <postgresql@envisity.com>
2001-12-05 21:26 ` Jan Wieck <janwieck@yahoo.com>
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