PostgreSQL 9, Listen / Notify, and Jruby – Part 2

I worked up one more example, in this case, using PL/Ruby rather than PL/PgSQL… and then using yaml to serialize the NEW record returned by the trigger, send it to the client, and reconstitute it as a hash.

The PL/Ruby stored proc:

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

And, the test client…

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
DriverManager.register_driver(org.postgresql.Driver.new)
url = "jdbc:postgresql://localhost/listen_notify_poller"

def insert_thread(url)
   8 lines:  insert_conn = DriverManager.get_connection(url) ------------------------------------------------------
end

def listen_thread(url)
  listen_conn = DriverManager.get_connection(url)

  stmt = listen_conn.create_statement
  stmt.execute("LISTEN watchers")
  stmt.close

  while true
    sleep 1
    puts 'polling...'

    notifications = listen_conn.get_notifications || []

    notifications.each do |notification|
      unless notification.nil?
        test = YAML::load(notification.parameter)
        puts test.inspect
      end
    end
  end
end

insert_thread = Thread.new{insert_thread(url)}
listen_thread = Thread.new{listen_thread(url)}

listen_thread.join
insert_thread.join

About taotetek

Sometimes stereotypical but never ironic. You can't stop the signal, Mal. All my opinions are my own, unless I stole them from you.
This entry was posted in jruby, postgresql and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s