37 lines
1.1 KiB
PL/PgSQL
37 lines
1.1 KiB
PL/PgSQL
CREATE EXTENSION pg_trgm;
|
|
|
|
ALTER TABLE entries
|
|
ADD COLUMN tsvec tsvector;
|
|
|
|
CREATE FUNCTION public.update_entry_tsvec () RETURNS TRIGGER LANGUAGE plpgsql AS $function$
|
|
declare
|
|
v_text text := '';
|
|
x_text text := '';
|
|
begin
|
|
select text into v_text from entry_versions ev where ev.entry_id = new.entry_id order by ev.created_at desc limit 1;
|
|
select text into x_text from entry_executions ex where ex.entry_id = new.entry_id order by ex.created_at desc limit 1;
|
|
update entries set tsvec =
|
|
to_tsvector('german', coalesce(v_text, '')) ||
|
|
to_tsvector('german', coalesce(x_text, ''))
|
|
where id = new.entry_id;
|
|
RETURN NEW;
|
|
END
|
|
$function$;
|
|
|
|
CREATE TRIGGER entry_versions_update_tsvec
|
|
AFTER INSERT
|
|
OR
|
|
UPDATE ON entry_versions FOR EACH ROW
|
|
EXECUTE PROCEDURE update_entry_tsvec ();
|
|
|
|
CREATE TRIGGER entry_execution_update_tsvec
|
|
AFTER INSERT
|
|
OR
|
|
UPDATE ON entry_executions FOR EACH ROW
|
|
EXECUTE PROCEDURE update_entry_tsvec ();
|
|
|
|
ALTER TABLE patients
|
|
ADD COLUMN full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;
|
|
|
|
CREATE INDEX entries_tsvec_idx ON entries USING GIN (tsvec);
|
|
CREATE INDEX patients_full_name ON patients USING gin (full_name gin_trgm_ops);
|