Add dist/search.sql

Adding SQL file to set up full text search.

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);