Folks,
I have an interesting problem. For purpose of presentation to users,
I'd like to concatinate a list of VARCHAR values from a subtable. To
simplify my actual situation:
CREATE TABLE clients (client_id SERIAL PRIMARY KEY,client_name VARCHAR(50) );
CREATE TABLE client_contacts (contact_id SERIAL PRIMARY KEY,client_id REFERENCES clients(client_id),first_name
VARCHAR(25),last_nameVARCHAR(25) );
CLients:
1 McMurphy Assoc.
2 Ross Construction
Contacts
1 1 Jim Henson
2 1 Pat O'Gannet
3 2 Sara Vaugn
3 2 Bill Murray
etc.
What I'd like to be able to do is present a list of clients and their
comma-seperated contacts in paragraph form, hence:
Client Contacts
McMurphy Assoc. Jim Henson, Pat O'Gannet, Kelley Moore
Ross Construction Sara Vaugn, Bill Murray, Peter Frump, Siskel Ebert
I can figure out how to do this procedurally (using PL/pgSQL and a
cursor) but not how to do it declaratively (using only SQL). The reason
it's important to do it declaritively is that there are actually two
sub-tables with thousands of entries and the procedural approach is
rather slow.
Suggestions?
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
