CODEWITHSUNDEEP
sqlpostgresqldatenull
fLen
fLen

Reputation: 598

PostgreSQL: Function with multiple date parameter

I'm trying to create a function with multiple parameter as below:

CREATE OR REPLACE FUNCTION select_name_and_date (
    IN f_name character,
    IN m_name character,
    IN l_name character,
    IN start_date  date,
    IN end_date  date )  
RETURNS TABLE (
start_date  date ,first_name character, middle_name character,last_name character ) AS $BODY$
BEGIN RETURN QUERY
select a.start_date, a.first_name, a.middle_name, a.last_name
FROM table1 a
where code in ('NEW', 'OLD')
and ( (a.first_name like '%' || f_name || '%' and a.middle_name like '%' || m_name || '%' and a.last_name like '%' || l_name || '%')) 
or ((a.date_applied) between start_date  and end_date  );
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

When I tried to execute with date, it shows correct result.

select * from select_name_and_date ('Firstname','','','2016-06-27','2016-06-28');

When i tried to remove the value of date, it shows: ERROR: invalid input syntax for type date: ""

select * from select_name_and_date ('Firstname','','','','');

When I tried to replace with NULL value of the date, it shows: 0 rows retrieved. (when it should have)

select * from select_name_and_date ('Firstname','','',NULL,NULL);

I want to have parameter that not depending on each parameter.

Upvotes: 2

Views: 13091

Answers (1)

Mureinik
Mureinik

Reputation: 312289

The between operator does not handle nulls. If you want to allow them, you'll to treat them explicitly. E.g., you could rewrite the part of the condition that applies to a.date_applied as follows:

((a.date_applied BETWEEN start_date AND end_date) OR
 (start_date IS NULL AND a.date_applied < end_date) OR
 (end_date IS NULL AND a.date_applied >= end_date) OR
 (start_date IS NULL AND end_date IS NULL))

Upvotes: 3

Related Questions