Re: Join field values

On Jun 5, 7:39 pm, g(dot)(dot)(dot)(at)hive(dot)is (Ragnar) wrote:
>
> create an aggregate function and use that in your select.http://www.postgresql.org/docs/8.2/static/sql-createaggregate.html

Of course you could do that. And it would look like that:

CREATE OR REPLACE FUNCTION f_concat_comma(text, text)
RETURNS text AS
$BODY$
BEGIN
RETURN $1 || ', ' || $2;
END;
$BODY$
LANGUAGE 'plpgsql' STABLE IMMUTABLE;

CREATE AGGREGATE concat_comma(
BASETYPE=text,
SFUNC=f_concat_comma,
STYPE=text
);

SELECT concat_comma(field1) FROM mytbl;

--Or, if want the values ordered:
SELECT concat_comma(field1) FROM (SELECT field1 FROM mytbl ORDER by
field1) x;

And that's what I did - before I found out about this simpler way:

SELECT array_to_string(ARRAY(SELECT field1 FROM mytbl ORDER by
field1), ', ');

More info:
http://www.postgresql.org/docs/8.2/static/functions-array.html

Regards
Erwin