0

I'm trying to create a database listener (postgres) in Java. Every time a user inserts or deletes an entry of a table I want to receive the value that was inserted or deleted, respectively.

For that I created a trigger, as depicted bellow, that runs after an insertion and returns the respective row field that am interested in. (I've also created a similar one for deletions).

CREATE OR REPLACE FUNCTION notify() RETURNS trigger AS $insert$
    DECLARE
        command_string text;
    BEGIN
        command_string := format('{"data":"%s"}',NEW.data);
        PERFORM pg_notify('demo', command_string);
        RETURN NEW;
    END; 
$insert$ LANGUAGE plpgsql;

The problem is that by using the function pg_notify(..) I can only retrieve the values as a String in my Java application. Ideally, I would like to have a trigger that is able to return the data serialized maintaining its respective type (byte [], Integer, ect)

For this, I was thinking of using Pljava: https://github.com/tada/pljava/wiki. Although am able to create the desired trigger I don't know I would I pull the values from my java application every time a row is deleted or inserted.

Any ideas of how could build this functionality?

For the sake of completion, bellow is the code that I use to retrieve the values from the pg_notify.

@Override
public void run() {
    while (running) {
        try {
            try ( // Dummy Pull 
                    Statement stmt = dbConnection.createStatement()) {
                ResultSet rs = stmt.executeQuery("SELECT 1");
                rs.close();
            }
            // Retreive Notifications
            PGNotification notifications[] = pgconn.getNotifications();
            if (notifications != null) {
                for (PGNotification notification : notifications) {
                    logger.debug("Got notification: " + notification.getName());
                    logger.debug("Got payload: " + notification.getParameter());
                }
            }
            Thread.sleep(pollingInterval);
        } catch (InterruptedException | SQLException ex) {
            logger.fatal("WATCHER ABORTED",ex);
        }
    }
}
Pedro Chaves
  • 123
  • 13
  • Have you considered full json serialization? `to_json(NEW)` would do a reasonable job of handling most types, and a library like Jackson would take to pain out of deserialization. – teppic Jan 20 '17 at 21:57
  • @teppic Thank you, I will try that approach and get back with the results. – Pedro Chaves Jan 22 '17 at 12:50

0 Answers0