Re: Anonymous code block with parameters

On 09/16/2014 09:38 AM, Kalyanov Dmitry wrote:
> I'd like to propose support for IN and OUT parameters in 'DO' blocks.
>
> Currently, anonymous code blocks (DO statements) can not receive or
> return parameters.
>
> I suggest:
>
> 1) Add a new clause to DO statement for specifying names, types,
> directions and values of parameters:
>
> DO <code> [LANGUAGE <lang>] [USING (<arguments>)]
>
> where <arguments> has the same syntax as in
> 'CREATE FUNCTION <name> (<arguments>)'.
>
> Example:
>
> do $$ begin z := x || y; end; $$
> language plpgsql
> using
> (
> x text = '1',
> in out y int4 = 123,
> out z text
> );
>
> 2) Values for IN and IN OUT parameters are specified using syntax for
> default values of function arguments.
>
> 3) If DO statement has at least one of OUT or IN OUT parameters then it
> returns one tuple containing values of OUT and IN OUT parameters.
>
> Do you think that this feature would be useful? I have a
> proof-of-concept patch in progress that I intend to publish soon.

There are two features here. One is to allow arguments to be passed to
DO statements. The other is to allow a DO statement to return a result.
Let's discuss them separately.

1) Passing arguments to a DO block can be useful feature, because it
allows you to pass parameters to the DO block without injecting them
into the string, which helps to avoid SQL injection attacks.

I don't like the syntax you propose though. It doesn't actually let you
pass the parameters out-of-band, so I don't really see the point. I
think this needs to work with PREPARE/EXECUTE, and the protocol-level
prepare/execute mechanism. Ie. something like this:

PREPARE mydoblock (text, int4) AS DO $$ ... $$
EXECUTE mydoblock ('foo', 123);

2) Returning values from a DO block would also be handy. But I don't see
why it should be restricted to OUT parameters. I'd suggest allowing a
RETURNS clause, like in CREATE FUNCTION:

DO $$ ... $$ LANGUAGE plpgsql RETURNS int4;

or

DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text, col2 int4);

- Heikki