Re: Anonymous code block with parameters

2014-09-16 10:09 GMT+02:00 Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>:

> On 09/16/2014 10:57 AM, Craig Ringer wrote:
>
>> On 09/16/2014 03:15 PM, Pavel Stehule wrote:
>>
>> Why we don't introduce a temporary functions instead?
>>>
>>
>> I think that'd be a lot cleaner and simpler. It's something I've
>> frequently wanted, and as Hekki points out it's already possible by
>> creating the function in pg_temp, there just isn't the syntax sugar for
>> "CREATE TEMPORARY FUNCTION".
>>
>> So why not just add "CREATE TEMPORARY FUNCTION"?
>>
>
> Sure, why not.
>
> It means two steps:
>>
>> CREATE TEMPORARY FUNCTION ... $$ $$;
>>
>> SELECT my_temp_function(blah);
>>
>> but I'm not personally convinced that a parameterised DO block is much
>> easier, and the idea just rings wrong to me.
>>
>
> With the above, you'll have to remember to drop the function when you're
> done, or deal with the fact that the function might already exist. That's
> doable, of course, but with a DO statement you don't have to.
>
> I agree with Pavel that the natural way to parameterise DO blocks, down
>> the track, will be to allow them to get (and set?) SQL-typed session
>> variables. Of course, we'd need to support them first ;-)
>>
>
> I responded to Pavel that using a session variable for a return value
> would be awkward, but using them as parameters would open a different can
> of worms. A session variable might change while the statement is run, so
> for anything but trivial DO blocks, a best practice would have to be to
> copy the session variable to a local variable as the first thing to do. For
> example, if you just use session variables arg1 and arg2, and you call a
> function that uses those same session variables for some other purposes,
> you will be surprised. Also, you'd have to remember to reset the session
> variables after use if there's any sensitive information in them, or you
> might leak them to surprising places. And if you forget to pass an
> argument, i.e. you forget to set a session variable that's used as an
> argument, the parser would not help you to catch your mistake but would
> merrily run the DO block with whatever the content of the argument happens
> to be.
>

Personally I can't to imagine some more complex code as DO block.

>
> Using session variables for arguments would be anything but natural.
>

> - Heikki
>
>