taotetek

CREATE FUNCTION beam_me_up() RETURNS TRIGGER AS $$
require 'yaml'
payload = new.to_yaml
$Plans["engage"] = PL::Plan.new("NOTIFY watchers, '#{payload}'")
$Plans["engage"].exec()
$$ LANGUAGE 'plruby';

CREATE TRIGGER beam_me_up_trigger AFTER INSERT ON watched_table
FOR EACH ROW EXECUTE PROCEDURE beam_me_up();

taotetek

require 'rubygems'
require 'bundler/setup'
require 'java'
require 'yaml'

$LOAD_PATH << 'vendor/jars/'
require 'postgresql-9.0-801.jdbc3.jar'

# set up our database connection to the example database...
java_import java.sql.DriverManager

taotetek

def insert_thread(url)
  insert_conn = DriverManager.get_connection(url)
  cnt = 0 
  while cnt < 10
    stmt = insert_conn.create_statement
    stmt.execute("INSERT INTO watched_table (value) VALUES (1)")
    cnt = cnt + 1 
    sleep 3
  end 
end

taotetek

CREATE FUNCTION notify_trigger() RETURNS trigger AS $$
DECLARE
BEGIN
  PERFORM pg_notify('watchers', TG_TABLE_NAME || ',id,' || NEW.id );
  RETURN new;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER watched_table_trigger AFTER INSERT ON watched_table
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();

taotetek

CREATE TABLE watched_table (
  id              SERIAL PRIMARY KEY,
  value           INT,
  date_updated    TIMESTAMP NOT NULL DEFAULT now()
);