Re: JDBC parameter binding not working for PL/PGSQL block JDBC3 Version 9.0-801

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