How to combine a CASE statement with a LATERAL JOIN in PostgreSQL?

Question

I need to call a function several times for every select row, because function have several OUT parameters, all of which I need

E.g.

SELECT
   a, b,
   (SELECT out1 from func(a)),
   (SELECT out2 from func(a))
FROM 
   table1

To call this function only once I use lateral join

SELECT 
   a, b,
   lat.out1,
   lat.out2
LEFT OUTER JOIN LATERAL (
   SELECT out1, out2 FROM func(a)
) lat ON (TRUE)

The problem is in case a is nullable. func throws exception being called with null. Then without joins I would do it like this

SELECT
   a, b,
   CASE WHEN a IS NOT NULL 
       THEN out1 from func(a)
   END,
   CASE WHEN a IS NOT NULL 
       THEN out2 from func(a)
   END
FROM 
   table1

But how could I implement it using lateral joins? Is there a way to use CASE inside lateral join? Or is there any other way to call the procedure only once?

Answer 1

Good question. You could create a wrapper function that returns an empty rowset when the parameter is null:

create or replace function wrap_func(a int)
returns table(out1 int, out2 int)
as $$
begin
    if a is null then
        return;
    end if;
    return query (
        select  out1, out2
        from    func(a)
    );
end;
$$ language plpgsql;

If you call the wrapper in your lateral join, it will behave as you expect.