Re: array_agg and array_accum (patch)

On Thu, Nov 20, 2008 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Robert Haas" <robertmhaas@gmail.com> writes:
>> It looks to me like section 34.10 of the docs might benefit from some
>> sort of update in light of this patch, since the builtin array_agg now
>> does the same thing as the proposed user-defined array_accum, only
>> better.  Presumably we should either pick a different example, or add
>> a note that a builtin is available that does the same thing more
>> efficiently.
>
> I did the latter.  If you can think of an equally plausible and short
> example of a polymorphic aggregate, we could certainly replace the
> example instead ...

maybe show how to stack arrays?
see: http://www.nabble.com/text-array-accumulate-to-multidimensional-text-array-td20098591.html

IMO a good example of how you can write aggregates in a language other
than C, which is IMO an underutilized technique.

CREATE OR REPLACE FUNCTION array_cat1(p1 anyarray, p2 anyarray)
RETURNS anyarray AS
$$ SELECT CASE WHEN $1 =  '{}'::text[] THEN ARRAY[p2] ELSE ARRAY_CAT(p1, p2) END;
$$ LANGUAGE sql;

CREATE AGGREGATE array_stack(anyarray)
(  sfunc = array_cat1,  stype = anyarray,  initcond = '{}'
);

merlin