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