How do I fix a Postgres 12 Error: set-returning functions are not allowed in CASE

Question

I just updated my Postres from 9.1 to 12 and I'm now getting this error message from a specific query:

SQLSTATE[0A000]: Feature not supported: 7 ERROR: set-returning functions are not allowed in CASE LINE 17: generate_series(start_date, ^ HINT: You might be able to move the set-returning function into a LATERAL FROM item.

The actual query generating that error is is part of a much larger on but the actual lines are:

CASE
    WHEN num_payments > 1 THEN
        generate_series(start_date, start_date + ((payment_interval*(num_payments-1)) || payment_interval2)::interval, (payment_interval::text || payment_interval2)::interval)::date
    ELSE
        start_date
END

Being the first time seeing this message, I have no idea what this entails. How do I move the set-returning function into a LATERAL FROM item as the error message hints?

Answer 1

For some reason I wasn't able to use the LATERAL since it just generated other syntactic error messages (I'll have to work on that on the long run). So I was able to solve my issue by simply just selecting all values then moving the CASE higher up in the query so that the generate_series() is not inside a case statement:

SELECT other_columns, 
       CASE
           WHEN num_payments > 1 THEN date_2 ELSE date_1
       END AS start_date
FROM(
       SELECT other_columns, 
              start_date AS date_1,
              generate_series(start_date, start_date + ((payment_interval*(num_payments-1)) || payment_interval2)::interval, (payment_interval::text || payment_interval2)::interval)::date AS date_2
       FROM(
           -- INNER QUERY
       )a
)b