simple postgres full text search

search.sql

-- Setup
alter table orders add column search_vector tsvector;

update orders as o 
set search_vector = to_tsvector( 'english', 
    o.id                 || ' ' ||
    coalesce(o.name, '') || ' ' ||
    coalesce(r.name, '') || ' ' ||
    coalesce(u.name, '') || ' ' ||
    coalesce(u.email, '')|| ' ' ||
    coalesce(u.organization, '') || ' ' 
  )
from restaurants as r, users as u
where o.restaurant_id = r.id
and o.user_id = u.id;

create index orders_search_idx on
  orders using gin( search_vector );

-- Prefix Querying

select * from orders 
where search_vector @@ to_tsquery( 'english', 'bazaa:*');


-- Query Performance!

w/o index ~ 18ms
w/  index ~ .1 ms

trigger.sql

-- Since the vector is compiled from the orders, restaurants and users table, 
-- I need to create a trigger for each table.

-- First I created a view for simplicity when compiling the vector (the search document)
create view orders_search_view as
  select 
    orders.id           as order_id,
    orders.name         as order_name,
    restaurants.name    as restaurant_name,
    users.name          as user_name,
    users.email         as user_email,
    users.organization  as user_organization
  from orders 
  join restaurants on orders.restaurant_id = restaurants.id
  join users on orders.user_id = users.id;

-- This trigger procedure compiles the search vector. 
-- Note that the where clause should accomodate which table is triggered

create or replace function update_orders_search_vector()
returns trigger as $$
begin
  update orders as o 
  set search_vector = to_tsvector( 'english', 
      o.id                            || ' ' ||
      coalesce(order_name, '')        || ' ' ||
      coalesce(restaurant_name, '')   || ' ' ||
      coalesce(user_name, '')         || ' ' ||
      coalesce(user_email, '')        || ' ' ||
      coalesce(user_organization, '') || ' ' 
    )
  from orders_search_view as osv
  where o.id = osv.order_id and osv.order_id = new.id;
  -- where o.id = osv.order_id and osv.restaurant_id = new.id;
  -- where o.id = osv.order_id and osv.user_id = new.id; 
  return new;
end;
$$ language plpgsql;

-- I wanted to reuse the same procedure for each of these triggers, but now I've
-- introduced direct dependency between View <--> Trigger Proc. <--> Table Trigger.
-- Not sure how to generalize this better.

drop trigger orders_search_update on orders;
create trigger orders_search_update
  after update of name
  on orders
  for each row
  execute procedure update_orders_search_vector();

drop trigger orders_search_update on restaurants;
create trigger orders_search_update
  after update of name
  on restaurants
  for each row
  execute procedure update_orders_search_vector();

drop trigger orders_search_update on users;
create trigger orders_search_update
  after update of name, email, organization
  on users
  for each row
  execute procedure update_orders_search_vector();