I have two tables:
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
parentmatches some predicate, then pick
- If not, then pick
child.periodfrom each child.
I have the following query:
select q.period from parent, lateral ( select parent.period where <some complex predicate> union all select child.period from child where child.parent_identifier = parent.identifier and not(<the same complex predicate as above>) ) 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
<some complex predicate> 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
child row and evaluate the predicate just once.
I tried to introduce a
case when <some complex predicate> 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 <predicate> 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.