Re: SELECT INTO Array?

On Fri, Apr 01, 2005 at 03:22:58PM +1200, Zitan Broth wrote:
>
> I was wondering if there was an easy way of converting the output
> from a SELECT statement into an Array ..... I'd like to be able to
> SELECT INTO MyArray[] * FROM TABLE WHERE ID=1 ..... is this possible?

In 7.4 and later you can use an array constructor with a subquery
that selects a single column:

CREATE TABLE foo (
id integer PRIMARY KEY,
name text NOT NULL
);

INSERT INTO foo (id, name) VALUES (1, 'John');
INSERT INTO foo (id, name) VALUES (2, 'David');
INSERT INTO foo (id, name) VALUES (3, 'James');

SELECT ARRAY(SELECT id FROM foo WHERE name LIKE 'J%');
?column?
----------
{1,3}
(1 row)

Here's a PL/pgSQL example:

DECLARE
a integer[] := ARRAY(SELECT id FROM foo WHERE name LIKE 'J%');

Selecting multiple columns into an array doesn't work in SQL or
PL/pgSQL -- that could cause problems in the general case because
columns might have different types and arrays contain elements of
the same type. However, some other languages' interfaces to
PostgreSQL can return rows as arrays (e.g., Perl DBI).

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/