pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL Functions vs PL/PgSQL
6+ messages / 5 participants
[nested] [flat]
* SQL Functions vs PL/PgSQL
@ 2003-02-12 21:51 Johnny Kristensen <johnnybo1@mail.tele.dk>
0 siblings, 2 replies; 6+ messages in thread
From: Johnny Kristensen @ 2003-02-12 21:51 UTC (permalink / raw)
To: pgsql-sql
Hi!
Does postgres store execution plan's for user-defined SQL functions as the PL/pgSQL interpreter does. And if so - will this only be prepared after an initial call to the function? Can't seem to find this information in the docs!?
Thanks,
Johnny Kristensen
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL Functions vs PL/PgSQL
@ 2003-02-13 09:21 Tomasz Myrta <jasiek@klaster.net>
parent: Johnny Kristensen <johnnybo1@mail.tele.dk>
1 sibling, 0 replies; 6+ messages in thread
From: Tomasz Myrta @ 2003-02-13 09:21 UTC (permalink / raw)
To: Johnny Kristensen <johnnybo1@mail.tele.dk>; +Cc: pgsql-sql
Johnny Kristensen wrote:
> Hi!
> Does postgres store execution plan's for user-defined SQL functions as
> the PL/pgSQL interpreter does. And if so - will this only be prepared
> after an initial call to the function? Can't seem to find this
> information in the docs!?
> Thanks,
> Johnny Kristensen
You need Postgresql 7.3 to do this. Check sql function "PREPARE".
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL Functions vs PL/PgSQL
@ 2003-02-13 17:02 Josh Berkus <josh@agliodbs.com>
parent: Johnny Kristensen <johnnybo1@mail.tele.dk>
1 sibling, 2 replies; 6+ messages in thread
From: Josh Berkus @ 2003-02-13 17:02 UTC (permalink / raw)
To: Johnny Kristensen <johnnybo1@mail.tele.dk>; pgsql-sql
Johnny,
> Does postgres store execution plan's for user-defined SQL functions as the
> PL/pgSQL interpreter does. And if so - will this only be prepared after an
> initial call to the function? Can't seem to find this information in the
> docs!? Thanks,
Unless you know something I don't, I do not believe that PL/pgSQL stores
execution plans for functions.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL Functions vs PL/PgSQL
@ 2003-02-13 18:50 Roberto Mello <rmello@cc.usu.edu>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 6+ messages in thread
From: Roberto Mello @ 2003-02-13 18:50 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Johnny Kristensen <johnnybo1@mail.tele.dk>; pgsql-sql
On Thu, Feb 13, 2003 at 09:02:05AM -0800, Josh Berkus wrote:
> Johnny,
>
> > Does postgres store execution plan's for user-defined SQL functions as the
> > PL/pgSQL interpreter does. And if so - will this only be prepared after an
> > initial call to the function? Can't seem to find this information in the
> > docs!? Thanks,
>
> Unless you know something I don't, I do not believe that PL/pgSQL stores
> execution plans for functions.
AFAIK execution plans for PL/pgSQL functions were cached once per backend.
No?
-Roberto
--
+----| Roberto Mello - http://www.brasileiro.net/ |------+
+ Computer Science Graduate Student, Utah State University +
+ USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +
If CPU < 486 then upgrade (486,OS/2 3.0) : while < 586...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL Functions vs PL/PgSQL
@ 2003-02-14 00:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Roberto Mello <rmello@cc.usu.edu>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-02-14 00:06 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; +Cc: Josh Berkus <josh@agliodbs.com>; Johnny Kristensen <johnnybo1@mail.tele.dk>; pgsql-sql
Roberto Mello <rmello@cc.usu.edu> writes:
> On Thu, Feb 13, 2003 at 09:02:05AM -0800, Josh Berkus wrote:
>> Unless you know something I don't, I do not believe that PL/pgSQL stores
>> execution plans for functions.
> AFAIK execution plans for PL/pgSQL functions were cached once per backend.
> No?
Yes. This is explained in the docs ...
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql.html#PLPGSQL-OVERVIEW
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: SQL Functions vs PL/PgSQL
@ 2003-02-14 09:12 Johnny Kristensen <johnnybo1@mail.tele.dk>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 6+ messages in thread
From: Johnny Kristensen @ 2003-02-14 09:12 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
Of course what I meant was if postgres stores execution plans for queries in user-defined SQL functions. From the docs I can see that PL/PgSQL uses the SPI-interface to create and store execution plans for queries for each connection. But I'll use the PREPARE function like Thomasz Myrta suggested.
/Johnny
----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "Johnny Kristensen" <johnnybo1@mail.tele.dk>; <pgsql-sql@postgresql.org>
Sent: Thursday, February 13, 2003 6:02 PM
Subject: Re: [SQL] SQL Functions vs PL/PgSQL
Johnny,
> Does postgres store execution plan's for user-defined SQL functions as the
> PL/pgSQL interpreter does. And if so - will this only be prepared after an
> initial call to the function? Can't seem to find this information in the
> docs!? Thanks,
Unless you know something I don't, I do not believe that PL/pgSQL stores
execution plans for functions.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-02-14 09:12 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-12 21:51 SQL Functions vs PL/PgSQL Johnny Kristensen <johnnybo1@mail.tele.dk>
2003-02-13 09:21 ` Tomasz Myrta <jasiek@klaster.net>
2003-02-13 17:02 ` Josh Berkus <josh@agliodbs.com>
2003-02-13 18:50 ` Roberto Mello <rmello@cc.usu.edu>
2003-02-14 00:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-14 09:12 ` Johnny Kristensen <johnnybo1@mail.tele.dk>
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