使用psql -v参数调用PL/pgSQL脚本删除触发器失败求助

核心问题 #

psql通过-v定义的是客户端变量，并非PostgreSQL服务器端的配置参数，所以你用current_setting()根本读不到这个变量的值：

• 加true参数时，current_setting找不到参数会返回NULL，导致WHERE trigger_name=v_trig_name匹配不到任何触发器，自然删不掉；
• 去掉true参数时，服务器找不到这个配置参数，直接抛出错误。

解决方案1：直接用psql变量注入（最简单） #

修改PL/pgSQL代码，直接使用psql的变量替换语法，让psql在执行前就把变量值注入到代码中：

DO $$
DECLARE
    r RECORD;
    v_trig_name text := :'trig_name'; -- 直接使用psql变量，自动处理字符串转义
BEGIN
    FOR r IN (SELECT event_object_table, trigger_name
              FROM information_schema.triggers
              WHERE trigger_name = v_trig_name)
    LOOP
        EXECUTE 'DROP TRIGGER IF EXISTS ' || quote_ident(r.trigger_name) ||
                ' ON ' || quote_ident(r.event_object_table) || ';';
    END LOOP;
END $$;

执行命令保持不变：

psql -d dbtest -f /var/lib/postgresql/data/dump/drop_triggers.sql -U srhbatch -v trig_name='trigger_test'

解决方案2：通过会话级参数传递（更规范） #

先在SQL文件开头用psql变量设置一个会话级的临时配置参数，再在PL/pgSQL中读取：

-- 先设置会话级参数，psql会替换:trig_name为传入的值
SET LOCAL trig_name = :'trig_name';

DO $$
DECLARE
    r RECORD;
    v_trig_name text := current_setting('trig_name'); -- 读取会话级参数
BEGIN
    FOR r IN (SELECT event_object_table, trigger_name
              FROM information_schema.triggers
              WHERE trigger_name = v_trig_name)
    LOOP
        EXECUTE 'DROP TRIGGER IF EXISTS ' || quote_ident(r.trigger_name) ||
                ' ON ' || quote_ident(r.event_object_table) || ';';
    END LOOP;
END $$;

执行命令同样保持不变，这种方法适合需要在多个PL/pgSQL块间共享变量的场景。

验证方法 #

执行删除命令后，用以下SQL查询触发器是否存在：

SELECT trigger_name, event_object_table 
FROM information_schema.triggers 
WHERE trigger_name = 'trigger_test';

内容的提问来源于stack exchange，提问作者Antonin