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)
