Answer a question

A table

create table matrices(
 matrix text[][] not null);

It's value:

 insert into Matrices values
    (array[ ['1','2','3'],
            ['4','5','6'] ]),
    (array[ ['a','b','c'],
            ['d','e','f'] ]);

How Could I write SQL to transpose each array, so that the result looks like:

   matrix
-------------
{{1,4},{2,5},{3,6}}
{{a,d},{b,e},{c,d}}
(2 rows)

Answers

A bit verbose but here it is

SELECT array_agg(v ORDER BY j) matrix  FROM (
    SELECT rn, j, array_agg(v ORDER BY i) AS v FROM (
        SELECT rn, i, j, matrix[i][j] AS v FROM (
            SELECT generate_subscripts(matrix, 2) j, q.* FROM (
                SELECT ROW_NUMBER() OVER () AS rn,
                       generate_subscripts(matrix, 1) AS i, 
                       matrix                
                  FROM matrices
            ) q
        ) r
    ) s
     GROUP BY rn, j
) t
 GROUP BY rn
 ORDER BY rn;

Here is a dbfiddle demo

Or create a function

CREATE OR REPLACE FUNCTION transpose_2d(anyarray)
RETURNS anyarray AS $$
SELECT array_agg(v ORDER BY j) matrix  FROM (
    SELECT j, array_agg(v ORDER BY i) AS v FROM (
        SELECT i, j, $1[i][j] AS v FROM (
            SELECT generate_subscripts($1, 2) j, q.* FROM (
                SELECT generate_subscripts($1, 1) AS i, $1
            ) q
        ) r
    ) s
     GROUP BY j
) t
$$ LANGUAGE sql IMMUTABLE;

Usage

SELECT transpose_2d(matrix) 
  FROM matrices;

Here is a dbfiddle demo

Output (in both cases):

   matrix

{{1,4},{2,5},{3,6}} {{a,d},{b,e},{c,f}} (2 rows)