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
CodePudding user response:
You need to remove the strict
attribute.
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)