[SOLVED] How to create SQL Function such that it accepts date or null value

Issue

How to create SQL Function such that it accepts date or null value

CREATE OR REPLACE function func(dob timestamp)
RETURNS TABLE(greet varchar, age int) AS $$
    SELECT CASE
        WHEN dob IS NULL
            THEN ('no birth'::text, 0)
        WHEN dob >= '2000-01-01'
            THEN ('post millennium', 21)
        ELSE ('does not match', -1)
    END
$$
language sql stable strict;

Here I want to accept date value or null. It is not working when I pass null. I am getting empty record

Solution

You need to remove the strict attribute.

Quote from the manual

RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.

^{(emphasis mine)}

Answered By – a_horse_with_no_name

Answer Checked By – Clifford M. (BugsFixing Volunteer)