Re: Merging multiple values into comma-delimited list in a

Look at the PostgreSQL cookbook web site.  Under "Aggregates" it has
plpgsql functions that can do comma aggregation.

---------------------------------------------------------------------------

Scott Goodwin wrote:
> Need some help with the following example. I have email, people and
> people2email tables. The people2email table is a one-to-many with one
> person able to have many email addresses, and the people_with_email
> view ties it all together for me. Here's the output when I do a "select
> * from people_with_email;"
>
>   first_name | last_name  |      email_address
> ------------+------------+--------------------------
>   Scott      | Goodwin    | scott@scottg.tv
>   Fred       | Flintstone | fred.flintstone@blah.com
>   Barney     | Rubble     | barney@hodown.com
>   Barney     | Rubble     | barney.rubble@hey.org
>
> What I really want is one person per row, with the email addresses
> concat'd together with commas, like this:
>
> first_name | last_name  |      email_address
> ------------+------------+--------------------------
>   Scott      | Goodwin    | scott@scottg.tv
>   Fred       | Flintstone | fred.flintstone@blah.com
>   Barney     | Rubble     | barney@hodown.com, barney.rubble@hey.org
>
> My question is: how do I modify the select statement that generates the
> people_with_email view so that it generates the output I want?
>
> I'll gladly tie myself to any PG-specific feature that does the job as
> I'll not be moving to any other database software within my lifetime if
> I can help it (and I can:). I wouldn't mind using arrays, but can't
> really change the data type of a column in a view (is there a way to
> CAST it?). Might be able to use a materialized view, which could then
> support array columns, but I'd be satisfied with a plain text string
> that I can split on with Tcl.
>
> The datamodel, with the view and dummy data is below.
>
> thanks,
>
> /s.
>
> ======== data model =========
>
> drop view  people_with_email;
> drop table people2email;
> drop table email;
> drop table people;
>
> create table email (
>     email_id        integer primary key,
>     email_address   varchar(128) not null unique
> );
>
> copy email from stdin with delimiter '|';
> 1|scott@scottg.tv
> 2|fred.flintstone@blah.com
> 3|barney@hodown.com
> 4|barney.rubble@hey.org
> \.
>
> create table people (
>     person_id       integer primary key,
>     first_name      varchar(32) not null,
>     last_name       varchar(32) not null
> );
>
> copy people from stdin with delimiter '|';
> 1|Scott|Goodwin
> 2|Fred|Flintstone
> 3|Barney|Rubble
> \.
>
> create table people2email (
>     person_id       integer references people (person_id),
>     email_id        integer references email (email_id)
> );
>
> copy people2email from stdin with delimiter '|';
> 1|1
> 2|2
> 3|3
> 3|4
> \.
>
> create view people_with_email as
>    select
>        a.first_name,
>        a.last_name,
>        b.email_address
>    from
>        people a
>    inner join
>        people2email r
>        on
>        a.person_id = r.person_id
>    inner join
>        email b
>        on
>        b.email_id = r.email_id
>        ;
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073