33 lines
647 B
PL/PgSQL
33 lines
647 B
PL/PgSQL
CREATE OR REPLACE FUNCTION public.search_exportlog(search text)
|
|
RETURNS SETOF exportlog
|
|
LANGUAGE plpgsql
|
|
STABLE
|
|
AS $function$ BEGIN IF search = '' THEN RETURN query
|
|
SELECT
|
|
*
|
|
FROM
|
|
exportlog e;
|
|
ELSE RETURN query
|
|
SELECT
|
|
e.*
|
|
FROM
|
|
exportlog e
|
|
LEFT JOIN jobs j on j.id = e.jobid
|
|
LEFT JOIN payments p
|
|
ON p.id = e.paymentid
|
|
LEFT JOIN bills b
|
|
ON e.billid = b.id
|
|
WHERE
|
|
(
|
|
j.ro_number ILIKE '%' || search
|
|
OR b.invoice_number ILIKE '%' || search
|
|
OR p.paymentnum ILIKE '%' || search
|
|
OR e.useremail ILIKE '%' || search
|
|
)
|
|
AND (e.jobid = j.id
|
|
or e.paymentid = p.id
|
|
or e.billid = b.id)
|
|
;
|
|
END IF;
|
|
END $function$;
|