PostgreSQL

How to use array_agg() function in less than PostgreSQL-8.3

What is array_agg() function in PostgreSQL

PostgreSQL-8.4 or above versions support array_agg() function, but the function doesn't exist less than PostgreSQL-8.3 version.

The array_agg() function transforms rows into array very easily, so I really need the function in less than PostgreSQL-8.3 version. In this article, I create the array_agg() function.

Create test table and data

In order to make sure we can use array_agg() function in PostgreSQL-8.4 and we cannot use the function in less than PostgreSQL-8.2, let's create the following table and data in both databases.

=> CREATE TABLE colors (id serial, name text NOT NULL);
CREATE TABLE

=> INSERT INTO colors (name) VALUES ('red'),('blue'),('yellow');
INSERT 0 3

=> SELECT * FROM colors;
+----+--------+
| id |  name  |
+----+--------+
|  1 | red    |
|  2 | blue   |
|  3 | yellow |
+----+--------+
(3 rows)

Use array_agg() function in PostgreSQL-8.4

First off, I called array_agg() function in PostgreSQL-8.4 database. I could convert rows into array as follows.

=> SELECT name FROM colors;
+--------+
|  name  |
+--------+
| red    |
| blue   |
| yellow |
+--------+
(3 rows)

=> SELECT array_agg(name) FROM colors;
+-------------------+
|     array_agg     |
+-------------------+
| {red,blue,yellow} |
+-------------------+
(1 row)

Also, let's make sure the array_agg() function exists in PostgreSQL-8.4 database.

[PostgreSQL-8.4]
=> \df array_agg
                            List of functions
+------------+-----------+------------------+---------------------+------+
|   Schema   |   Name    | Result data type | Argument data types | Type |
+------------+-----------+------------------+---------------------+------+
| pg_catalog | array_agg | anyarray         | anyelement          | agg  |
+------------+-----------+------------------+---------------------+------+
(1 row)

Then, check the function in PostgreSQL-8.2 database. It doesn't exist.

[PostgreSQL-8.2]
=> \df array_agg
                    List of functions
+--------+------+------------------+---------------------+
| Schema | Name | Result data type | Argument data types |
+--------+------+------------------+---------------------+
+--------+------+------------------+---------------------+
(0 rows)

However, we can create the array_agg() function very easily, so let's create it in PostgreSQL-8.2 database.

Create array_agg() function in PostgreSQL-8.2

Connect to a database which you would like to use the array_agg() function, then run the following SQL (CREATE AGGREGATE statement), so that the array_agg() function will be created/registered into PostgreSQL database.

=> CREATE AGGREGATE array_agg(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
INITCOND='{}'
);

Once you created the function, let's call the function as follows.

=> SELECT version();
+-----------------------------------------------------------------------------------------------------+
|                                               version                                               |
+-----------------------------------------------------------------------------------------------------+
| PostgreSQL 8.2.19 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52) |
+-----------------------------------------------------------------------------------------------------+
(1 row)

=> SELECT name FROM colors;
+--------+
|  name  |
+--------+
| red    |
| blue   |
| yellow |
+--------+
(3 rows)

=> SELECT array_agg(name) FROM colors;
+-------------------+
|     array_agg     |
+-------------------+
| {red,blue,yellow} |
+-------------------+
(1 row)

As above query, we could successfully converted rows into array in less than PostgreSQL-8.3 version.