52 lines
1016 B
PL/PgSQL
Executable File
52 lines
1016 B
PL/PgSQL
Executable File
|
|
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$; |