我正在尝试创建具有多个参数的函数,如下所示:
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;
当我尝试执行日期时,它显示正确的结果。
select * from select_name_and_date ('Firstname','','','2016-06-27','2016-06-28');
当我尝试删除date的值时,它显示:错误:类型为date的输入语法无效:“”
select * from select_name_and_date ('Firstname','','','','');
当我尝试用日期的NULL值替换时,它显示:检索到0行。(应该有的时候)
select * from select_name_and_date ('Firstname','','',NULL,NULL);
我想拥有不依赖于每个参数的参数。
该between
运营商不处理null
秒。如果要允许它们,则将对其进行显式处理。例如,您可以重写适用于a.date_applied
以下条件的部分:
((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))
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句