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?

score 0 · Answer 1 · answered Dec 17 '19 at 15:38

This is what is called a "Lateral Join" -- basically it is a calculated column that is "cross joined" to your current query.

Use it like this:

SELECT MY_LATERAL,
     -- etc other columns 
FROM YOUR_TABLE 
  LATERAL (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) AS MY_LATERAL

Documentation https://www.postgresql.org/docs/9.4/queries-table-expressions.html section 7.2.1.5

score 0 · Accepted Answer · answered Dec 18 '19 at 09:34

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

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

2 Answers2