Conditional selection in FROM clause I have two tables:


parent with columns identifier (pkey) and period;
child with columns identifier (pkey), parent_identifier and period.


There are additional columns, but I did not list them here as they are not really relevant.

I want to select the periods as following:


If the parent matches some predicate, then pick parent.period.
If not, then pick child.period from each child.


I have the following query:

select   q.period
from     parent,
lateral  (
           select  parent.period
           where   &lt;some complex predicate&gt;
           union all
           select  child.period
           from    child
           where   child.parent_identifier = parent.identifier and
                   not(&lt;the same complex predicate as above&gt;)
         ) as q(period);


While this query works and returns the expected periods, I am wondering if this query could be rewritten in such way that &lt;some complex predicate&gt; does not have to be evaluated twice, to improve performance (albeit by a small factor) and general query structure to represent the if ... else ... from above. Ideally, I want to loop once through each parent/child row and evaluate the predicate just once.

I tried to introduce a case when &lt;some complex predicate&gt; then ... else ... end clause, but I fail to get it right. I am not sure if that is even possible in the FROM clause. Is this possible?

I also had select case when &lt;predicate&gt; then parent.period else unnest(array(select child.period from child where ...)) from parent in mind (though I did not try it, so it might be disallowed in a case when ... as well), but I am not sure if first creating and then unnesting an array is really optimal. Also, I am not really fond of set-returning functions, such as unnest, in the selection.
The following queries evaluate the expensive predicate just once for each parent row. To achieve this, the predicate is evaluated in a separate subquery. The first version uses a lateral join, the second an inner join. This meets the requirements from the question.

select   q2.period
from     parent,
lateral  (
           select &lt;some complex predicate&gt;
         ) as q1(predicate),
lateral  (
           select  parent.period
           where   q1.predicate
           union all
           select  child.period
           from    child
           where   child.parent_identifier = parent.identifier and
                   not(q1.predicate)
         ) as q2(period);


select     q2.period
from       parent,
inner join (
             select  parent.identifier, &lt;some complex predicate&gt;
             from    parent
           ) as q1(identifier, predicate)
on         parent.identifier = q1.identifier,
lateral    (
             select  parent.period
             where   q1.predicate
             union all
             select  child.period
             from    child
             where   child.parent_identifier = parent.identifier and
                     not(q1.predicate)
           ) as q2(period);