Re: [GENERAL] how can I select into an array?

[Pavel Stehule]

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: [EMAIL PROTECTED] 
> 
> 


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly