lateral function call on true

lateral.sql

/*
https://stackoverflow.com/questions/38297935/split-function-returned-record-into-multiple-columns
*/

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;

select * from hi_lo(2, 3, 4);

begin;
create temp table actor(actor_id bigint, name text);
create temp table movies_actors(movie_id bigint);
insert into actor values(1,'test');
insert into actor values(1,'Hello_1');
insert into actor values(1,'你好');
insert into actor values(3,'Hello_test');
insert into movies_actors values(1);
commit;

--using lateral. 
select * from actor 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;

--Don't do the following way.
explain(costs off)
select (hi_lo(a.actor_id,length(a.name),ma.movie_id)).*
from actor a
join movies_actors ma on a.actor_id = ma.movie_id;
 
--using subquery
select *, (x).* from (
    select 
        *
        ,hi_lo(a.actor_id,length(a.name), ma.movie_id) as x
    from actor a 
    join movies_actors ma 
    on a.actor_id = ma.movie_id
) sub;

--using CTE
with cte as(
    select *
        ,hi_lo(a.actor_id, length(a.name), ma.movie_id) as x
    from actor a join movies_actors ma
    on a.actor_id = ma.movie_id
)
select *, (x).* from cte;