通過匹配我的條件，SQL自定義函式未按預期回傳資料

我正在嘗試創建此函式，但空值失敗

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;

但是有些條件不起作用，特別是當我傳遞沒有給出預期結果的空呼叫時：

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)

期望在我得到 ("NO MATCH", -4) 時回傳 ('IN_PROGRESS', 11)

簡而言之，以下是我想要實作的一個 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)

uj5u.com熱心網友回復：

我只解釋空情況。您的代碼按預期作業。如果你不確定。只需復制粘貼 sql fiddle 中的所有作業即可。
演示

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)

標籤：sql PostgreSQL 功能 日期

上一篇：SQL獲取一列的值，使得另一列中的某個值不存在

下一篇：更改表時觸發創建，但將發生更新或洗掉的e_id相同