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
– Raymond Nijland, Commented Oct 23, 2018 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?" meta.stackoverflow.com/questions/333952/… — Raymond Nijland
– Raymond Nijland, Commented Oct 23, 2018 at 15:12
Your case logic is doing nothing. All the start and end dates are the same. — Gordon Linoff
– Gordon Linoff, Commented Oct 23, 2018 at 15:16
@GordonLinoff, No they are not. I have added an image of the start and end dates — noobeerp
– noobeerp, Commented Oct 23, 2018 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
– 404, Commented Oct 23, 2018 at 15:56

404 · Accepted Answer · 2018-10-23 16:27:32Z

5

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

edited Oct 23, 2018 at 16:27

answered Oct 23, 2018 at 15:53

404

8,7622 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Set-returning functions are not allowed in CASE in postgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related