Thread: aggregate generic ANYARRAY

Thought others might find this useful (PostgreSQL 7.4+ only)

When used, it outputs an array of the inputs, in order.

CREATE OR REPLACE FUNCTION aggregate_array(ANYARRAY,ANYELEMENT) RETURNS
ANYARRAY AS '
   SELECT
    CASE
     WHEN $1 IS NULL
     THEN ARRAY[$2]
     WHEN $2 IS NULL
     THEN $1
     ELSE array_append($1,$2)
 END;
' LANGUAGE 'SQL';

CREATE AGGREGATE aggarray (BASETYPE = ANYELEMENT, SFUNC = aggregate_array,
STYPE = ANYARRAY);

-- *********************************************
--simple demonstration of aggarray
-- *********************************************
SELECT
 n.nspname,
 aggarray(relname)
FROM pg_catalog.pg_class c
INNER JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid)
WHERE c.relkind = 'r'
GROUP BY c.relnamespace,n.nspname;

/*
-- results from test agg array
      nspname       |  aggarray
----------------------------------------------------------------------
 information_schema |
{sql_sizing,sql_sizing_profiles,sql_features,sql_implementation_info,sql_lan
guages,sql_packages}
 pg_catalog         |
{pg_shadow,pg_namespace,pg_conversion,pg_depend,pg_attrdef,pg_constraint,pg_
database,pg_description,pg_group,pg_proc,pg_rewrite,pg_statistic,pg_type,pg_
attribute,pg_class,pg_inherits,pg_index,pg_operator,pg_opclass,pg_am,pg_amop
,pg_amproc,pg_language,pg_largeobject,pg_aggregate,pg_trigger,pg_listener,pg
_cast}
(2 rows)

Time: 10.000 ms
test=#

*/

--
Tom Hebbron
www.hebbron.com
+39 0444540626 (Vicenza, Italy)

Tom Hebbron wrote:
> When used, it outputs an array of the inputs, in order.
[...snip...]
> CREATE AGGREGATE aggarray (BASETYPE = ANYELEMENT, SFUNC = aggregate_array,
> STYPE = ANYARRAY);

There is really no need for the aggregate_array() function. See the
example in the docs:
   http://www.postgresql.org/docs/current/static/xaggr.html

So it would look like this:
CREATE AGGREGATE aggarray (basetype = anyelement, sfunc = array_append,
stype = anyarray, initcond = '{}' );

> -- *********************************************
> --simple demonstration of aggarray
> -- *********************************************
regression=# SELECT n.nspname, aggarray(relname) FROM
pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON
(c.relnamespace = n.oid) WHERE c.relkind = 'r' GROUP BY
c.relnamespace,n.nspname;
       nspname       |

                                aggarray


--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  information_schema |
{sql_features,sql_implementation_info,sql_languages,sql_packages,sql_sizing,sql_sizing_profiles}
  MySchema           | {Foo}
  pg_catalog         |

{pg_shadow,pg_constraint,pg_database,pg_description,pg_group,pg_proc,pg_rewrite,pg_statistic,pg_type,pg_attribute,pg_class,pg_inherits,pg_index,pg_operator,pg_opclass,pg_am,pg_amop,pg_amproc,pg_language,pg_largeobject,pg_aggregate,pg_trigger,pg_listener,pg_cast,pg_namespace,pg_conversion,pg_depend,pg_attrdef}
  public             | {foo,mytable,fw_chain}
(4 rows)

Time: 2.518 ms

HTH,

Joe