How to receive notices with PyGreSQL?

Question

I'm using PyGreSQL 4.1.1 with Postgres 9.5, and have written some stored functions. I use RAISE with different levels inside of the functions for debugging purposes, which works very well in psql, but I haven't found a way to access those messages in Python.

Example:

CREATE OR REPLACE FUNCTION my_function()  RETURNS BOOLEAN AS $_$
    BEGIN
        RAISE NOTICE 'A notice from my function.';
        RETURN TRUE;
    END
$_$ LANGUAGE plpgsql;

My Python code looks like this:

conn = pgdb.connect(database = 'mydb', user = 'myself')
cursor = conn.cursor()
cursor.execute("SELECT my_function()"):

How can I access the notice (A notice from my function.) after running my_function()?

[Connection.set_notice_receiver(func)](http://www.pygresql.org/contents/pg/connection.html#pg.Connection.set_notice_receiver) — klin, Nov 09 '17 at 13:57
@klin: Thanks, but `set_notice_receiver()` is apparently out of the `pg` module. Is there also a variant for `pgdb`? — clemens, Nov 09 '17 at 14:33
Unfortunately, notices are not included in DB-API 2.0 as it's a feature specific for Postgres. — klin, Nov 09 '17 at 14:41
@klin: Thank you for you hint. I found a solution based on this, which I have posted below. — clemens, Nov 09 '17 at 18:41

score 0 · Accepted Answer · answered Nov 09 '17 at 18:39

Due to @klin's comment I found a somewhat unclean solution. The pgdb.Connection object stores the underlying pg.Connection object in a private property named _cnx. Thus, you can set the notice receiver like this:

def my_notice_receiver(notice):
    logging.info("Notice: %s", notice)

conn._cnx.set_notice_receiver(my_notice_receiver)

How to receive notices with PyGreSQL?

1 Answers1