Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A78979FB988 for ; Mon, 7 May 2007 14:20:47 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 91856-01 for ; Mon, 7 May 2007 14:20:43 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 0BCA19FBE4B for ; Mon, 7 May 2007 14:19:48 -0300 (ADT) Received: from [10.82.1.66] (docboy.decibel.org [65.86.67.34]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) by noel.decibel.org (Postfix) with ESMTP id 975AF56446; Mon, 7 May 2007 12:19:46 -0500 (CDT) In-Reply-To: <36e682920705011016u6af5810bv4fc3ad7feb0fbe8e@mail.gmail.com> References: <11208.1177980742@sss.pgh.pa.us> <36e682920705011016u6af5810bv4fc3ad7feb0fbe8e@mail.gmail.com> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <6960BD9A-8B30-4906-ACA2-7ACF5DAD02C0@decibel.org> Cc: "Collin Peters" , "Tom Lane" , pgsql-sql@postgresql.org Content-Transfer-Encoding: 7bit From: Jim Nasby Subject: Re: Dynamic prepare possible in plpgsql? Date: Mon, 7 May 2007 11:58:15 -0500 To: Jonah H. Harris X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/47 X-Sequence-Number: 28328 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)