Re: how can I select into an array? - Mailing list pgsql-general

Thank you for your response - I should have mention I'm using Postgres 7.2.x
ANYARRAY does not appear to exist in that version
Is there a workaround?

-----Original Message-----
From: Pavel Stehule [mailto:stehule@kix.fsv.cvut.cz]
Sent: Friday, February 06, 2004 10:49 AM
To: Andy Kriger
Cc: Pgsql-General
Subject: Re: [GENERAL] 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
>
>