Hi,
I have a card that has an attribute “NetworkSecurity”. If that attribute is changed I want to notify an external service that updates settings in our LDAP based on the change. So in my trigger function for that card I do the following:
if (new."NetworkSecurity" is not null and new."NetworkSecurity" <> old."NetworkSecurity") then
perform(
with payload(topic, building, nwsecurity, tstp) as (
select 'network_security_change' as topic, new."Code" as building, (select "Code" from "LookUp" lu where lu."Id" = new."NetworkSecurity" and lu."Status" = 'A') as nwsecurity, now() as tstp
)
select pg_notify('your_channel_name', row_to_json(payload)::text) from payload
);
end if;
Then I have my “broker” program checking for the notifications:
private void checkForNotifications() {
try {
PGNotification notifications[] = pgConnection.getNotifications();
if (notifications == null || notifications.length == 0) {
LOGGER.info("No notifications received");
return;
}
for (PGNotification notification : notifications) {
LOGGER.info("Notification {} with parameter {} received", notification.getName(), notification.getParameter());
Map<String, String> parameters = CmdbuildReady2UseChangeListerServiceImpl.OBJECT_MAPPER.readValue(notification.getParameter(), new TypeReference<Map<String, String>>(){});
String topic = parameters.get("topic");
if (topic != null && changeListenerServices.containsKey(topic)) {
LOGGER.info("Notification is for topic {} and we have a service available", topic);
// now process your notification ...
} else if (topic == null) {
LOGGER.error("No topic attribute found in payload");
} else if (!changeListenerServices.containsKey(topic)) {
LOGGER.warn("No service available for topic {}", topic);
}
}
} catch (final SQLException e) {
LOGGER.error(e.getLocalizedMessage());
e.printStackTrace();
} catch (final IOException e) {
LOGGER.error(e.getLocalizedMessage());
e.printStackTrace();
}
}
Here I read the notifications from my postgresql connection. I unmarshall the JSON payload and look for my topic attribute (in this case the “network_security_change” set in the trigger).
If the notification contains a topic and if I have a service that can handle these notifications (hashmap look “changeListenerServices.containsKey(topic)”) then I can process the messages.
This is how I connect to the database and listen for the notifications:
connection = dataSource.getConnection();
pgConnection = connection.unwrap(PGConnection.class);
Statement statement = connection.createStatement();
statement.execute("LISTEN " + “your_channel_name”);
statement.close();
Hope this clarifies a little bit
Alex