Re: [where's the documentation] subselect to a string

On Fri, May 26, 2006 at 10:48:50AM -0700, Ben Riddell wrote:
> Hiya,
>
> Bit of a basic question.
>
> I has asked about a year ago about how to deal with data from a
> subselect, and was regaled with tales of the wondrous new
> functionality of the release known as "8".
>
> Okay. Got that version. (Better late than never.)
>
> Now I need to (sub) select an arbitrary number of string values and
> return them as a single string value. Can't simply concatenate, 'cuz
> of the arbitrariness.

Sure you can :)

SELECT
array_to_string(
ARRAY(
SELECT your_column /* Just one column.
Use || if needed to make this so */
FROM your_table t1
WHERE t1.your_table_id = t0.your_table_id
),
','
) AS "comma-separated string"
FROM
other_table t0
WHERE
t0.foo = 'bar'
AND
t0.baz < 40
;

> Where is the documentation for what I need? I'm just not finding it
> on the site or through Google. Is it under "cast" or something
> else?

The ARRAY() constructor and array_to_string() function are documented,
but you'd have had to know to look for them, so this (or
<irc://irc.freenode.net/postgresql>) is probably the best way to find
out.

HTH :)

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!