From 5f03b404c4a23fbde2aba12d12922507bba72e77 Mon Sep 17 00:00:00 2001
From: Lumb <lumb@git.multiple-creature.party>
Date: Tue, 25 Jun 2019 03:00:49 +0000
Subject: Add dist/search.sql

Adding SQL file to set up full text search.
---
 dist/search.sql | 11 +++++++++++
 1 file changed, 11 insertions(+)
 create mode 100644 dist/search.sql

(limited to 'dist/search.sql')

diff --git a/dist/search.sql b/dist/search.sql
new file mode 100644
index 000000000..b706a1e1f
--- /dev/null
+++ b/dist/search.sql
@@ -0,0 +1,11 @@
+ALTER TABLE statuses ADD COLUMN tsv tsvector;
+
+CREATE INDEX tsv_idx ON statuses USING gin(tsv);
+
+CREATE FUNCTION tsv_update_trigger() RETURNS trigger AS $$ begin new.tsv := to_tsvector('english', COALESCE(new.spoiler_text, '') || ' ' || new.text); return new; end $$ LANGUAGE plpgsql;
+
+CREATE TRIGGER tsvectorupdate BEFORE UPDATE ON statuses FOR EACH ROW WHEN (old.spoiler_text IS DISTINCT FROM new.spoiler_text OR old.text IS DISTINCT FROM new.text) EXECUTE PROCEDURE tsv_update_trigger();
+
+CREATE TRIGGER tsvectorinsert BEFORE INSERT ON statuses FOR EACH ROW EXECUTE PROCEDURE tsv_update_trigger();
+
+UPDATE statuses SET tsv = to_tsvector('english', COALESCE(spoiler_text, '') || ' ' || text);
-- 
cgit 

