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

2008/11/18 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> 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?

array_accum was terrible slow. Non aggregate construct is much faster
- we should to build array in one simple cycle.

Pavel

>
>
> Sam
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>