On Tue, Jul 07, 2009 at 01:59:35AM +0430, Lee Harr wrote: > > Hi; > > I'm looking for a way to do this: > > > # \d tbl > Table "public.tbl" > Column | Type | Modifiers > --------+---------+----------- > idn | integer | > code | text | > # SELECT * FROM tbl; > idn | code > -----+------ > 1 | A > 2 | B > 2 | C > 3 | A > 3 | C > 3 | E > (6 rows) > # select idn, magic() as codes FROM tbl; > idn | codes > -----+------ > 1 | A > 2 | B, C > 3 | A, C, E > (3 rows) > > > Right now, I use plpgsql functions, but each time I do it > I have to rewrite the function to customize it. > > Is there a generic way to do this? An aggregate maybe?
The aggregate is called array_agg() and it's in 8.4. You can then wrap array_to_string() around it and get pretty formatting, as in: SELECT idn, array_to_string(array_agg(code),', ') AS codes FROM tbl; If you're not on 8.4 yet, you can create a similar aggregate with CREATE AGGREGATE. Cheers, David. -- David Fetter <da...@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
