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

On Wed, Mar 03, 2004 at 15:25:18 -0500,
Scott Goodwin <scott(at)scottg(dot)net> 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(at)scottg(dot)tv
> Fred | Flintstone | fred(dot)flintstone(at)blah(dot)com
> Barney | Rubble | barney(at)hodown(dot)com
> Barney | Rubble | barney(dot)rubble(at)hey(dot)org
>
> What I really want is one person per row, with the email addresses
> concat'd together with commas, like this:

You can write a custom aggregate to do that. A sample function to do this
(concatenate strings) has been posted to at least one of the lists previously
and should be in the archives.

>
> first_name | last_name | email_address
> ------------+------------+--------------------------
> Scott | Goodwin | scott(at)scottg(dot)tv
> Fred | Flintstone | fred(dot)flintstone(at)blah(dot)com
> Barney | Rubble | barney(at)hodown(dot)com, barney(dot)rubble(at)hey(dot)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(at)scottg(dot)tv
> 2|fred(dot)flintstone(at)blah(dot)com
> 3|barney(at)hodown(dot)com
> 4|barney(dot)rubble(at)hey(dot)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