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(at)vmware(dot)com
> <mailto:hlinnakangas(at)vmware(dot)com>>:
>
> On 09/16/2014 10:15 AM, Pavel Stehule wrote:
>
> 2014-09-16 9:10 GMT+02:00 Heikki Linnakangas
> <hlinnakangas(at)vmware(dot)com <mailto: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);
>
>
> 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Ü