Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@cpursley
Last active February 21, 2024 14:39
Show Gist options
  • Star 18 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save cpursley/e3586382c3a42c54ca7f5fef1665be7b to your computer and use it in GitHub Desktop.
Save cpursley/e3586382c3a42c54ca7f5fef1665be7b to your computer and use it in GitHub Desktop.
Postgres Full Text Search
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment