
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 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 $function$;