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,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_i mplementation_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_rewrit e,pg_statistic,pg_type,pg_
attribute,pg_class,pg_inherits,pg_index,pg_operato r,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
[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(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_language s,sql_packages,sql_sizing,sql_sizing_profiles}
MySchema | {Foo}
pg_catalog |
{pg_shadow,pg_constraint,pg_database,pg_descriptio n,pg_group,pg_proc,pg_rewrite,pg_statistic,pg_type ,pg_attribute,pg_class,pg_inherits,pg_index,pg_ope rator,pg_opclass,pg_am,pg_amop,pg_amproc,pg_langua ge,pg_largeobject,pg_aggregate,pg_trigger,pg_liste ner,pg_cast,pg_namespace,pg_conversion,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