Re: Still don't know how to build this string ? - Mailing list pgsql-sql

From: <juerg.rietmann@pup.ch>

> I have a table with diameters and types. I need to build a comma separated
> string.
>
> typ       diam
> 01        800
[snip]
>
> select diam from zylinder where typ='01'
>
> should produce the string "800,840,870,1120"

Try the following as a starting point:

CREATE FUNCTION comma_join(text,text) RETURNS text AS '
BEGIN IF $1>\'\' AND $2>\'\' THEN   RETURN $1 || \',\' || $2; ELSE   RETURN $1 || $2; END IF;
END;
' LANGUAGE 'plpgsql';


CREATE AGGREGATE joinall ( sfunc = comma_join, basetype = text, stype = text, initcond = ''
);


\d dia         Table "dia"Attribute |  Type   | Modifier
-----------+---------+----------typ       | integer |diam      | integer |


SELECT typ,joinall(diam::text) FROM dia GROUP BY typ;typ |          joinall
-----+----------------------------  1 | 800,840,870,1120  2 | 760,800,900,1200,1234,1352
(2 rows)


Note the explicit cast of diam into text.

- Richard Huxton