Re: Join field values - Mailing list pgsql-general

On Jun 5, 7:39 pm, g...@hive.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