| From: | John Gunther <postgresql(at)bucksvsbytes(dot)com> |
|---|---|
| To: | pgsql-novice(at)postgresql(dot)org |
| Subject: | Re: Populating an array from a select statement |
| Date: | 2008-02-18 14:15:43 |
| Message-ID: | 47B9930F.6060102@bucksvsbytes.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-novice |
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
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.<br>
<br>
John<br>
<br>
A. Kretschmer wrote:
<blockquote cite="mid:20080218134715(dot)GC23570(at)a-kretschmer(dot)de"
type="cite">
<pre wrap="">am Mon, dem 18.02.2008, um 8:22:14 -0500 mailte John Gunther folgendes:
</pre>
<blockquote type="cite">
<pre wrap="">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 <a class="moz-txt-link-abbreviated" href="mailto:john(at)domain(dot)com">john(at)domain(dot)com</a>, <a class="moz-txt-link-abbreviated" href="mailto:john(at)gmail(dot)com">john(at)gmail(dot)com</a>, and
<a class="moz-txt-link-abbreviated" href="mailto:john(at)yahoo(dot)com">john(at)yahoo(dot)com</a> in the address table,
select emails from user where id=25;
will return:
emails
----------------------------------------------------------------
{<a class="moz-txt-link-abbreviated" href="mailto:john(at)domain(dot)com">john(at)domain(dot)com</a>, <a class="moz-txt-link-abbreviated" href="mailto:john(at)gmail(dot)com">john(at)gmail(dot)com</a>, and <a class="moz-txt-link-abbreviated" href="mailto:john(at)yahoo(dot)com">john(at)yahoo(dot)com</a>}
</pre>
</blockquote>
<pre wrap=""><!---->
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
</pre>
</blockquote>
<br>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 2.0 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Leo | 2008-02-18 15:08:06 | Full text indexing of documents |
| Previous Message | A. Kretschmer | 2008-02-18 13:47:15 | Re: Populating an array from a select statement |