Postgres Full Text Search

full_text_search.sql

CREATE EXTENSION unaccent;

ALTER TABLE client
ADD COLUMN full_text_search VARCHAR;

-- Do an initial update and apply the UNACCENT function searchable content
UPDATE client
  SET full_text_search = tsvector_to_array(to_tsvector('english', UNACCENT(name || ' ' || COALESCE(notes, ' ') || ' ' || COALESCE(location_address, ' '))));

-- Create an AFTER INSERT OR UPDATE trigger (to maintain the column data for inserted/updated rows)
CREATE OR REPLACE FUNCTION client_full_text_search_refresh()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
    	NEW.full_text_search := (tsvector_to_array(to_tsvector('english', UNACCENT(NEW.name || ' ' || COALESCE(NEW.notes, ' ') || ' ' || COALESCE(NEW.location_address, ' ')))));
    END IF;
RETURN NEW;
END;
$function$;

CREATE TRIGGER client_full_text_search_refresh
BEFORE INSERT OR UPDATE OF name, notes, location_address
ON public.client
FOR EACH ROW
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE client_full_text_search_refresh();

-- Add GIN index
CREATE INDEX client_search_gin_idx ON client USING GIN (full_text_search gin_trgm_ops);

-- Function (required for Hasura: https://hasura.io/docs/latest/graphql/core/databases/postgres/schema/custom-functions.html)
CREATE FUNCTION search_client(search text)
    RETURNS SETOF client
    AS $$
    SELECT
        *
    FROM
        client
    WHERE
        -- full_text_search column already has UNACCENT applied to it, so we don't have to re-apply
        UNACCENT(search) <% full_text_search
    ORDER BY
        SIMILARITY (search, (full_text_search)) DESC;
$$
LANGUAGE sql
STABLE;