Reduce bothering notices in plpgsql

Question

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.

Answer 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;

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):

• How to suppress INFO messages when running psql scripts

Answer 2

You can also reduce the verbosity of the messages using the GUC parameter:

set log_error_verbosity='terse';

which can of course be set at the function level.