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

I have worked up a complete example. You probably want to read up on
creating aggregate functions to see why the state function can be as
simple as it is.

I also changed the joins to use where clause conditions rather than
using the inner join syntax, because I am more comfortable with it. Though
with versions older than 7.4 (or perhaps 7.3), using the inner join syntax
forced the order in which the joins were done, which could cause a
performance problem.

If you care about what order the email addresses for a person
are listed in it is possible to do this with an order by
in the from item subselect with the group by clause.

The results I get are the following:

bruno=> select * from people_with_email;
 first_name | last_name  |              email_address
------------+------------+------------------------------------------
 Barney     | Rubble     | barney@hodown.com, barney.rubble@hey.org
 Fred       | Flintstone | fred.flintstone@blah.com
 Scott      | Goodwin    | scott@scottg.tv
(3 rows)

The modified creation script is as follows:

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
;