Re: Anonymous code block with parameters

On 09/16/2014 09:44 AM, Pavel Stehule wrote:
>
>
> 2014-09-16 9:24 GMT+02:00 Heikki Linnakangas <hlinnakangas@vmware.com
> <mailto:hlinnakangas@vmware.com>>:
>
>     On 09/16/2014 10: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);
>
>
>         Why we don't introduce a temporary functions instead?
>
>
>     You can already do that:
>
>     create function pg_temp.tempfunc(i int4) returns int4 as $$ begin
>     end; $$ language plpgsql;
>
>
> it looks much more like workaround than supported feature.
a straightforward CREATE TEMPORARY FUNCTION implementation would do
exactly that.
>  
>
>
>     Compared to DO, you have to do extra steps to create the function,
>     and drop it when you're done. And you can't use them in a hot
>     standby, because it changes the catalogs. (although a better
>     solution to that would be to make it work, as well as temporary
>     tables, but that's a much bigger project).
>
>     Maybe we don't need any of this, you can just use temporary
>     function. But clearly someone though that DO statements are useful
>     in general, because we've had temporary functions for ages and we
>     nevertheless added the DO statement.
>
>
> I afraid so we create little bit obscure syntaxes, without real effect
> and real cost
I would agree with you if we had session-level "temporary" functions

But then we would still miss anonymous/in-line/on-the-spot functions
>
> Any new useful syntax should be clean, simple, natural and shorter
> than create function ...
This is not how SQL works, nor ADA nor pl/pgsql ;)
>
> and without risks a conflicts with ANSI SQL
>
> I prefer a typed session variables, where is not risk of SQL injection
> or some performance lost. The benefit of typed server side variables
> can be for wide group of users.
Agreed

but this would be a much bigger project, as Heikki already mentioned re.
temp things on replicas.

especially if typed session variables could hold temporary functions .

DECLARE FUNCTION mytempfucntion () ...


Cheers

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