PostgreSQL: Function with multiple date parameter

Question

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.

Mureinik · Accepted Answer · 2016-06-27 07:31:57Z

3

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))

answered Jun 27, 2016 at 7:31

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

3 Comments

Abelisto Over a year ago

@fLen Much simpler: a.date_applied <@ daterange(start_date, end_date, '[]')

fLen Over a year ago

I dont have a function for daterange @Abelisto.. :(

Abelisto Over a year ago

@fLen It was introduced in the 9.2 PostgreSQL version.

Collectives™ on Stack Overflow

PostgreSQL: Function with multiple date parameter

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related