Mr. F Mr. F - 4 months ago 38
SQL Question

Split function-returned record into multiple columns

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

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.
LEFT JOIN LATERAL ... ON true to avoid dropping rows from the left side if the function returns no row:

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 result row 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:

Comments