PotgreSQL 2D array to rows

Question

Hello,

I am new to PostgreSQL array's.

I am trying to a write a procedure to convert array-into-rows, and wanted following output:

alphabet | number
---------+----------
       A |      10
       B |      10
       C |       6
       D |       9
       E |       3



from following:


id |                                     alphabet_series                                                                                
-------+--------------------------------------------------------------------------------------------------
     1 | {{A,10},{B,10},{C,6},{D,9},{E,3},{F,9},{I,10},{J,17},{K,16},{L,17},{M,20},{N,13},{O,19}}

I have searched for array-to-rows functions, but they all seems to accept 1-d array. but in this case, it is 2-d array.

Any pointers will be appreciated.

Many thanks.

Answer 1

I would create a table with 3 fields :

CREATE TABLE matrix ( ROW CHAR, COL INT, VALUE )

and fill it with your matrix then you can query it using :

SELECT ROW, SUM(VALUE) FROM matrix GROUP BY COLUMN ORDER BY ROW;

or similar.

This will work fine if the matrix is of a reasonable size (like not billions of elements)

Answer 2

CREATE TABLE two_d (x text, y text);

CREATE OR REPLACE FUNCTION unnest_2d(anyarray)
  RETURNS SETOF two_d AS
$BODY$
  SELECT $1[i][1], $1[i][2] FROM
   generate_series(array_lower($1,1),
                    array_upper($1,1)) i
$BODY$
  LANGUAGE 'sql' IMMUTABLE;

SELECT * FROM unnest_2d('{{A,a},{B,b}}'::text[][]);