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

Question

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

user330315 · Accepted Answer · 2022-03-25 07:24:59Z

2

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 Mar 25, 2022 at 7:24

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

jian Over a year ago

Thanks. I thought of type issue. now I know what strict mean...

user330315 Over a year ago

@VinitKhandelwal: works for me: dbfiddle.uk/…

Vinit Khandelwal Over a year ago

@a_horse_with_no_name here in this function I am using this but not working stackoverflow.com/questions/71613680/…

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related