Re: MAP syntax for arrays

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> On 5/8/18 10:18, Alvaro Herrera wrote:
>> How would you invoke it?  It seems you'd be forced to use EXECUTE in a
>> plpgsql function, or a C function.

> Yes, I was thinking about a C function.

The thing actually implementing MAP would presumably be in C,
so this doesn't seem like a problem technically.  But having to
create a function seems like a big usability stumbling block,
probably a big enough one to make the "select array_agg(expression)
from unnest(something)" approach more attractive.

I do see the usability benefit of a dedicated MAP syntax --- I'm
just afraid of getting out in front of the SQL committee on such
things.  I doubt that it's enough nicer than the sub-select way
to justify risking future standards-compliance issues.

Realistically, we're talking about this:

    select a, b, (select array_agg(x*2) from unnest(arraycol) x)
    from ...

versus something on the order of this:

    select a, b, map(x*2 over x from arraycol)
    from ...

Yeah, it's a bit shorter, but not that much ... and there's a lot
more you can do with the sub-select syntax, eg add a WHERE filter.

            regards, tom lane