Function with dates as parameters, which can be NULL
I am trying to implement a function with dates as parameters in PostgreSQL 9.3.5.
It should cover all of the conditions below and return 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
But my above SQL Fiddle doesn't work as expected.
I want to have a common implementation, so if tomorrow I still have new Date Fields
criteria to look for, then I can just add them to where condition
, but not to every line of the IF
Statement.
source to share
It could be much easier :
You can simplify the expression from @Clodoaldo's currently accepted answer:
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 further_sd is not null
.
The expression can only evaluateTRUE
with non-zero_sd
.
But none of this is required:
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 include the language name. This is an identifier.
- Postgres provides special values
-infinity
andinfinity
for the typestimestamp
anddate
. All you need is the default cCOALESCE
.
SQL Fiddle (for item 9.3).
Also: I would suggest using standard ISO dates instead , which is potentially ambiguous and breaks with a different language setting. The manual advises the same amount. '2014-11-10'
'11-10-2014'
source to share
You must define a function to return a set of
sampletest, and then return different requests based on the arguments passed.
CREATE FUNCTION
test_dob_dates(_sd date, _ed date)
RETURNS
SETOF sampletest
AS
$BODY$
BEGIN
IF _sd IS NOT NULL AND _ed IS NOT NULL THEN
RETURN QUERY SELECT * FROM sampletest WHERE dob BETWEEN _sd AND _ed;
ELSIF _sd IS NULL AND _ed IS NULL THEN
RETURN QUERY SELECT * FROM sampletest WHERE dob IS NULL;
ELSEIF _ed IS NOT NULL THEN
RETURN QUERY SELECT * FROM sampletest WHERE dob <= _ed;
ELSE
RETURN QUERY SELECT * FROM sampletest WHERE dob >= _sd;
END IF;
END;
$BODY$
LANGUAGE
'plpgsql' VOLATILE;
The suggestion eid IS NOT NULL
seems like overkill since you have already defined eid
as a primary key, which means it can never be NULL.
source to share
create function test_dob_dates(_sd date, _ed date)
returns setof sampletest as $body$
select *
from sampletest
where
eid is not null
and (
(_sd is null and _ed is null)
or (_sd is not null and _ed is null and dob >= _sd)
or (_sd is null and _ed is not null and dob <= _ed)
or dob between _sd and _ed
);
$body$ language sql stable;
source to share