From 3632a76773dbfc31cf6f99ad831bf13f793871ae Mon Sep 17 00:00:00 2001 From: CiaranG Date: Sun, 14 Sep 2008 16:17:44 -0400 Subject: PostgreSQL: support for full text searching (notice and people) darcs-hash:20080914201744-f6e2c-71b2a3aec4c0b91557465323d79645e7eab2bfd1.gz --- db/laconica_pg.sql | 14 ++++++++++---- 1 file changed, 10 insertions(+), 4 deletions(-) (limited to 'db/laconica_pg.sql') diff --git a/db/laconica_pg.sql b/db/laconica_pg.sql index 315112b93..ff11219d5 100644 --- a/db/laconica_pg.sql +++ b/db/laconica_pg.sql @@ -9,14 +9,12 @@ create table profile ( bio varchar(140) /* comment 'descriptive biography' */, location varchar(255) /* comment 'physical location' */, created timestamp not null /* comment 'date this record was created' */, - modified timestamp /* comment 'date this record was modified' */ + modified timestamp /* comment 'date this record was modified' */, -/* FULLTEXT(nickname, fullname, location, bio, homepage) */ + textsearch tsvector ); create index profile_nickname_idx on profile using btree(nickname); - - create table avatar ( profile_id integer not null /* comment 'foreign key to profile table' */ references profile (id) , original integer default 0 /* comment 'uploaded by user or generated?' */, @@ -289,3 +287,11 @@ create table foreign_subscription ( ); create index foreign_subscription_subscriber_idx on foreign_subscription using btree(subscriber); create index foreign_subscription_subscribed_idx on foreign_subscription using btree(subscribed); + +/* Textsearch stuff */ + +create index textsearch_idx on profile using gist(textsearch); +create index noticecontent_idx on notice using gist(to_tsvector('english',content)); +create trigger textsearchupdate before insert or update on profile for each row +execute procedure tsvector_update_trigger(textsearch, 'pg_catalog.english', nickname, fullname, location, bio, homepage); + -- cgit v1.2.3-54-g00ecf