Function array_agg(array) - Mailing list pgsql-hackers

From Ali Akbar
Subject Function array_agg(array)
Date
Msg-id CACQjQLo-bM30ydc+RmtTvYSdDUbRb3Ab+c6rRV_8iOiwbgXBGA@mail.gmail.com
Whole thread Raw
Responses Re: Function array_agg(array)
List pgsql-hackers
Greetings,

While looking for easier items in PostgreSQL Wiki's Todo List (this will be my 3rd patch), i found this TODO:

Add a built-in array_agg(anyarray) or similar, that can aggregate 1-dimensional arrays into a 2-dimensional array.

I've stumbled by this lack of array_agg(anyarray) sometimes ago in my work, so i decided to explore this.

Currently, if we try array_agg(anyarray), PostgreSQL behaves like this:

# select array_agg('{1,2}'::int[]);
ERROR:  could not find array type for data type integer[]

Reading implementation of array_agg, it looks like the array_agg function is generic, and can process any input. The error comes from PostgreSQL not finding array type for int[] (_int4 in pg_proc).

In PostgreSQL, any array is multidimensional, array type for any array is the same:
- the type of {1,2} is int[]
- {{1,2}, {3,4}} is int[]
- {{{1},{2}, {3} ,{4}}} is still int[]

So, can't we just set the typarray of array types to its self oid? (patch attached). So far:
- the array_agg is working and returning correct types:

backend> select array_agg('{1,2}'::int[]);
     1: array_agg    (typeid = 1007, len = -1, typmod = -1, byval = f)
    ----
     1: array_agg = "{"{1,2}"}"    (typeid = 1007, len = -1, typmod = -1, byval = f)
    ----

select array_agg('{''a'',''b''}'::varchar[]);
     1: array_agg    (typeid = 1015, len = -1, typmod = -1, byval = f)
    ----
     1: array_agg = "{"{'a','b'}"}"    (typeid = 1015, len = -1, typmod = -1, byval = f)
    ----


- Regression tests passed except for the pg_type sanity check while checking typelem relation with typarray:

SELECT p1.oid, p1.typname as basetype, p2.typname as arraytype,
       p2.typelem, p2.typlen
FROM   pg_type p1 LEFT JOIN pg_type p2 ON (p1.typarray = p2.oid)
WHERE  p1.typarray <> 0 AND
       (p2.oid IS NULL OR p2.typelem <> p1.oid OR p2.typlen <> -1);
!  oid  |    basetype    |   arraytype    | typelem | typlen
! ------+----------------+----------------+---------+--------
!   143 | _xml           | _xml           |     142 |     -1
!   199 | _json          | _json          |     114 |     -1
!   629 | _line          | _line          |     628 |     -1
!   719 | _circle        | _circle        |     718 |     -1
... (cut)


Aside from the sanity check complaints, I don't see any problems in the resulting array operations.

So, back to the question: Can't we just set the typarray of array types to its self oid?

Regards,
--
Ali Akbar
Attachment

pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: Wait free LW_SHARED acquisition - v0.9
Next
From: Tom Lane
Date:
Subject: Re: Function array_agg(array)