Skip site navigation (1) Skip section navigation (2)

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

From: David Fetter <david(at)fetter(dot)org>
To: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: [where's the documentation] subselect to a string
Date: 2006-05-26 18:04:12
Message-ID: 20060526180412.GF17861@fetter.org (view raw or flat)
Thread:
Lists: sfpug
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!

In response to

sfpug by date

Next:From: Josh BerkusDate: 2006-05-26 18:11:42
Subject: Joint Meeting with SVOSUG -- June 22nd
Previous:From: Ben RiddellDate: 2006-05-26 17:48:50
Subject: [where's the documentation] subselect to a string

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group