0

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

3
  • Use the pg_notify function. Details here Commented Jul 8, 2015 at 23:49
  • But pg_notify(userdata, NEW.id) or NOTIFY userdata, NEW.id doesn't work. Can you please suggest a work around? Commented Jul 9, 2015 at 0:23
  • 1
    Both arguments must be text, so probably something like pg_notify('userdata', NEW.id::text) Commented Jul 9, 2015 at 0:32

2 Answers 2

1

In a PL/pgSQL function, when you issue a SELECT command, you need to specify where to put the result, even when no rows are returned.

So in your case you replace all

SELECT pg_notify('userdata', ...);

with

PERFORM pg_notify('userdata', ...);
Sign up to request clarification or add additional context in comments.

Comments

0

This did the job.

CREATE 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
            PERFORM PG_NOTIFY('userdata', 'cn '|| NEW.id::text);
        END IF;
        IF OLD.sn <> NEW.sn THEN
            PERFORM PG_NOTIFY('userdata', 'sn '|| NEW.id::text);
        END IF;
        IF OLD.givenname <> NEW.givenname THEN
            PERFORM PG_NOTIFY('userdata', 'givenname '|| NEW.id::text);
        END IF;
        IF OLD.c <> NEW.c THEN
            PERFORM PG_NOTIFY('userdata', 'c '|| NEW.id::text);
        END IF;
        IF OLD.l <> NEW.l THEN
            PERFORM PG_NOTIFY('userdata', 'l '|| NEW.id::text);
        END IF;
        IF OLD.st <> NEW.st THEN
            PERFORM PG_NOTIFY('userdata', 'st '|| NEW.id::text);
        END IF;
        IF OLD.street <> NEW.street THEN
            PERFORM PG_NOTIFY('userdata', 'street '|| NEW.id::text);
        END IF;
        IF OLD.o <> NEW.o THEN
            PERFORM PG_NOTIFY('userdata', 'o '|| NEW.id::text);
        END IF;
        IF OLD.ou <> NEW.ou THEN
            PERFORM PG_NOTIFY('userdata', 'ou '|| NEW.id::text);
        END IF;
        IF OLD.title <> NEW.title THEN
            PERFORM PG_NOTIFY('userdata', 'title  '|| NEW.id::text);
        END IF;
        IF OLD.description <> NEW.description THEN
            PERFORM PG_NOTIFY('userdata', 'description '|| NEW.id::text);
        END IF;
        IF OLD.postaladdress <> NEW.postaladdress THEN
            PERFORM PG_NOTIFY('userdata', 'postaladdress '|| NEW.id::text);
        END IF;
        IF OLD.telephonenumber <> NEW.telephonenumber THEN
            PERFORM PG_NOTIFY('userdata', 'telephonenumber '|| NEW.id::text);
        END IF;
        IF OLD.email <> NEW.email THEN
            PERFORM PG_NOTIFY('userdata', 'email '|| NEW.id::text);
        END IF;
        IF OLD.userpassword <> NEW.userpassword THEN
            PERFORM PG_NOTIFY('userdata', 'userpassword '|| NEW.id::text);
        END IF;
        IF OLD.adorldap <> NEW.adorldap THEN
            PERFORM PG_NOTIFY('userdata', 'adorldap '|| NEW.id::text);
        END IF;
        RETURN NEW;
    ELSIF (TG_OP='DELETE') THEN
        NOTIFY userdata,'DELETE';
        PERFORM PG_NOTIFY('userdata', 'DELETE '||OLD.id::text);
        RETURN OLD;
    ELSIF (TG_OP='INSERT') THEN
        NOTIFY userdata,'INSERT';
        PERFORM PG_NOTIFY('userdata', 'INSERT '||NEW.id::text);
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.