如何保存和恢复ON_ERROR_STOP的值？ [英] How to save and restore value of ON_ERROR_STOP?

问题描述

是否可以保存，临时更改并恢复 psql ON_ERROR_STOP 的值code>变量？

Is there a way to save, temporarily change, and then restore the value of the psql ON_ERROR_STOP variable?

基本上，我想在 psql 脚本：

save_on_error_stop=ON_ERROR_STOP

\unset ON_ERROR_STOP
ALTER TABLE foo DROP COLUMN bar;  -- (for example)
\set ON_ERROR_STOP save_on_error_stop

ALTER TABLE foo ADD COLUMN bar;

重点是'\set'命令实际上不会设置 ON_ERROR_STOP 。

The point being that the '\set' command at the end won't actually set ON_ERROR_STOP unless it was set before.

推荐答案

我认为不可能在单个 psql 会话中执行此操作。根据手册，可以单独使用 \set 命令列出所有 psql 变量。

I don't think it is possible to do this in a single psql session. According to the manual, one can use \set command on it's own to list all psql variables.

一个人可以记住外壳程序中的设置，但这使整个事情变得毫无用处，因为仅执行所需的查询集来强制执行所需的 ON_ERROR_STOP

One can memorize the setting in shell, but this makes the whole thing useless, as it is much more simple just to execute the desired set of queries enforcing the desired ON_ERROR_STOP value.

另一种方法是编写一个匿名代码块和 DO 一些额外的逻辑来检测

Another alternative is to write an anonymous code block and DO some extra logic to detect, whether column needs to be dropped before adding it.

BTW，直接删除并添加列的目的是什么？

BTW, What is the purpose of dropping and adding column straight after?

如果仅是要确保不存在此类列，如何修改 DO 块：

If it is only to make sure no such column exists, how bout this DO block:

DO $$
BEGIN
IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema='public' AND table_name='foo'
       AND column_name='bar')
THEN
    EXECUTE format('ALTER TABLE %I.%I ADD %I text',
                   'public','foo','bar');
END IF;
END; $$;

如果您倾向于经常进行此类检查，则还可以创建一个函数。

You can also create a function if you tend to do such check quite often.

这篇关于如何保存和恢复ON_ERROR_STOP的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！