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

Thanks for the solution, Bruno. I spent some time last night working
with aggregates and group by after being pointed in the right direction
by you and Bruce and was at the point where I could get the correct
rows with a count of how many email addresses a person had. I had also
written an aggregate function to concatenate the addresses, but was
stuck on how to make that actually work within the query. Thanks again;
I'll be integrating this into the data model for the app I'm building.

/s.

On Mar 4, 2004, at 1:15 AM, Bruno Wolff III wrote:

> drop view  people_with_email;
> drop table people2email;
> drop table email;
> drop table people;
> drop aggregate concatenate(text);
> drop function join_with_comma(text,text);
>
> create function join_with_comma(text,text)
>   returns text
>   immutable strict language 'sql'
>   as 'select $1||'', ''||$2'
> ;
>
> create aggregate concatenate (
>   sfunc = join_with_comma,
>   basetype = text,
>   stype = text
> );
>
> create table email (
>     email_id        integer primary key,
>     email_address   text 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      text not null,
>     last_name       text 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,
>        c.email_address
>    from
>        people a,
>        (select r.person_id, concatenate(b.email_address) as
> email_address
>            from people2email r, email b
>            where r.email_id = b.email_id
>            group by r.person_id) as c
>     where a.person_id = c.person_id
> ;