Re: Anonymous code block with parameters

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.

Using session variables for arguments would be anything but natural.

- Heikki