I am trying to create this function but null values fail
CREATE OR REPLACE function func(inspection_date date, strike_time date, taskaction_display varchar, last_known_status varchar, severity int) RETURNS TABLE(last_known_task_status varchar, taskaction int) AS $$ SELECT CASE WHEN inspection_date IS null THEN (taskaction_display, severity) WHEN strike_time >= inspection_date THEN (taskaction_display, severity) WHEN ((strike_time BETWEEN inspection_date - INTERVAL '6' MONTH AND inspection_date) AND last_known_status IS NOT null) THEN (last_known_status, CASE WHEN last_known_status = 'IN_PROGRESS' THEN 11 WHEN last_known_status = 'PENDING' THEN 12 WHEN last_known_status = 'COMPLETE' THEN 13 WHEN last_known_status = '' THEN -1 ELSE -2 END) WHEN ((strike_time BETWEEN inspection_date::date - INTERVAL '6' MONTH AND inspection_date) AND last_known_status IS null) THEN ('REVIEWED'::text, -3) WHEN (strike_time < inspection_date - INTERVAL '6' MONTH) THEN ('REVIEWED'::text, -2) ELSE ('NO MATCH'::text, -4) END $$ language sql stable;
But some of the conditions are not working, specially when I am passing null calls that are not giving expected results are:
SELECT * FROM func('2022-03-22', '2022-01-22', 'strike in six months before inspection + last_known_status = null', null, 20)
Expecting above to return (‘REVIEWED’, -2) while I am getting nothing
SELECT * FROM func(null, '2021-01-22', 'strike time older than inspection date by more than 6 months but inspection_date is null', 'IS_NULL', 11)
Expecting above to return ( , -2) while I am getting nothing
SELECT * FROM func('2022-03-22', '2021-01-22', 'strike in six months before inspection + last_known_status = IN_PROGRESS', 'IN_PROGRESS', 20)
Expecting above to return (‘IN_PROGRESS’, 11) while I am getting (“NO MATCH”, -4)
In simple words the following is what I am trying to achieve A SQL function that takes in
func(inspection date, strike date, action varchar, status varchar, severity int)
And returns the following based on conditions
if(inspection == null) return (action, severity) else if(strike >= inspection) return (action, severity) else if(strike >= inspection-6 months) { if(status == null) return ('REVIEWED', -1) else if(status == 'IN_PROGRESS') return (status, 11) else if(status == 'PENDING') return (status, 12) else if(status == 'COMPLETE') return (status, 13) else ('NO STATUS MATCH', -2) } else if(strike < inspection-6 months) return ('REVIEWED', -3) else return ('NO MATCH', -4)
Advertisement
Answer
I explain null case only. Your code working as intended.
If you not sure. just copy paste all the work in sql fiddle.
demo
SELECT * FROM func(null, '2021-01-22', 'strike time older than inspection date by more than 6 months but inspection_date is null', 'IS_NULL', 11)
will return
last_known_task_status | taskaction ------------------------------------------------------------------------------------------+------------ strike time older than inspection date by more than 6 months but inspection_date is null | 11
BECAUSE: This execution meet predicate:
WHEN inspection_date IS null THEN (taskaction_display, severity)
SELECT * FROM func('2022-03-22', '2022-01-22', 'strike in six months before inspection + last_known_status = null', null, 20);
will get
last_known_task_status | taskaction ------------------------+------------ REVIEWED | -2
BECAUSE It meet predicate
WHEN ((strike_time BETWEEN inspection_date::date - INTERVAL '6' MONTH AND inspection_date) AND last_known_status IS null) THEN ('REVIEWED'::text, -2)