Re: Anonymous code block with parameters

2014-09-16 9:10 GMT+02:00 Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>:

> 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

Why we don't introduce a temporary functions instead?

Pavel

>
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>