2

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Hink
  • 1,054
  • 1
  • 15
  • 31

2 Answers2

4

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • That aside, chances are there are (much) **betters solutions** than creating temp tables for every call of a view. I suggest you start a new question with all details for your view. My first shot in the dark, would be CTEs ... – Erwin Brandstetter Dec 22 '14 at 15:02
  • In fact I need the temporary table only for debugging purposes. I collected another data in it for subsequent print by plain select * from t_xy. So I can remove it in production mode. – Hink Jan 13 '15 at 09:36
1

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.

Fazal Majid
  • 728
  • 7
  • 13