CREATE OR REPLACE FUNCTION public.search_jobs(search text, startdate date, enddate date)
 RETURNS SETOF jobs
 LANGUAGE plpgsql
 STABLE
AS $function$ BEGIN if search = '' then return query
select *
from jobs j;
else

if (startDate is null) || (endDate is null) then 
return query
SELECT *
FROM jobs j2
WHERE 

ownr_fn ILIKE '%' || search || '%'
    or ownr_ln ILIKE '%' || search || '%'
   
    or clm_no ILIKE '%' || search || '%'
ORDER BY 
     clm_no ILIKE '%' || search || '%'
    OR null,
    ownr_fn ILIKE '%' || search || '%'
    OR NULL,
   ownr_ln ILIKE '%' || search || '%'
    OR NULL;
else 
return query
SELECT *
FROM jobs j2
WHERE 
close_date between startDate and endDate  and
(
ownr_fn ILIKE '%' || search || '%'
    or ownr_ln ILIKE '%' || search || '%'
   
    or clm_no ILIKE '%' || search || '%')

ORDER BY 
     clm_no ILIKE '%' || search || '%'
    OR null,
    ownr_fn ILIKE '%' || search || '%'
    OR NULL,
   ownr_ln ILIKE '%' || search || '%'
    OR NULL;

end if;


end if;
END $function$;