Set-returning functions are not allowed in CASE in postgreSQL

Question

I am trying to run this query was able to till some time ago. I don't know what went wrong and I started getting this error now?

Your database returned: ERROR: set-returning functions are not allowed in CASE Hint: You might be able to move the set-returning function into a LATERAL FROM item.

My query:

SELECT distinct
(CASE
WHEN {PERIOD} = 'Previous Quarter' AND pto.pto_start_date < (date_trunc('quarter', now() - INTERVAL '1 month') + INTERVAL '1 month')::date AND pto.pto_end_date >= (date_trunc('quarter', now() - INTERVAL '1 month') + INTERVAL '1 month')::date
THEN generate_series(pto.pto_start_date, pto.pto_end_date, '2 day'::interval)
WHEN {PERIOD} = 'Current Quarter' AND pto.pto_start_date < (date_trunc('quarter', now() - INTERVAL '1 month') + INTERVAL '1 month')::date AND pto.pto_end_date >= (date_trunc('quarter', now() - INTERVAL '1 month') + INTERVAL '1 month')::date
THEN generate_series(pto.pto_start_date, pto.pto_end_date, '1 day'::interval)
ELSE
generate_series(pto.pto_start_date, pto.pto_end_date, '1 day'::interval)
END) AS dt
FROM cust_pto pto

Start dates and end Dates:

What has gone wrong?

"What has gone wrong?" Problem is CASE WHEN .. THEN ...END can return one column value.. `generate_series()` makes more records with one column value that the main problem. — Raymond Nijland, Oct 23 '18 at 15:11
Your question is unclear and incomplete. I advice you to read this "Why should I provide an MCVE for what seems to me to be a very simple SQL query?" https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Raymond Nijland, Oct 23 '18 at 15:12
Your `case` logic is doing nothing. All the start and end dates are the same. — Gordon Linoff, Oct 23 '18 at 15:16
@GordonLinoff, No they are not. I have added an image of the start and end dates — noobeerp, Oct 23 '18 at 15:20
@noobeerp The issue Gordon mentioned is that in all cases, whether `{PERIOD}` is PREVIOUS, CURRENT, or anything else, you always use the exact same values for generate_series. So there's no point in the case statement in the first place. — 404, Oct 23 '18 at 15:56
@noobeerp . . . *Au contraire*. The `then`/`else` clauses in your query are all exactly the same `generate_series()` expression. The `case` is not doing anything. — Gordon Linoff, Oct 23 '18 at 16:16
@GordonLinoff Exactly. All cases return the exact same value so there's no point doing a case statement. I assume the actual code is different and this is just an example. My answer used his code as is, with the assumption that you would of course have a case statement in there which makes sense; hence my db-fiddle link to some code which actually uses the case values. — 404, Oct 23 '18 at 16:18
I just realized that. I made the change on the generate series for both pervious and current values. There is a 2 day interval for previous and 1 day interval for current @eurotrash — noobeerp, Oct 23 '18 at 16:25
@noobeerp OK I've changed the case statement in my answer based on that. You don't need a case for start/end, just for the interval. And you don't need the second `WHEN` because it's the same as `ELSE`. — 404, Oct 23 '18 at 16:28

404 · Accepted Answer · 2018-10-23T16:27:32.827

Why you're getting the error now: you upgraded to postgres 10. Set returning functions are no longer allowed.

What to do: there is more than one way to accomplish what you're trying to do. For the sake of keeping it as close as possible to your original query, all you have to do is put your CASE statement inside generate_series:

SELECT distinct generate_series(
        pto.pto_start_date,
        pto.pto_end_date,
        CASE
        WHEN {PERIOD} = 'Previous Quarter' AND pto.pto_start_date < (date_trunc('quarter', now() - INTERVAL '1 month') + INTERVAL '1 month')::date AND pto.pto_end_date >= (date_trunc('quarter', now() - INTERVAL '1 month') + INTERVAL '1 month')::date THEN
            '2 day'::interval
        ELSE
            '1 day'::interval
        END
) AS dt
FROM cust_pto pto

Set-returning functions are not allowed in CASE in postgreSQL

1 Answers1