Re: how can I select into an array?

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
>
>