20 lines
1.3 KiB
YAML
20 lines
1.3 KiB
YAML
- args:
|
|
cascade: true
|
|
read_only: false
|
|
sql: "CREATE OR REPLACE FUNCTION public.search_jobs(search text, startdate date,
|
|
enddate date)\n RETURNS SETOF jobs\n LANGUAGE plpgsql\n STABLE\nAS $function$\nBEGIN
|
|
if search = '' and ((startDate is null) or (endDate is null)) then return query\nselect
|
|
*\nfrom jobs j;\nelse\n\nif (startDate is null) or (endDate is null) then \nreturn
|
|
query\nSELECT *\nFROM jobs j2\nWHERE \n\nownr_fn ILIKE '%' || search || '%'\n
|
|
\ or ownr_ln ILIKE '%' || search || '%'\n \n or clm_no ILIKE '%' || search
|
|
|| '%'\nORDER BY \n clm_no ILIKE '%' || search || '%'\n OR null,\n ownr_fn
|
|
ILIKE '%' || search || '%'\n OR NULL,\n ownr_ln ILIKE '%' || search ||
|
|
'%'\n OR NULL;\nelse \nreturn query\nSELECT *\nFROM jobs j2\nWHERE \nclose_date
|
|
between startDate and endDate and close_date is not null and\n(\nownr_fn ILIKE
|
|
'%' || search || '%'\n or ownr_ln ILIKE '%' || search || '%'\n \n or
|
|
clm_no ILIKE '%' || search || '%')\n\nORDER BY \n clm_no ILIKE '%' || search
|
|
|| '%'\n OR null,\n ownr_fn ILIKE '%' || search || '%'\n OR NULL,\n
|
|
\ ownr_ln ILIKE '%' || search || '%'\n OR NULL;\n\nend if;\n\n\nend if;\nEND
|
|
$function$;"
|
|
type: run_sql
|