From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | Scott Goodwin <scott(at)scottg(dot)net> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Merging multiple values into comma-delimited list in a view |
Date: | 2004-03-04 06:15:53 |
Message-ID: | 20040304061553.GA13809@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)hodown(dot)com, barney(dot)rubble(at)hey(dot)org
Fred | Flintstone | fred(dot)flintstone(at)blah(dot)com
Scott | Goodwin | scott(at)scottg(dot)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(at)scottg(dot)tv
2|fred(dot)flintstone(at)blah(dot)com
3|barney(at)hodown(dot)com
4|barney(dot)rubble(at)hey(dot)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
;
From | Date | Subject | |
---|---|---|---|
Next Message | Elinor Medezinski | 2004-03-04 10:42:23 | find close (duplicate) points + create index |
Previous Message | tully | 2004-03-04 02:46:42 | Re: Column Headings using Comment? |