19 lines
1.2 KiB
YAML
19 lines
1.2 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$
|
|
BEGIN if search = '' then return query\nselect *\nfrom jobs j;\nelse\n\nif (startDate
|
|
is null) || (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\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
|