Multi-Dimensional Arrays in PostgreSQL
PostgreSQL supports arrays of various types, and handles also multi-dimensional arrays. Except that it does not support multi-dimensional arrays!Allow me to better explain. Multi-dimensional arrays are just an array that contains other arrays. In this sense, PostgreSQL does not provide a pure native multi-dimensional array, even if you can specify them. Let’s see this in action by means of
pg_typeof:
testdb=> select pg_typeof( array[ array[ 1, 2 ],
array[ 3, 4 ] ]::int[][] );
pg_typeof
-----------
integer[]
(1 row)
As you can see, the above matrix is repoted to be a single flat array.
Consider now the following function, that accepts a multi-dimensional array and returns a table:
CREATE OR REPLACE FUNCTION
f_matrix( int[][] )
RETURNS TABLE( a int, b int )
AS $CODE$
my ( $matrix ) = @_;
for my $row ( 0 .. $matrix->@* - 1 ) {
for my $column ( 0 .. $matrix->[ $row ]->@* - 1 ) {
return_next( { a => $row + 1,
b => $matrix->[ $row ]->[ $column ]
} );
}
}
return undef;
$CODE$
LANGUAGE plperl;
The above function, when invoked with a multi-dimensional array, works as expected:
testdb=> select *
from f_matrix( array[ array[ 1, 2 ],
array[ 3, 4 ] ]::int[][] );
a | b
---+---
1 | 1
1 | 2
2 | 1
2 | 2
(4 rows)
However, if you inspect the function, its signature clearly tells that the input parameter is a flat array:
testdb=> \df f_matrix
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+----------+-----------------------------+---------------------+------
public | f_matrix | TABLE(a integer, b integer) | integer[] | func
(1 row)
The same result, clearly, can be achieved by
plpgsql, for example implementing the following function:
CREATE OR REPLACE FUNCTION
f_matrix( m int[][] )
RETURNS TABLE( a int, b int )
AS $CODE$
DECLARE
r int;
c int;
BEGIN
FOR r IN 1 .. array_length( m, 1 ) LOOP
FOR c IN 1 .. array_length( m, 2 ) LOOP
a := r;
b := m[ r ][ c ];
RETURN NEXT;
END LOOP;
END LOOP;
RETURN;
END
$CODE$
LANGUAGE plpgsql;
In conclusion, PostgreSQL manages multi-dimensional arrays as flat lists, like what you would do in the C programming language. This does not mean that you cannot use multi-dimensional arrays in an comfortable and efficient way, rather that you need to take into account how they are handled by the database engine, especially when passing them to a function.