Forum OpenACS Q&A: something like join of tcl for postgres?

Collapse
1: something like join of tcl for postgres?
Posted by Rocael Hernández Rizzardini on 03/20/03 04:34 PM
Hi,
does anyone know about a function in postgres that joins the results of a query, something like this:

select join_func(select phone_number from telecom_numbers where  party_id = p.person_id, ', ') as phones, first_names, other_columns... from persons p;

I need this for an ad_table construction.

Collapse
2: Re: something like join of tcl for postgres? (response to 1)
Posted by Jonathan Ellis on 03/20/03 07:04 PM
there's no builtin, but you can create one thanks to postgresql's support for user-defined aggregate functions: 
CREATE FUNCTION comma_cat(text, text) RETURNS text AS
'select case
WHEN $2 is null or $2 = '''' THEN $1
WHEN $1 is null or $1 = '''' THEN $2
ELSE $1 || '', '' || $2
END'
LANGUAGE 'sql';

CREATE AGGREGATE list(
    sfunc1=comma_cat,
    basetype=text,
    stype1=text,
    initcond1=''
);

bf2=# select list(foo) from (select 'a' as foo union select 'b' as foo) as q1;
 list
------
 a, b
(1 row)
Collapse
3: Re: something like join of tcl for postgres? (response to 1)
Posted by Rocael Hernández Rizzardini on 03/20/03 09:49 PM
Hey Jonathan!
smart solution, thanks
=)
Collapse
4: Re: something like join of tcl for postgres? (response to 1)
Posted by Roberto Mello on 03/26/03 07:55 PM
Hey Jonathan,

Why don't you submit this recipe at the PostgreSQL Cookbook? (http://www.brasileiro.net/postgres/cookbook/)

-Roberto

Collapse
5: Re: something like join of tcl for postgres? (response to 1)
Posted by Jonathan Ellis on 03/26/03 08:06 PM
someone's already got a "comma" function that's pretty much the same tho he doesn't handle nulls.  it's the same idea...

I notice you have 80something recipes in 30-odd categories.  I'd suggest cutting the top-level categories down to 10 at most.

Collapse
6: Re: something like join of tcl for postgres? (response to 1)
Posted by Roberto Mello on 03/26/03 08:43 PM
Hi Jonathan,

Will do when I revamp the cookbook, which will only happen after the semester is over.

Thanks for the suggestion.

-Roberto