aggregate generic ANYARRAY

[Tom Hebbron]

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,ANYEL EMENT) 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(relnam e)
FROM pg_catalog.pg_c lass c
INNER JOIN pg_catalog.pg_n amespace n ON (c.relnamespace = n.oid)
WHERE c.relkind = 'r'
GROUP BY c.relnamespace, n.nspname;

/*
-- results from test agg array
nspname | aggarray
----------------------------------------------------------------------
information_sch ema |
{sql_sizing,sql _sizing_profile s,sql_features, sql_implementat ion_info,sql_la n
guages,sql_pack ages}
pg_catalog |
{pg_shadow,pg_n amespace,pg_con version,pg_depe nd,pg_attrdef,p g_constraint,pg _
database,pg_des cription,pg_gro up,pg_proc,pg_r ewrite,pg_stati stic,pg_type,pg _
attribute,pg_cl ass,pg_inherits ,pg_index,pg_op erator,pg_opcla ss,pg_am,pg_amo p
,pg_amproc,pg_l anguage,pg_larg eobject,pg_aggr egate,pg_trigge r,pg_listener,p g
_cast}
(2 rows)

Time: 10.000 ms
test=#

*/

--
Tom Hebbron
www.hebbron.com
[phone] (Vicenza, Italy)

[Joe Conway]

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/curre...tic/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(relnam e) FROM
pg_catalog.pg_c lass c INNER JOIN pg_catalog.pg_n amespace n ON
(c.relnamespace = n.oid) WHERE c.relkind = 'r' GROUP BY
c.relnamespace, n.nspname;
nspname |

aggarray

--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
information_sch ema |
{sql_features,s ql_implementati on_info,sql_lan guages,sql_pack ages,sql_sizing ,sql_sizing_pro files}
MySchema | {Foo}
pg_catalog |
{pg_shadow,pg_c onstraint,pg_da tabase,pg_descr iption,pg_group ,pg_proc,pg_rew rite,pg_statist ic,pg_type,pg_a ttribute,pg_cla ss,pg_inherits, pg_index,pg_ope rator,pg_opclas s,pg_am,pg_amop ,pg_amproc,pg_l anguage,pg_larg eobject,pg_aggr egate,pg_trigge r,pg_listener,p g_cast,pg_names pace,pg_convers ion,pg_depend,p g_attrdef}
public | {foo,mytable,fw _chain}
(4 rows)

Time: 2.518 ms

HTH,

Joe
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org