Re: Anonymous code block with parameters

On 09/16/2014 09:15 AM, Pavel Stehule wrote:
>
>
> 2014-09-16 9:10 GMT+02:00 Heikki Linnakangas <hlinnakangas@vmware.com
> <mailto:hlinnakangas@vmware.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?

As I see it, the DO blocks _are_ temporary (or rather in-line)
functions, though quite restricted in not taking arguments and not
returning anything.


DO you have a better syntax for "temporary / in-line functions" ?

What I would like to to is to make DO blocks equal to any other data
source, so you could do

WITH mydoblock(col1, col2)(DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE
(col1 text, col2 int4))
SELECT * FROM mydoblock;

or

SELECT *
FROM (DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text, col2
int4)) mydoblock;

and for the parameter-taking version

SELECT (DO $$ ... $$ LANGUAGE plpgsql USING (user) RETURNS
int4)(username) AS usernum
FROM users;


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ