From: <[EMAIL PROTECTED]>
> 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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
