Re: Merging multiple values into comma-delimited list in a view
От | Bruno Wolff III |
---|---|
Тема | Re: Merging multiple values into comma-delimited list in a view |
Дата | |
Msg-id | 20040303220142.GA13235@wolff.to обсуждение исходный текст |
Ответ на | Merging multiple values into comma-delimited list in a view (Scott Goodwin <scott@scottg.net>) |
Список | pgsql-novice |
On Wed, Mar 03, 2004 at 15:25:18 -0500, Scott Goodwin <scott@scottg.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@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: 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@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 > ; > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend
В списке pgsql-novice по дате отправления:
Предыдущее
От: Scott GoodwinДата:
Сообщение: Merging multiple values into comma-delimited list in a view
Следующее
От: Bruce MomjianДата:
Сообщение: Re: Merging multiple values into comma-delimited list in a