Re: how can I select into an array?

["Andy Kriger" <akriger@greaterthanone.com>]

Title: 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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andy Kriger
Sent: Friday, February 06, 2004 10:37 AM
To: Pgsql-General
Subject: 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@greaterthanone.com