Re: how can I select into an array?

this is a followup to my original message for anyone else stuck on older
psql versions...

i wrote a function to accomplish selecting into an array which works in
7.2.1
you will need to 'createlang plpgsql' (see the docs on procedural languages)
before you can use it
it takes a SQL query as an arg and returns an array aggregated from the
result column named 'arrayval'
it is written to work with varchars but could easily be modified to work
with other data types

-- create an array from the results of a query
-- query must return a column named arrayval
-- $1 = query string
CREATE OR REPLACE FUNCTION array_query(VARCHAR)
RETURNS VARCHAR[]
AS '
DECLARE
query ALIAS FOR $1;
rec RECORD;
str VARCHAR;
arr VARCHAR[];
BEGIN
str := ''{'';
FOR rec IN EXECUTE query LOOP
str := str || ''"'' || rec.arrayval || ''"'' || '','';
END LOOP;
str := str || ''}'';
SELECT INTO arr str;
RETURN arr;
END;
' LANGUAGE 'plpgsql';

_____

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Andy Kriger
Sent: Friday, February 06, 2004 10:37 AM
To: Pgsql-General
Subject: [GENERAL] how can I select into an array?

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