hello,
try:
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);
or
CREATE AGGREGATE aggarray (basetype = anyelement, sfunc = array_append,
stype = anyarray, initcond = '{}' );
testdb011=> SELECT count(*), aggarray(prijmeni) FROM lide GROUP BY
prijmeni ~ '.*á';
regards
Pavel Stehule
On Fri, 6 Feb 2004, Andy Kriger wrote:
> I would like to select strings from a table and return them as an array
> For example,
> select new_array(name) from my_tbl
> would return
> String[] { name1, name2, name3, etc }
>
> Is this possible with built-in SQL/psql functions?
> If not, how hard would it be to write a function that does this? (given that
> I have coding experience but none writing pgsql functions)
>
> Andy Kriger | Software Mechanic | Greater Than One, Inc.
> 28 West 27th Street | 7th Floor | New York, NY 10001
> P: 212.252.7197 | F: 212.252.7364 | E: akriger@greaterthanone.com
>
>