Merging multiple values into comma-delimited list in a view

Поиск
Список
Период
Сортировка
От Scott Goodwin
Тема Merging multiple values into comma-delimited list in a view
Дата
Msg-id E38CD2FD-6D50-11D8-AE0F-000A95A0910A@scottg.net
обсуждение исходный текст
Ответы Re: Merging multiple values into comma-delimited list in a view  (Bruno Wolff III <bruno@wolff.to>)
Re: Merging multiple values into comma-delimited list in a  (Bruce Momjian <pgman@candle.pha.pa.us>)
Re: Merging multiple values into comma-delimited list in a view  (Bruno Wolff III <bruno@wolff.to>)
Список pgsql-novice
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
       ;


В списке pgsql-novice по дате отправления:

Предыдущее
От: "M. Bastin"
Дата:
Сообщение: system tables
Следующее
От: Bruno Wolff III
Дата:
Сообщение: Re: Merging multiple values into comma-delimited list in a view