Thread: Dynamic prepare possible in plpgsql?
Is it possible to have a dynamic PREPARE statement in plpgsql? Something like PREPARE users_plan ( || 'text, text' || ) ASINSERT INTO pp_users( || 'col1, col2' || )VALUES($1, $2); Regards, Collin
"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
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 >
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/
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/ >
"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
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)