pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDynamic prepare possible in plpgsql?
7+ messages / 4 participants
[nested] [flat]
* Dynamic prepare possible in plpgsql?
@ 2007-04-30 23:58 Collin Peters <cadiolis@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Collin Peters @ 2007-04-30 23:58 UTC (permalink / raw)
To: pgsql-sql
Is it possible to have a dynamic PREPARE statement in plpgsql?
Something like
PREPARE users_plan ( || 'text, text' || ) AS
INSERT INTO pp_users( || 'col1, col2' || )
VALUES($1, $2);
Regards,
Collin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dynamic prepare possible in plpgsql?
@ 2007-05-01 00:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: Collin Peters <cadiolis@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-05-01 00:52 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; +Cc: pgsql-sql
"Collin Peters" <cadiolis@gmail.com> writes:
> Is it possible to have a dynamic PREPARE statement in plpgsql?
Well, you could use plpgsql's EXECUTE to run SQL PREPARE and EXECUTE
commands, but it seems awfully brute-force. What do you really need
to accomplish here?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dynamic prepare possible in plpgsql?
@ 2007-05-01 16:52 Collin Peters <cadiolis@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Collin Peters @ 2007-05-01 16:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
I have a plpgsql function which is doing a loop over one table of user
data and then inserting that data in various tables. Example:
loop over user table (temp data)
insert into users1 table
insert into users2 table
etc....
end loop
Is it faster to use PREPARE for the various INSERT statements inside a
plpgsql function? Perhaps I am wrong and it does its PREPARE work
when the function is parsed.
Collin
On 4/30/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Collin Peters" <cadiolis@gmail.com> writes:
> > Is it possible to have a dynamic PREPARE statement in plpgsql?
>
> Well, you could use plpgsql's EXECUTE to run SQL PREPARE and EXECUTE
> commands, but it seems awfully brute-force. What do you really need
> to accomplish here?
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dynamic prepare possible in plpgsql?
@ 2007-05-01 17:16 Jonah H. Harris <jonah.harris@gmail.com>
parent: Collin Peters <cadiolis@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Jonah H. Harris @ 2007-05-01 17:16 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On 5/1/07, Collin Peters <cadiolis@gmail.com> wrote:
> Is it faster to use PREPARE for the various INSERT statements inside a
> plpgsql function? Perhaps I am wrong and it does its PREPARE work
> when the function is parsed.
IIRC, PLpgSQL automagically prepares each statement behind the scenes
on the first use.
--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dynamic prepare possible in plpgsql?
@ 2007-05-02 15:53 Collin Peters <cadiolis@gmail.com>
parent: Jonah H. Harris <jonah.harris@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Collin Peters @ 2007-05-02 15:53 UTC (permalink / raw)
To: pgsql-sql
So if I have an INSERT inside a LOOP in a plpgsql function, it is only
prepared once?
Regards,
Collin
On 5/1/07, Jonah H. Harris <jonah.harris@gmail.com> wrote:
> On 5/1/07, Collin Peters <cadiolis@gmail.com> wrote:
> > Is it faster to use PREPARE for the various INSERT statements inside a
> > plpgsql function? Perhaps I am wrong and it does its PREPARE work
> > when the function is parsed.
>
> IIRC, PLpgSQL automagically prepares each statement behind the scenes
> on the first use.
>
>
> --
> Jonah H. Harris, Software Architect | phone: 732.331.1324
> EnterpriseDB Corporation | fax: 732.331.1301
> 33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com
> Iselin, New Jersey 08830 | http://www.enterprisedb.com/
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dynamic prepare possible in plpgsql?
@ 2007-05-03 05:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Collin Peters <cadiolis@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2007-05-03 05:55 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; +Cc: pgsql-sql
"Collin Peters" <cadiolis@gmail.com> writes:
> So if I have an INSERT inside a LOOP in a plpgsql function, it is only
> prepared once?
Once per session, yes (barring some special cases like polymorphic
functions and trigger functions, which would get prepared once per
session per calling situation)
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Dynamic prepare possible in plpgsql?
@ 2007-05-07 16:58 Jim Nasby <decibel@decibel.org>
parent: Jonah H. Harris <jonah.harris@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Jim Nasby @ 2007-05-07 16:58 UTC (permalink / raw)
To: Jonah H. Harris <jonah.harris@gmail.com>; +Cc: Collin Peters <cadiolis@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On May 1, 2007, at 12:16 PM, Jonah H. Harris wrote:
> On 5/1/07, Collin Peters <cadiolis@gmail.com> wrote:
>> Is it faster to use PREPARE for the various INSERT statements
>> inside a
>> plpgsql function? Perhaps I am wrong and it does its PREPARE work
>> when the function is parsed.
>
> IIRC, PLpgSQL automagically prepares each statement behind the scenes
> on the first use.
BTW, a good use for a version of EXECUTE that accepted parameters is
the trigger on a partitioned table to direct inserts to the
appropriate partition. Currently, you have to quote_literal(coalesce
(NEW.field, 'NULL')) in the dynamic statement.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-05-07 16:58 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-30 23:58 Dynamic prepare possible in plpgsql? Collin Peters <cadiolis@gmail.com>
2007-05-01 00:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-01 16:52 ` Collin Peters <cadiolis@gmail.com>
2007-05-01 17:16 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-05-02 15:53 ` Collin Peters <cadiolis@gmail.com>
2007-05-03 05:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-07 16:58 ` Jim Nasby <decibel@decibel.org>
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