# 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.