Re: Populating an array from a select statement

Just out of curiously, why would you ever want to do that? (I am not saying
your method is wrong, but SQL should be ample, no?)

On Mon, Feb 18, 2008 at 9:15 AM, John Gunther <postgresql(at)bucksvsbytes(dot)com>
wrote:

> 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(at)domain(dot)com, john(at)gmail(dot)com, and john(at)yahoo(dot)com in the address table,
> select emails from user where id=25;
> will return:
> emails
> ----------------------------------------------------------------
> {john(at)domain(dot)com, john(at)gmail(dot)com, and john(at)yahoo(dot)com}
>
>
> You can use array_to_string() and array().
>
> Example:
>
> test=*# select * from mail_adr ;
> id | email
> ----+---------------
> 2 | foo(at)bar
> 2 | bar(at)batz
> 2 | foobar(at)barfoo
> (3 rows)
>
> test=*# select array_to_string(array(select email from mail_Adr where id=2), ', ');
> array_to_string
> ----------------------------------
> foo(at)bar, bar(at)batz, foobar(at)barfoo
> (1 row)
>
>
> Andreas
>
>
>
>