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 по дате отправления:
Следующее
От: Bruno Wolff IIIДата:
Сообщение: Re: Merging multiple values into comma-delimited list in a view