Convert array returned from function to columns

I have a function in PostgreSQL (PLPGSQL) that returns an array containing two elements. When I run a select statement calling the function, I get a column containing the array (as expected):

{1, 2}

What I really would like to do is extract these elements to be their own columns:

[ 1 | 2 ]

I have found that I can do:

SELECT (MyFunction())[1], (MyFunction())[2]

But that calls the function twice, therefore doubling the run time (this function is a very time-consuming function). Is the开发者_C百科re a better way to handle this?

Here is an almost perfect replica of what I have:

SELECT table1.a, table1.b, table1.c, (MyFunction(table1.a, table1.b, table1.c))[1],
(MyFunction(table1.a, table1.b, table1.c))[2]
FROM table1
INNER JOIN table2 using(b)
WHERE ... GROUP BY table1.a, table1.b, table1.c;

Again, this produces the two columns from the array, but my function is called twice, which doubles my run time.

---

can you use a subselect?

postgres=# select ar[1], ar[2] from (select string_to_array('a b c', ' ') ar) as sq;
 ar | ar 
----+----
 a  | b
(1 row)

This still requires you explicitly extract each column (like you already do). If there are more elements in the array than extracted, they will be lost, and if there are fewer, then the missing columns will just be NULL.

EDIT: I think I would wrap the whole thing in a subselect; the inner subselect generates the desired rows, with the outer select projecting the inner query into the desired columns:

SELECT subquery1.a, subquery1.b, subquery1.c, 
    myfunction_result[1], myfunction_result[2] 
FROM ( SELECT table1.a, table1.b, table1.c,
              MyFunction(table1.a, table1.b, table1.c) as myfunction_result
       FROM table1 INNER JOIN table2 using(b) 
       WHERE ... GROUP BY table1.a, table1.b, table1.c
) AS subquery1;

The inner and outer selects will properly correlate the table1 references.

---

select data[1] as id, data[2] as value from (SELECT 
string_to_array(rs,':') as data from unnest(string_to_array('1:234,2:400',',')) as rs) as foo

This will result as:

id|Value
--------
1 | 234
2 | 400

---

You can't do that. A single array column could have, for example, one array with three elements and another with five elements. If you tried to expand those arrays into individual columns, you'd end up with two rows in a result set that have different numbers of columns and that is not allowed.

The closest thing available is unnest:

expand an array to a set of rows

but that gives you rows rather the columns you want.

---

If you're try to return a TABLE from a FUNCTION I found that subselecting did not work for me (SingleNegationElimination's answer returns a single column when used with RETURN QUERY inside of a FUNCTION) so I had to use WITH instead like this:

CREATE
OR REPLACE FUNCTION STRING_TO_TABLE(
  IN P_STRING text, 
  IN P_DELIMITER text DEFAULT ' '
) RETURNS TABLE (
    FIRST text, 
    SECOND text
) LANGUAGE plpgsql AS $$

BEGIN

  RETURN QUERY
    WITH SQ AS (
      SELECT STRING_TO_ARRAY(P_STRING, P_DELIMITER) A
    )
    SELECT 
      A[1] AS FIRST,
      A[2] AS SECOND
    FROM SQ;

END $$;

which then allows you to use SELECT...INTO with the function like this:

SELECT
  FIRST,
  SECOND
FROM
  STRING_TO_TABLE('first second')
INTO
  P_FIRST,
  P_SECOND;