about summary refs log tree commit diff
path: root/dist
diff options
context:
space:
mode:
authorLumb <lumb@git.multiple-creature.party>2019-06-25 03:00:49 +0000
committermultiple creatures <dev@multiple-creature.party>2019-07-17 02:22:47 -0500
commit5f03b404c4a23fbde2aba12d12922507bba72e77 (patch)
tree02c3aa8ce78a806162922fbd6f11f52de5dcc888 /dist
parent7629b2c22b4eba5000a4111ea6e9d26b0944e04c (diff)
Add dist/search.sql
Adding SQL file to set up full text search.
Diffstat (limited to 'dist')
-rw-r--r--dist/search.sql11
1 files changed, 11 insertions, 0 deletions
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);