I have the following class which listens to notifications from POSTGRESQL database.
class Listener extends Thread {
private Connection conn;
private org.postgresql.PGConnection pgconn;
Listener(Connection conn) throws Exception {
this.setConn(conn);
this.pgconn = (org.postgresql.PGConnection) conn;
Statement stmt = conn.createStatement();
stmt.execute("LISTEN userdata");
stmt.close();
}
public Connection getConn() {
return conn;
}
public void setConn(Connection conn) {
this.conn = conn;
}
public static void main(String[] args) {
try {
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost/users";
String user = "admin";
String password = "password";
Connection listenerCon = DriverManager.getConnection(url, user,
password);
Listener listener = new Listener(listenerCon);
listener.start();
} catch (Exception e) {
e.printStackTrace();
}
}
public void run() {
while (true) {
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt
.executeQuery("SELECT * FROM userdata WHERE id='1'");
rs.close();
stmt.close();
org.postgresql.PGNotification notifications[] = pgconn
.getNotifications();
if (notifications != null) {
for (int i = 0; i < notifications.length; i++) {
System.out.println("Got notification: "
+ notifications[i].getParameter());
}
}
Thread.sleep(500);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
These are the following columns in userdata table:
id
cn
sn
givenname
c
l
st
street
o
ou
title
description
postaladdress
telephonenumber
email
userpassword
adorldap
Here is my Stored Procedure in users database
CCREATE OR REPLACE FUNCTION get_effected_row_primarykey() RETURNS TRIGGER AS
$$
DECLARE
primarykey TEXT;
BEGIN
IF (TG_OP='UPDATE') THEN
IF OLD.cn <> NEW.cn THEN
SELECT PG_NOTIFY('userdata', 'cn');
END IF;
IF OLD.sn <> NEW.sn THEN
SELECT PG_NOTIFY('userdata', 'sn');
END IF;
IF OLD.givenname <> NEW.givenname THEN
SELECT PG_NOTIFY('userdata', 'givenname');
END IF;
IF OLD.c <> NEW.c THEN
SELECT PG_NOTIFY('userdata', 'c');
END IF;
IF OLD.l <> NEW.l THEN
SELECT PG_NOTIFY('userdata', 'l');
END IF;
IF OLD.st <> NEW.st THEN
SELECT PG_NOTIFY('userdata', 'st');
END IF;
IF OLD.street <> NEW.street THEN
SELECT PG_NOTIFY('userdata', 'street');
END IF;
IF OLD.o <> NEW.o THEN
SELECT PG_NOTIFY('userdata', 'o');
END IF;
IF OLD.ou <> NEW.ou THEN
SELECT PG_NOTIFY('userdata', 'ou');
END IF;
IF OLD.title <> NEW.title THEN
SELECT PG_NOTIFY('userdata', 'title');
END IF;
IF OLD.description <> NEW.description THEN
SELECT PG_NOTIFY('userdata', 'description');
END IF;
IF OLD.postaladdress <> NEW.postaladdress THEN
SELECT PG_NOTIFY('userdata', 'postaladdress');
END IF;
IF OLD.telephonenumber <> NEW.telephonenumber THEN
SELECT PG_NOTIFY('userdata', 'telephonenumber');
END IF;
IF OLD.email <> NEW.email THEN
SELECT PG_NOTIFY('userdata', 'email');
END IF;
IF OLD.userpassword <> NEW.userpassword THEN
SELECT PG_NOTIFY('userdata', 'userpassword');
END IF;
IF OLD.adorldap <> NEW.adorldap THEN
SELECT PG_NOTIFY('userdata', 'adorldap');
END IF;
RETURN NEW;
ELSIF (TG_OP='DELETE') THEN
SELECT PG_NOTIFY('userdata', OLD.id::text);
RETURN OLD;
ELSIF (TG_OP='INSERT') THEN
SELECT PG_NOTIFY('userdata', NEW.id::text);
RETURN NEW;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
Here is my trigger
CREATE TRIGGER create_data AFTER INSERT OR UPDATE OR DELETE
ON userdata
FOR EACH ROW EXECUTE PROCEDURE get_effected_row_primarykey();
Java program above works fine, it gets notified whenever there is an insert, update, or a delete in table. My questions are:
Updated my stored procedure. When I insert into the table, stored procedure is returning the following error:
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function get_effected_row_primarykey() line 59 at SQL statement
Can anyone help resolve it.Any help appreciated
pg_notifyfunction. Details heretext, so probably something likepg_notify('userdata', NEW.id::text)