Re: Anonymous code block with parameters

On 09/18/2014 08:41 PM, Andrew Dunstan wrote:
>
> On 09/18/2014 07:40 AM, Andres Freund wrote:
>> On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote:
>>> 2014-09-17 22:07 GMT+02:00 Vik Fearing <vik(dot)fearing(at)dalibo(dot)com>:
>>>
>>>> On 09/16/2014 10:09 AM, Heikki Linnakangas wrote:
>>>>> 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.
>>>> Because you still have to do
>>>>
>>>> SELECT pg_temp.my_temp_function(blah);
>>>>
>>>> to execute it.
>>>>
>>> this problem should be solvable. I can to use a temporary tables
>>> without
>>> using pg_temp schema.
>> I fail to see why that is so much preferrable for you to passing
>> parameter to DO?
>>
>> 1) You need to think about unique names for functions
>> 2) Doesn't work on HOT STANDBYs
>> 3) Causes noticeable amount of catalog bloat
>> 4) Is about a magnitude or two more expensive
>>
>> So yes, TEMPORARY FUNCTION would be helpful. But it's simply a different
>> feature.
>>
>
>
> +1
>
> If my memory isn't failing, when we implemented DO there were
> arguments for this additional feature, but we decided that it wouldn't
> be done at least on the first round. But we've had DO for a while and
> it's proved its worth. So I think now is a perfect time to revisit the
> issue.
One possible syntax would be extending WITH to somehow enable on-spot
functions in addition to on-spot views

WITH FUNCTION myfunc(...) RETURNS TABLE(...) LANGUAGE plpgsql AS $$
...
$$
SELECT f.*
FROM myfunc(x,y,z);

Cheers

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