Postgres Pro
Downloads
Home   >   mailing lists

Thread: Join field values

Join field values

From
veejar
Date:
Hello!

I have such field in my table:

field1
-------
1
2
3
4
5


I want to get such result from select:
'1,2,3,4,5' - join all values in field1 with ','
// result must be varchar.

Help to write SELECT-query for this task.

Re: Join field values

From
Jeff Davis
Date:
On Tue, 2007-06-05 at 19:33 +0300, veejar wrote:
> Hello!
>
> I have such field in my table:
>
> field1
> -------
> 1
> 2
> 3
> 4
> 5
>
>
> I want to get such result from select:
> '1,2,3,4,5' - join all values in field1 with ','
> // result must be varchar.
>

Look into writing a simple function:

http://www.postgresql.org/docs/current/static/plpgsql.html

Also, consider that you should have an ORDER BY somewhere, to make sure
the values get joined in the order that you expect.

Regards,
    Jeff Davis

Re: Join field values

From
Ragnar
Date:
On þri, 2007-06-05 at 19:33 +0300, veejar wrote:
> Hello!
>
> I have such field in my table:
>
> field1
> -------
> 1
> 2
> 3
> 4
> 5
>
>
> I want to get such result from select:
> '1,2,3,4,5' - join all values in field1 with ','
> // result must be varchar.
>
> Help to write SELECT-query for this task.

create an aggregate function and use that in your select.
http://www.postgresql.org/docs/8.2/static/sql-createaggregate.html

google reminded me of the mysql compatibility project,
whose implementation for group_concat() can be found here:

http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/mysqlcompat/mysqlcompat/aggregate.sql?rev=1.2&content-type=text/x-cvsweb-markup


gnari

Re: Join field values

From
Erwin Brandstetter
Date:
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

Re: Join field values

From
Erwin Brandstetter
Date:
On Jun 5, 10:14 pm, Erwin Brandstetter <brsaw...@gmail.com> wrote:
> CREATE OR REPLACE FUNCTION f_concat_comma(text, text)
(...)
>   LANGUAGE 'plpgsql' STABLE IMMUTABLE;

There's a typo. Should be:
LANGUAGE 'plpgsql' IMMUTABLE;

Regards
Erwin

Re: Join field values

From
Jerry Sievers
Date:
veejar <veejar.net@gmail.com> writes:

> Hello!
>
> I have such field in my table:
>
> field1
> -------
> 1
> 2
> 3
> 4
> 5
>
>
> I want to get such result from select:
> '1,2,3,4,5' - join all values in field1 with ','
> // result must be varchar.

No sense in writing your own func for this; the feature is already
provided.

select array_to_string(array(select * from generate_series(1,5)), ',');

 array_to_string
-----------------
 1,2,3,4,5
(1 row)

> Help to write SELECT-query for this task.

--
-------------------------------------------------------------------------------
Jerry Sievers   732 365-2844 (work)     Production Database Administrator
                305 321-1144 (mobil    WWW E-Commerce Consultant

Re: Join field values

From
Erwin Brandstetter
Date:
On Jun 7, 9:49 pm, j...@jerrysievers.com (Jerry Sievers) wrote:
>
> No sense in writing your own func for this; the feature is already
> provided.
>
> select array_to_string(array(select * from generate_series(1,5)), ',');

Tell me about redundant efforts! :)

Regards
Erwin