Get all notices of PostgreSQL's RAISE NOTICE

Question

I have big DB function which has multiple lines like this

RAISE NOTICE 'some step completed';

I want to get all this notices in my PHP application. I found only pg_last_notice() function which only returns the very last notice.

Is there any way I can get all notices?

---

Example: DB function:

CREATE OR REPLACE FUNCTION do_smth()
  RETURNS void AS
$BODY$

BEGIN

-- some actions
RAISE NOTICE 'Result of the actions:...';
-- some other actions
RAISE NOTICE 'Result of the other actions..';

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

PHP code:

<?php
// ...
$db->exec("SELECT do_smth()"); // executing DB function

$last_notice = pg_last_notice($db_connection); 
// returns 'NOTICE: Result of the other actions..'

Answer 1

According to the libpq notice documentation "The default notice handling function prints the message on stderr, but the application can override this behavior by supplying its own handling function."

In your case the "application" (php itself) is overriding this behavior by specifying custom notice handler, called _php_pgsql_notice_handler:

    /* set notice processor */
    if (! PGG(ignore_notices) && Z_TYPE_P(return_value) == IS_OBJECT) {
        PQsetNoticeProcessor(pgsql, _php_pgsql_notice_handler, link);
    }

That means that PostgreSQL notices are not propagated further into stderr, but are captured and processed by that handler.

In the handler itself (line #827) you can see that every time a notice is issued, php updates the variable holding it, and does not append the value to some array. Hence at the end only the last notice is present in that variable, obtainable by calling pg_last_notice().

So, it looks like it is not possible to obtain previous PostgreSQL notices from within PHP.

However, if you look further into the same notice handler, you would see that it is writing the notices to the error log, in case pgsql.ignore_notices = 0, pgsql.log_notices = 1 and E_NOTICE is included into the error_reporting. I guess with some php error handling functions juggling you will be able to obtain something.

Answer 2

pg_last_notice() now accepts an optional parameter to specify an operation.

This can be done with one of the following new constants:

• PGSQL_NOTICE_LAST: to return last notice
• PGSQL_NOTICE_ALL: to return all notices
• PGSQL_NOTICE_CLEAR: to clear notices

More info for Changed Functions in version 7.1 HERE.