PostgreSQL conversion array returned from function to columns

Question

I have a function in PostgreSQL (PLPGSQL) that returns an array containing two elements. When I run the 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 into my own columns:

 [ 1 | 2 ] 

I found that I can do:

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

But this calls the function twice, therefore doubles the execution time (this function is very time-consuming). Is there a better way to handle this?

---

UPDATE

Here is an almost perfect copy 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 creates two columns from the array, but my function is called twice, which doubles my runtime.

Answer 1

Can I use a subquery?

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

This still requires you to explicitly retrieve each column (as you already did). If there are more elements in the array than are extracted, they will be lost, and if there are fewer, then the missing columns will be only NULL .

EDIT: I think I would wrap it all in a subquery; the internal subquery generates the necessary rows, and the external selection issues an internal query in the necessary 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; 

Internal and external selection will correctly correlate table1 links.

Answer 2

You cannot do this. One column of an array can have, for example, one array with three elements and another with five elements. If you tried to expand these arrays into separate columns, you would end up with two rows in the result set that have different numbers of columns, and this is not allowed.

The nearest unnest thing unnest :

expand array to rowset

but it gives you the rows, not the columns you want.

Answer 3

 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 

It will look like:

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