如何检查 PostgreSQL 中是否存在触发器? [英] How to check if trigger exists in PostgreSQL?

问题描述

我想验证向某些表添加触发器的数据库迁移的正确性.我正在使用 sqitch，所以我想找到一种使用 SQL 查询进行检查的方法.我相信 postgres 系统表应该是可能的，但我目前找不到办法做到这一点.

解决方案

使用目录 pg_trigger.

一个表books的简单查找:

选择 tgname来自 pg_trigger哪里不是 tgisinternal和 tgrelid = 'books'::regclass;名称---------------book_trigger(1 行)

使用pg_proc 获取触发函数的来源:

选择 tgname、proname、prosrc来自 pg_trigger在 p.oid = tgfoid 上加入 pg_proc p哪里不是 tgisinternal和 tgrelid = 'books'::regclass;名称 |名誉|源头---------------+------------------------------+----------------------------------------------book_trigger |book_trigger |+||开始 +||如果 tg_op = 'UPDATE' 然后 +||如果 new.listorder >old.listorder 然后 +||更新书籍 +||设置列表顺序 = 列表顺序- 1 +||其中 listorder <= new.listorder +||和列表顺序 >old.listorder +||和 id <>新编号；+||其他 +||更新书籍 +||设置列表顺序 = 列表顺序+ 1 +||其中 listorder >= new.listorder +||和列表顺序

pg_get_triggerdef() 函数用法:

选择 pg_get_triggerdef(t.oid) 作为触发器声明"来自 pg_trigger t哪里不是 tgisinternal和 tgrelid = 'books'::regclass;触发器声明--------------------------------------------------------------------------------------------------------------CREATE TRIGGER books_trigger BEFORE INSERT OR UPDATE ON books FOR EACH ROW EXECUTE PROCEDURE books_trigger()(1 行)

在 Sqitch verify 脚本中，您可以使用匿名代码块，例如:

做$$开始执行 tgname来自 pg_trigger哪里不是 tgisinternal和 tgrelid = 'books'::regclass;如果没有找到那么引发异常未找到触发器"；万一;结束 $$;

I want to verify correctness of database migrations which add triggers to some tables. I'm using sqitch, so I'd like to find a way to check it with SQL queries. I believe it should be possible with postgres system tables, but I currently can't find a way to do this.

解决方案

Use the catalog pg_trigger.

A simple lookup for a table books:

select tgname
from pg_trigger
where not tgisinternal
and tgrelid = 'books'::regclass;

    tgname     
---------------
 books_trigger
(1 row)

Using pg_proc to get the source of the trigger function:

select tgname, proname, prosrc 
from pg_trigger
join pg_proc p on p.oid = tgfoid
where not tgisinternal
and tgrelid = 'books'::regclass;

    tgname     |    proname    |                    prosrc
---------------+---------------+------------------------------------------------
 books_trigger | books_trigger |                                               +
               |               | begin                                         +
               |               |     if tg_op = 'UPDATE' then                  +
               |               |         if new.listorder > old.listorder then +
               |               |             update books                      +
               |               |             set listorder = listorder- 1      +
               |               |             where listorder <= new.listorder  +
               |               |             and listorder > old.listorder     +
               |               |             and id <> new.id;                 +
               |               |         else                                  +
               |               |             update books                      +
               |               |             set listorder = listorder+ 1      +
               |               |             where listorder >= new.listorder  +
               |               |             and listorder < old.listorder     +
               |               |             and id <> new.id;                 +
               |               |             end if;                           +
               |               |     else                                      +
               |               |         update books                          +
               |               |         set listorder = listorder+ 1          +
               |               |         where listorder >= new.listorder      +
               |               |         and id <> new.id;                     +
               |               |     end if;                                   +
               |               |     return new;                               +
               |               | end
(1 row)

Example of the pg_get_triggerdef() function usage:

select pg_get_triggerdef(t.oid) as "trigger declaration"
from pg_trigger t
where not tgisinternal
and tgrelid = 'books'::regclass;

                                             trigger declaration                
--------------------------------------------------------------------------------------------------------------
 CREATE TRIGGER books_trigger BEFORE INSERT OR UPDATE ON books FOR EACH ROW EXECUTE PROCEDURE books_trigger()
(1 row) 

In a Sqitch verify script you can use an anonymous code block, e.g.:

do $$
begin
    perform tgname
    from pg_trigger
    where not tgisinternal
    and tgrelid = 'books'::regclass;
    if not found then 
        raise exception 'trigger not found';
    end if;
end $$;

这篇关于如何检查 PostgreSQL 中是否存在触发器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！