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>:

On 09/16/2014 10:15 AM, Pavel Stehule wrote:

2014-09-16 9:10 GMT+02:00 Heikki Linnakangas <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Ü