Re: Still don't know how to build this string ?

From: <juerg(dot)rietmann(at)pup(dot)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