current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question
    
I will note that the number of elements in the returning array will ALWAYS be 2... –  lightningmanic Aug 5 '11 at 18:24
    
If the shape of the returned result is known, should you be returning an array at all? that should probably be a row. Do you have the ability to refactor that? –  IfLoop Aug 5 '11 at 19:01
add comment

3 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
That's one of the usual "local variable" tricks and it should work as long as the number of elements in the array is fixed. –  mu is too short Aug 5 '11 at 18:19
    
The only problem (and perhaps I should have mentioned this earlier) is that the function depends on other relations of the same query level. So I'm selecting "table_id", and passing that same id into the function. –  lightningmanic Aug 5 '11 at 18:23
    
@mu is too short: not true at all. See edit. –  IfLoop Aug 5 '11 at 18:25
    
This produces: ERROR: missing FROM-clause entry for table "table1" in the first line: SELECT table1.a ... –  lightningmanic Aug 5 '11 at 18:50
1  
@mu is too short: If the returned array is variadic, or just generally long, then it shouldn't be transposed into columns in the first place. variadic collections should stay as arrays. –  IfLoop Aug 5 '11 at 19:00
show 3 more comments
up vote 1 down vote

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.

share|improve this answer
add comment
up vote 0 down vote

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.