Function with dates as params that can be NULL

Question

I am trying to implement a function with dates as parameters in PostgreSQL 9.3.5.
It should cover all the below conditions and return the results accordingly.

select * from tablename
where sid is not null and dob >= '11-14-2014' order by dob;
-- Only Start Date

select * from tablename
where sid is not null and dob <= '11-21-2014' order by dob;
-- Only End Date

select * from tablename
where sid is not null and dob between '11-10-2014' and '11-21-2014' order by dob; 
-- Both Start and End Dates are Given

select * from tablename
where sid is not null and dob is null; 
-- Both Start and End Dates are Null

SQL Fiddle

But my above SQL Fiddle is not working as expected.

I want to have a generic implementation so that if tomorrow I have some more new Date Fields into the criteria search, then I just can add them in a where condition, but not in every line of IF Statement.

Updated SQL Fiddle

Answer 1

This can be much simpler still:

You can simplify the expression from @Clodoaldo's currently accepted answer to:

SELECT *
FROM   sampletest
WHERE  eid IS NOT NULL
AND   (_sd IS NULL AND _ed IS NULL OR
       _sd IS NULL AND dob <= _ed  OR
       _ed IS NULL AND dob >= _sd  OR
       dob BETWEEN _sd AND _ed);

• Once you compare dob >= _sd, there is no need to check _sd is not null additionally.
  The expression can only evaluate to TRUE with non-null _sd anyway.

But none of this is necessary:

CREATE OR REPLACE FUNCTION test_dob_dates(_sd date, _ed date)
  RETURNS SETOF sampletest AS
$func$
SELECT *
FROM   sampletest
WHERE  eid IS NOT NULL
AND    dob BETWEEN COALESCE(_sd, -infinity) AND COALESCE(_ed, infinity)
$func$ LANGUAGE sql STABLE;

• Do not quote the language name. It's an identifier.
• Postgres provides the special values -infinity and infinity for timestamp and date types. All you need is to default to those values with COALESCE.

SQL Fiddle (for pg 9.3).

Aside: I would advise to use standard ISO dates '2014-11-10' instead of '11-10-2014', which is potentially ambiguous and breaks with a different locale setting. The manual advises as much.