Re: Populating an array from a select statement

Thanks, Andreas. That was easy. I thought I new all the value expression types, but following your clue, I've learned the last two, ARRAY() and ROW(), from Section 4.2.

John

A. Kretschmer wrote:

am  Mon, dem 18.02.2008, um  8:22:14 -0500 mailte John Gunther folgendes: 

What's the most straightforward way to populate an array from a select 
statement? For example, using a fictional extension of SQL syntax, I'd like:

update users set emails=ARRAY[select email from address where userid=25] 
where id=25;

So if user 25  has emails john@domain.com, john@gmail.com, and 
john@yahoo.com in the address table,
select emails from user where id=25;
will return:                                        emails
----------------------------------------------------------------
{john@domain.com, john@gmail.com, and john@yahoo.com}   

You can use array_to_string() and array().

Example:

test=*# select * from mail_adr ;id |     email
----+--------------- 2 | foo@bar 2 | bar@batz 2 | foobar@barfoo
(3 rows)

test=*# select array_to_string(array(select email from mail_Adr where id=2), ', ');        array_to_string
----------------------------------foo@bar, bar@batz, foobar@barfoo
(1 row)


Andreas