Re: Anonymous code block with parameters

On Mon, Sep 22, 2014 at 2:49 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-09-22 15:46:48 -0400, Peter Eisentraut wrote:
>> On 9/18/14 7:40 AM, Andres Freund wrote:
>> > 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
>>
>> Doesn't this apply to all temporary objects? It would also be great to
>> have temporary tables, temporary indexes, temporary triggers, temporary
>> extensions, etc. that don't have the above problems. I think inventing
>> a separate mechanism for working around each instance of this problem
>> would end up being very confusing.
>
> Meh. Those aren't comparable. TEMPORARY TABLES/INDEXES/... all live
> beyond a single statement. What's being discussed here doesn't.

Even if that wasn't true, 'DO' doesn't involve changes to system
catalogs whereas temporary functions would. With a little imagination
I could come up a with a scenario involving a script of a whole bunch
of repeated trivial DO statements which would involve a lot less
beating on the system catalogs.

When the data-modifying-with feature was considered, an implementation
that relied on temp tables was rejected at least in part because of
system catalog thrash and poorer performance for very trivial queries.

So, to me, DO vs CREATE FUNCTION has nothing to do with passing
arguments and/or returning data. It has to do with lifespan; single
call of the function body only, use DO, otherwise, create a function.

merlin