sql - Split function-returned record into multiple columns

Question

In a basic Postgres function tutorial there is an example with OUT parameters like so:

create or replace function hi_lo(a numeric, 
                                 b numeric, 
                                 c numeric, 
                                 OUT hi numeric, 
                                 OUT lo numeric)
as $$
begin
hi := greatest(a, b, c);
lo := least(a, b, c);
end; $$
language plpgsql;

Then results look like

select hi_lo(2, 3, 4);
-- returns one column, "hi_lo" with value "(4, 2)".

select * from hi_lo(2, 3, 4);
-- returns two columns, "hi" / 4 and "lo" / 2.

But suppose you want to execute the function on columns that come from performing a join, and that you do not have access to modify the function or use an alternative function? For example, using some toy data:

select hi_lo(a.actor_id, length(a.name), ma.movie_id) 
from 
    actors a 
join 
    movies_actors ma 
on 
    a.actor_id = ma.movie_id 
limit 10;

returns results in a single column "hi_lo" have 2-tuple values.

Wrapping the query in parentheses and trying to a select * from it does not change the format of the output. So

select *
from (
    select hi_lo(a.actor_id, length(a.name), ma.movie_id) 
    from 
        actors a 
    join 
        movies_actors ma 
    on 
        a.actor_id = ma.movie_id 
    limit 10;
) rr

does not impact the result shape.

The following try results in the error "subquery must return only one column"

select (
    select * from hi_lo(a.actor_id, length(a.name), ma.movie_id)
) 
from 
    actors a 
join 
    movies_actors ma 
on
    a.actor_id = ma.movie_id 
limit 10;

Finally, I also tried unnest but it gives an argument type error as the tuple values are not treated as arrays.

How can you achieve multiple columns in the output when you cannot move the function evaluation into the from section?

Answer 1

In Postgres 9.3 or later this is best solved with a LATERAL join:

SELECT *
FROM   actors a 
JOIN   movies_actors ma on a.actor_id = ma.movie_id 
LEFT   JOIN LATERAL hi_lo(a.actor_id, length(a.name), ma.movie_id) x ON true
LIMIT  10;

Avoids repeated evaluation of the function (for each column in the output - the function does have to be called for each input row either way).
LEFT JOIN LATERAL ... ON true to avoid dropping rows from the left side if the function returns no row:

• What is the difference between LATERAL and a subquery in PostgreSQL?

Follow-up in your comment:

only the expanded columns produced by the function call

SELECT x.*  -- that's all!
FROM   actors a 
JOIN   movies_actors ma on a.actor_id = ma.movie_id 
LEFT   JOIN LATERAL hi_lo(a.actor_id, length(a.name), ma.movie_id) x ON true
LIMIT  10;

But since you don't care about other columns, you can simplify to:

SELECT x.*
FROM   actors a 
JOIN   movies_actors ma on a.actor_id = ma.movie_id 
     , hi_lo(a.actor_id, length(a.name), ma.movie_id) x
LIMIT  10;

Which is an implicit CROSS JOIN LATERAL. If the function can actually return "no row" occasionally, the result can be different: we don't get NULL values for the rows, those rows are just eliminated - and LIMIT does not count them any more.

---

In older versions (or generally) you can also just decompose the composite type with the right syntax:

SELECT *, (hi_lo(a.actor_id, length(a.name), ma.movie_id)).*  -- note extra parentheses!
FROM   actors a 
JOIN   movies_actors ma on a.actor_id = ma.movie_id 
LIMIT  10;

The drawback is that the function is evaluated once for each column in the function output due to a weakness in the Postgres query planner. It's better to move the call into a subquery or CTE and decompose the row type in the outer SELECT. Like:

SELECT actor_id, movie_id, (x).*  -- explicit column names for the rest
FROM  (
   SELECT *, hi_lo(a.actor_id, length(a.name), ma.movie_id) AS x
   FROM   actors a 
   JOIN   movies_actors ma on a.actor_id = ma.movie_id 
   LIMIT  10
   ) sub;

But you have to name individual columns and can't get away with SELECT * unless you are ok with the row type in the result redundantly. Related:

• Avoid multiple calls on same function when expanding composite result
• How to avoid multiple function evals with the (func()).* syntax in an SQL query?