Re: is any reason why only one columns subselect are allowed in array()?

On Tue, Nov 18, 2008 at 06:22:21PM +0000, Sam Mason wrote:
> I've always been taught to design things so that the that the basic
> semantics should be as simple as possible which maintaining useful

this should of course be "while"! ^^^^^

> performance.

> CREATE FUNCTION array_concat_(ANYARRAY,ANYARRAY) RETURNS ANYARRAY
> AS $$ SELECT array_cat($1,ARRAY[$2]); $$
> LANGUAGE SQL
> IMMUTABLE;
>
> CREATE AGGREGATE array_concat (ANYARRAY) (
> sfunc = array_concat_,
> stype = ANYARRAY,
> initcond = '{}'
> );
>
> A demo query being:
>
> SELECT array_concat(a) FROM (VALUES
> (ARRAY[1,2,3]),
> (ARRAY[5,6,7]),
> (ARRAY[7,8,9])) x(a);
>
> is that somewhat correct?

I've just realized that this doesn't work in simple cases like the
following:

SELECT (SELECT array_concat(a) FROM (VALUES
(1), (5), (7)) x(a));

To do this, another pair of function and aggregate is needed, this time
parametrized on ANYNONARRAY rather than ANYARRAY. It would be nice if
PG's type system was sufficiently clever to allow the above definition
to be parametrized on ANYELEMENT (or maybe something else) and it all
work out, but never mind...

Another point came to mind as well; why does array_accum pretty much
duplicate the behavior of array(subquery)? The following looks like a
useful and general transform:

SELECT accum_fn(subquery);

to:

SELECT (accum_fn(c1..cn) FROM (subquery) x(c1..cn));

What have I missed?

Sam