Обсуждение: Merging multiple values into comma-delimited list in a view
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
;
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
Look at the PostgreSQL cookbook web site. Under "Aggregates" it has plpgsql functions that can do comma aggregation. --------------------------------------------------------------------------- Scott Goodwin 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: > > 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 > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
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
;
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 > ;