Porting mysql group_concat to postgres

There's a very useful aggregate function in mysql that allows you to concatenate several row values into one single row value. Here is an example:



mysql> create table t1 (
-> t1_id numeric,
-> col2 varchar(50)
-> );
Query OK, 0 rows affected (0.18 sec)

mysql> create table t2 (
-> t2_id numeric,
-> t1_id numeric,
-> col2 varchar(50)
-> );
Query OK, 0 rows affected (0.17 sec)

mysql> insert into t1 values (1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into t2 values (1,1,'d'),(2,1,'f'),(3,2,'g'),(4,3,'f'),(5,3,'h'),(6,3,'i'),(7,3,'j');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> select * from t1;
+-------+------+
| t1_id | col2 |
+-------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+-------+------+
3 rows in set (0.01 sec)

mysql> select * from t2;
+-------+-------+------+
| t2_id | t1_id | col2 |
+-------+-------+------+
| 1 | 1 | d |
| 2 | 1 | f |
| 3 | 2 | g |
| 4 | 3 | f |
| 5 | 3 | h |
| 6 | 3 | i |
| 7 | 3 | j |
+-------+-------+------+
7 rows in set (0.00 sec)

mysql> select *,(select group_concat(col2) from t2 where t1_id = t.t1_id) from t1 as t;
+-------+------+-----------------------------------------------------------+
| t1_id | col2 | (select group_concat(col2) from t2 where t1_id = t.t1_id) |
+-------+------+-----------------------------------------------------------+
| 1 | a | d,f |
| 2 | b | g |
| 3 | c | f,h,i,j |
+-------+------+-----------------------------------------------------------+
3 rows in set (0.17 sec)


Postgres as you may have noticed doesn't have a function like this, so I created my own approach:

Remember that you should have enabled pspgsql for your database previously:

In the shell you must type :

createlang plpgsql -Upostgres -d template1



template1=# CREATE FUNCTION concat (text, text) RETURNS text AS '
template1'# DECLARE
template1'# result text;
template1'# BEGIN
template1'# IF $1 is not null THEN
template1'# result := $1 || $2;
template1'# END IF;
template1'#
template1'# RETURN result;
template1'# END;
template1'# ' LANGUAGE plpgsql;
CREATE FUNCTION

template1=# CREATE AGGREGATE row_concat(
template1(# sfunc = concat,
template1(# basetype = text,
template1(# stype = text,
template1(# initcond = ''
template1(# );
CREATE AGGREGATE


template1=# create table t1 (
template1(# t1_id numeric,
template1(# col2 varchar(50)
template1(# );
CREATE TABLE
template1=#
template1=# create table t2 (
template1(# t2_id numeric,
template1(# t1_id numeric,
template1(# col2 varchar(50)
template1(# );
CREATE TABLE

template1=# insert into t1 values (1,'a');
INSERT 0 1
template1=# insert into t1 values (2,'b');
INSERT 0 1
template1=# insert into t1 values (3,'c');
INSERT 0 1

INSERT 0 1
template1=# insert into t2 values (1,1,'d');
INSERT 0 1
template1=# insert into t2 values (2,1,'f');
INSERT 0 1
template1=# insert into t2 values (3,2,'g');
INSERT 0 1
template1=# insert into t2 values (4,3,'f');
INSERT 0 1
template1=# insert into t2 values (5,3,'h');
INSERT 0 1
template1=# insert into t2 values (6,3,'i');
INSERT 0 1
template1=# insert into t2 values (7,3,'j');
INSERT 0 1

template1=# select *,(select row_concat(col2) from t2 where t1_id = t.t1_id) from t1 as t;
t1_id | col2 | ?column?
-------+------+----------
1 | a | df
2 | b | g
3 | c | fhij
(3 rows)




I didn't want to set a comma as the default separator so the user can customize easily the string. Another important thing is that if there is a null value in any row then the result will be null, like most of the aggregate functions.


template1=# select *,(select row_concat(coalesce(col2,'') || ' , ') from t2 where t1_id = t.t1_id) from t1 as t;
t1_id | col2 | ?column?
-------+------+------------------
1 | a | d , f ,
2 | b | g ,
3 | c | f , h , i , j ,
(3 rows)