Handling multiple return values in subquery

Question

I have the following data:

cte
=================
gp_id | m_ids
------|----------
1     | {123}
2     | {432,222}
3     | {123,222}

And a function with a signature like this (which in fact returns not a table but a couple of ids):

FUNCTION foo(m_ids integer[])
RETURNS TABLE (
    first_id integer,
    second_id integer
)

Now, I've got to iterate over each row and perform some calculations with that function, so I would get something like this:

gp_id | first_id | second_id 
------|----------|-----------
1     | 25       | 25        
2     | 13       | 24        
3     | 25       | 11

To achieve that I tried the following code:

SELECT gp_id, 
       ( 
              SELECT * 
              FROM   foo( 
                     ( 
                            SELECT m_ids 
                            FROM   cte c2 
                            WHERE  c2.gp_id = c1.gp_id)) limit 1) 
FROM   cte c1

The problem is in the SELECT * statement. If I use SELECT first_id, everything works well (except for that I have to run two consecutive queries, which I'd like to avoid, obviously), but in the former case I'm getting the error

subquery must return only one column

which is somewhat expected.
So how can I correctly iterate over the table in one single query?

klin · Accepted Answer · 2017-06-22 11:46:23Z

1

Use the function in a lateral join:

select gp_id, first_id, second_id
from cte, 
lateral foo(m_ids);

answered Jun 22, 2017 at 11:46

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Handling multiple return values in subquery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related