Re: Dynamic prepare possible in plpgsql?
Re: Dynamic prepare possible in plpgsql?
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Collin Peters" 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
Re: Dynamic prepare possible in plpgsql?
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Collin Peters" 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
Re: Dynamic prepare possible in plpgsql?
От:
"Jonah H. Harris" <jonah.harris@gmail.com>
Дата:
On 5/1/07, Collin Peters 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/
Re: Dynamic prepare possible in plpgsql?
От:
Jim Nasby <decibel@decibel.org>
Дата:
On May 1, 2007, at 12:16 PM, Jonah H. Harris wrote: > On 5/1/07, Collin Peters 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)
Dynamic prepare possible in plpgsql?
От:
"Collin Peters" <cadiolis@gmail.com>
Дата:
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
Re: Dynamic prepare possible in plpgsql?
От:
"Collin Peters" <cadiolis@gmail.com>
Дата:
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 wrote: > "Collin Peters" 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 >
Re: Dynamic prepare possible in plpgsql?
От:
"Collin Peters" <cadiolis@gmail.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 wrote: > On 5/1/07, Collin Peters 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/ >
