Transpose two-dimensional array from n rows to 2 columns

Question

Background

Using PostgreSQL 9.1, so WITH ORDINAL (a 9.4 feature) is not available.

Problem

Looking to simplify the code that pivots a two-dimensional array.

Code

A working, overly-verbose example that illustrates the problem is:

SELECT
  u.aspect,
  u.preference
FROM (
  SELECT
    t.aspect_preference AS aspect,
    -- Skip every second row
    seq % 2 AS seq,
    lead( aspect_preference, 1 ) OVER (ORDER BY t.seq) AS preference
  FROM (
    SELECT
      unnest( '{ {"COLOUR_SCHEME", "RASPBERRY_BLISS"}, {"FONT", "TERMES_HEROS"}, {"LIST_LAYOUT", "BULLET_SNOWFLAKE"} }'::text[] ) aspect_preference,
      -- Maintain array order after unnesting to a result set
      generate_series( 1,
       (array_ndims( '{ {"COLOUR_SCHEME", "RASPBERRY_BLISS"}, {"FONT", "TERMES_HEROS"}, {"LIST_LAYOUT", "BULLET_SNOWFLAKE"} }'::text[] ) *
        array_length( '{ {"COLOUR_SCHEME", "RASPBERRY_BLISS"}, {"FONT", "TERMES_HEROS"}, {"LIST_LAYOUT", "BULLET_SNOWFLAKE"} }'::text[], 1 ) )
      ) seq
  ) t
  ORDER BY
    t.seq
) u
WHERE
  u.seq = 1

This produces the correct results with the unnest'ed array rows pivoted into the desired columns:

aspect          | preference
----------------+--------------------
COLOUR_SCHEME   | RASPBERRY_BLISS
FONT            | TERMES_HEROS
LIST_LAYOUT     | BULLET_SNOWFLAKE

Question

What is a simpler way to write this query (e.g., fewer lines of code, fewer nested SELECTs, or fewer references to the array)?

Answer 1

Here is a possible solution:

WITH data AS (
    SELECT '{ {"COLOUR_SCHEME", "RASPBERRY_BLISS"}, {"FONT", "TERMES_HEROS"}, {"LIST_LAYOUT", "BULLET_SNOWFLAKE"} }'::text[] AS arr
)
SELECT 
    arr[i][1] AS aspect, 
    arr[i][2] AS preference
FROM 
    data, 
    generate_subscripts((SELECT arr FROM data), 1) i
;

The WITH part is there for centralizing data input. Otherwise it just takes all sub-arrays and selects the elements of those. Check this on SQLFiddle.