postgresqldebuggingplpgsqlnotice

Reduce bothering notices in plpgsql

I have a function which uses temporary table, that must be dropped if exists. 

drop table if exists t_xy;
create temp table t_xy on commit drop as select ...;

Subsequently I use this function in a view. The function is called many times while select is in progress. I like to use "raise notice" command because it is almost the only reliable way to report any variables in functions for debug purposes. The problem is I must search for them in huge amount of unwanted lines like:

NOTICE:  table "t_xy" does not exist, skipping
CONTEXT:  SQL statement "drop table if exists t_xy"
PL/pgSQL function f_pending_operations(uuid) line5 in SQL command

Is there a way to suppress such notices that haven't been generated by raise notice command, but by drop table if exists or dropping other objects? Setting 'client_min_messages' option to 'debug' makes the problem worse.

Solution

  • You can silence notices to the client from any command with a local setting for client_min_messages:

    SET LOCAL client_min_messages = warning;  -- "debug" would have opposite effect
DROP TABLE if exists t_xy;
-- RESET client_min_messages;

    If you don't issue RESET you effectively silence notices for the rest of the transaction. The manual:

    The effects of SET LOCAL last only till the end of the current transaction

    Alternatively, you can set client_min_messages in the call from the command line (for the duration of the session):