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.

回答1:

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=warning;  -- may or may not be needed

Per documentation:

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

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

Alternatively, you can also set client_min_messages per call from the command line:

• How to suppress INFO messages when running psql scripts