---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
Here is an example I did to do concatenation:
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|sc...@scottg.tv
2|fred.fl...@blah.com
3|bar...@hodown.com
4|barney...@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
;
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)