Re: how can I select into an array?

if you can in plpgsql 7.2.x return array of known type, you can replace
anyarray and anyelement like varchar[], varchar. But I don't know if it
7.2 supported.

Pavel

On Fri, 6 Feb 2004, Andy Kriger wrote:

> 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(at)kix(dot)fsv(dot)cvut(dot)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(at)greaterthanone(dot)com
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>