Get warning messages through psycopg2

Question

I want to call a plpgsql function through psycopg2 and see the warning messages. I.e, I have this function:

create or replace function test_warning() returns void as $$
begin
raise warning 'this is only a test';
end; 
$$
language plpgsql;

and call it so in python:

import psycopg2
conn = psycopg2.connect(conn_string)
cursor = conn.cursor()
cursor.callproc("test_warning")
# or so:
cursor.execute('SELECT test_warning()')

Unfortunately the warning message as defined in plpgsql does not appear anywhere in the python output. Is there a way to get the warning message printed in the python output?

Answer 1

The notices member of the connection is a list of the session's messages sent to the client up to that point:

for notice in conn.notices:
    print notice

http://initd.org/psycopg/docs/connection.html#connection.notices

To get the last notice:

print conn.notices[-1]

If an exception is raised inside a function, and not caught, no warning will be received. That is because a function wraps an implicit transaction and everything inside that transaction is rolled back including warnings.

Answer 2

I don't have the reputation to comment on Clodoaldo's answer, but my solution for getting the latest notices (note that a query can generate several notices) is pretty straightforward:

def execute_query(query, conn):
    logger = logging.getLogger('test')
    nr_notices = len(conn.notices)
    cursor = conn.cursor()
    cursor.execute(query)
    for notice in conn.notices[nr_notices:]:
        logger.info(f'NOTICE: {notice}.')