PostgreSQL convert array returned from function to columns

user_757376 0
Viewed 35898

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 there a better way to handle this?

UPDATE

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.

postgresql function arrays
3 Answers
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
user_29673320
Related