Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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 -2
                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, -2)
        WHEN (strike_time < inspection_date - INTERVAL '6' MONTH) THEN ('REVIEWED'::text, -2)
        ELSE ('NO MATCH'::text, -3)
    END
$$
language sql ;
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


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