减少plpgsql中的麻烦通知 [英] Reduce bothering notices in plpgsql

问题描述

我有一个使用临时表的函数，如果存在的话必须被删除。

  drop table if exists t_xy; 
创建临时表t_xy在提交下降为select ...; 
  

随后我在视图中使用这个功能。该功能在选择正在进行时被多次调用。我喜欢使用raise notice命令，因为它几乎是在调试目的中报告函数中任何变量的唯一可靠的方法。问题是我必须搜索巨大的不必要的行，如：

 
注意：表t_xy不存在，跳过
 CONTEXT：SQL语句drop table if exists t_xy
 PL / pgSQL函数f_pending_operations（uuid）SQL命令中的line5 
 

有没有办法来抑制这些通知不是由提醒通知命令生成，而是通过 drop table if exists 或删除其他对象？将'client_min_messages'选项设置为'debug'会使问题更糟。

解决方案

您可以使用任何命令将通知静默到客户端 <$ c的本地设置$ c> client_min_messages ：

  SET LOCAL client_min_messages = warning; - debug会有相反的效果
 DROP TABLE if exists t_xy; 
 RESET client_min_messages = warning; - 可能需要或可能不需要
  

每个文档：

SET LOCAL 的最终效果仅在当前交易结束之前

如果您不会发行 RESET ，您可以有效地将通知留在交易的其余部分。

或者，您也可以从命令行每次呼叫设置 client_min_messages

• 如何在运行psql脚本时抑制INFO消息

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.

解决方案

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

这篇关于减少plpgsql中的麻烦通知的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！