Thank you again Oliver for your reply.
I ended up parsing the parameters into the string block and then
preparing the statement for the short term.
I'm getting good performance currently.
For the long term I'll use your advice and create functions where appropriate.
Thanks again
On Wed, Apr 27, 2011 at 5:17 PM, Oliver Jowett <oliver@opencloud.com> wrote:
> On 28 April 2011 08:40, Brady S Edwards <brady.s.edwards@seagate.com> wrote:
>> Thank you for your reply Oliver.
>>
>> Is it possible to call an anonymous plpgsql block with bind variables?
>> With Oracle I would just prepare a statement like:
>> declare
>> ...
>> begin
>> xx = ?;
>> ...
>> end;
>>
>> These are created on the fly, so I can't really create stored
>> procedures for them.
>
> Looking at the description of DO, I don't see a way to provide parameters:
>
>> The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed
andexecuted a single time.
>
> If the body of the block is used just once, then it may be simplest to
> just interpolate the parameter values into the block body yourself
> while generating it.
> If you're going to reuse the block with different values, though, it's
> probably worthwhile creating a proper function, even if you turn
> around and drop it later. (You could perhaps create it in the pg_temp
> schema)
> If you don't have explicit reuse of these statements but the same
> generated block is likely to be generated again later, you could do
> something like a per-connection cache of function text to temporary
> function definition.
>
> Oliver
>
--
Brady Edwards
(720) 684-2984
Seagate Technology