Task counter

task_counter_migration.rb

class AddCountTriggers < ActiveRecord::Migration
  def up
    execute %q{
      CREATE OR REPLACE FUNCTION update_task_counters (taskid integer)
      RETURNS void AS $$
      BEGIN
        update tasks set
          users_count = coalesce((select count(9) from taskships where task_id = taskid), 0)
        where id = taskid;
      END;
      $$ LANGUAGE plpgsql;
    }
    execute %q{
      CREATE OR REPLACE FUNCTION update_taskship_counters()
        RETURNS trigger AS $update_taskship_counters$
          BEGIN
            IF (tg_op = 'UPDATE') THEN
              PERFORM update_task_counters(new.task_id);
            return new;
            ELSIF (tg_op = 'INSERT') then
              PERFORM update_task_counters(new.task_id);
              RETURN new;
            ELSIF (tg_op = 'DELETE') then
              PERFORM update_task_counters(old.task_id);
              RETURN null;
            END IF;
            RETURN null;
          END;
        $update_taskship_counters$ LANGUAGE plpgsql;
    }


    execute %q{
      CREATE TRIGGER update_taskship_counters
      AFTER INSERT OR UPDATE OR DELETE ON taskships
      FOR EACH ROW EXECUTE PROCEDURE update_taskship_counters();
    }
  end

  def down
    execute "DROP TRIGGER IF EXISTS update_taskship_counters;"
    execute "DROP FUNCTION IF EXISTS update_taskship_counters;"
    execute "DROP FUNCTION IF EXISTS update_task_counters(taskit integer);"
  end
end