postgreSQL自定义函数没有根据我的条件返回预期的数据

Question

我正在尝试创建此函数，但空值未通过

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;

但有些条件不起作用，尤其是当我通过null时没有给出预期结果的调用是：

SELECT * FROM func('2022-03-22', '2022-01-22',
'strike in six months before inspection + last_known_status = null',
null, 20)

应返回以上内容('REVIEWED'，-2(而我什么也得不到

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)

期望上面返回(，-2(而我什么也得不到

SELECT * FROM func('2022-03-22', '2021-01-22',
'strike in six months before inspection + last_known_status = IN_PROGRESS',
'IN_PROGRESS', 20)

期望上面返回('IN_PROGRESS'，11(而我得到("不匹配"，-4(

简单地说，以下是我正在努力实现的目标接收的SQL函数

func(inspection date, strike date, action varchar, status varchar, severity int)

并根据条件返回以下

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)

Answer 1

我只解释null的情况。您的代码按预期工作。如果你不确定。只需复制粘贴sqlfiddle中的所有工作
演示

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)

将返回

last_known_task_status                                  | taskaction
------------------------------------------------------------------------------------------+------------
strike time older than inspection date by more than 6 months but inspection_date is null |         11

原因：此执行满足谓词：

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);

将获得

last_known_task_status | taskaction
------------------------+------------
REVIEWED               |         -2

因为它符合谓词

WHEN ((strike_time BETWEEN inspection_date::date - INTERVAL '6' MONTH AND inspection_date)
AND last_known_status IS null) THEN ('REVIEWED'::text, -2)