Re: SELECT INTO Array?

Hi Michael,

Thanks a lot for your response.

Basically I am selecting out "one row" from the database with multiple
columns (should have told you that earlier) so it seems that unfortunately
your elegant approach below might not work.  Although some of the columns
are text and some numeric I was planning to load them all into strings.
Perhaps I could convert the output to strings on the fly or something?

Otherwise I guess I could select the data into a temporary table and then
loop through each column adding the value to my array - will search around
for this.

Can you tell I'm normally a php developer? :-)

Thanks again,
Z.


----- Original Message ----- > 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/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>