Re: Anonymous code block with parameters

2014-09-16 10:01 GMT+02:00 Hannu Krosing <hannu(at)2ndquadrant(dot)com>:

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

sure -- two languages are hard to maintain, hard to develop. Three ... my
God :)

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

probably

>
> especially if typed session variables could hold temporary functions .
>
> DECLARE FUNCTION mytempfucntion () ...
>

Why not? When somebody solves a work with dynamic planning and solves all
issues related to stored plans. Still we have a issues, when some changes
needs a session cleaning (disconnect)

Regards

Pavel

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