output 2d array from function as rows and columns

Question

I'm working on a function where I create a 2d array something along the lines of...

{{1, 'John', 'Smith'}, {1, 'Jane', 'Doe'}, {3, 'Tony', 'Stark'}}

The function I'm building combines data from a few different queries and even though it may seem unnecessary is the only way I can seem to combine my data so far.

The problem I'm running into is getting my array displayed as a table.

I'm calling my function like this:

SELECT * FROM my_func();

^^ And getting the array output from above. ^^

I'd like to be able to return something like this: - Even better if I can label the columns.

Col1   | Col2   | Col3
1      | John   | Smith
2      | Jane   | Doe
3      | Tony   | Stark

I tried: SELECT * FROM my_func(); which was better, but still unusable as it put everything into a single column.

Col1
1
John
Smith
2
Jane
Doe
3
Tony
Stark

Answer 1

Information is missing in the question, so assuming you want to return three columns (int, text, text). Also assuming current Postgres 9.5.

Plain SQL

Use generate_subscripts() in a LATERAL join:

WITH t(a) AS (
   SELECT '{{1, John, Smith}, {1, Jane, Doe}, {3, Tony, Stark}}'::text[]
   )
SELECT t.a[i][1]::int AS col1, t.a[i][2] AS col2, t.a[i][3] AS col3
FROM t, generate_subscripts(t.a, 1) i;

Related:

• Passing an array of arrays as parameter to a function

PL/pgSQL

Loop with FOREACH target [ SLICE number ] IN ARRAY expression:

CREATE OR REPLACE FUNCTION my_func(_arr text[])
  RETURNS TABLE (col1 int, col2 text, col3 text) AS
$func$
DECLARE
   m   text[];
BEGIN
   FOREACH m SLICE 1 IN ARRAY _arr
   LOOP
      RETURN QUERY SELECT m[1]::int, m[2], m[3];
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Call:

WITH t(a) AS (
   SELECT '{{1, John, Smith}, {1, Jane, Doe}, {3, Tony, Stark}}'::text[]
   )
SELECT f.*
FROM   t, my_func(t.a) f;

Related:

• Postgres - array for loop

---

Each returns:

 col1 | col2 | col3
------+------+-------
    1 | John | Smith
    1 | Jane | Doe
    3 | Tony | Stark