50 lines
1.2 KiB
PL/PgSQL
50 lines
1.2 KiB
PL/PgSQL
CREATE
|
|
OR REPLACE FUNCTION public.search_owners (search text) RETURNS SETOF owners LANGUAGE plpgsql STABLE AS $function$
|
|
BEGIN
|
|
IF search = ''
|
|
THEN
|
|
RETURN query
|
|
SELECT
|
|
*
|
|
FROM
|
|
owners;
|
|
ELSE
|
|
RETURN query
|
|
SELECT
|
|
*
|
|
FROM
|
|
owners
|
|
WHERE
|
|
(
|
|
ownr_fn || ' ' || ownr_ln
|
|
)
|
|
ILIKE '%' || search || '%'
|
|
OR ownr_ln ILIKE '%' || search || '%'
|
|
OR ownr_fn ILIKE '%' || search || '%'
|
|
OR ownr_fn ILIKE '%' || search || '%'
|
|
OR ownr_co_nm ILIKE '%' || search || '%'
|
|
OR ownr_ph1 ILIKE '%' || search || '%'
|
|
OR ownr_ph2 ILIKE '%' || search || '%'
|
|
OR ownr_addr1 ILIKE '%' || search || '%'
|
|
ORDER BY
|
|
(ownr_fn || ' ' || ownr_ln) ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
ownr_ln ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
ownr_fn ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
ownr_co_nm ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
ownr_fn ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
ownr_ph1 ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
ownr_ph2 ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
ownr_addr1 ILIKE '%' || search || '%'
|
|
OR NULL;
|
|
END
|
|
IF;
|
|
END
|
|
$function$;
|