如何检查PostgreSQL中是否存在触发器? [英] How to check if trigger exists in PostgreSQL?
本文介绍了如何检查PostgreSQL中是否存在触发器?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想验证数据库迁移的正确性,该迁移将触发器添加到某些表中。我使用的是 sqitch ,所以我想找到一种使用SQL查询进行检查的方法。我相信postgres系统表应该可以,但是我目前找不到解决方法。
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.
推荐答案
使用目录 pg_trigger 。
对表书籍
的简单查找:
select tgname
from pg_trigger
where not tgisinternal
and tgrelid = 'books'::regclass;
tgname
---------------
books_trigger
(1 row)
使用 pg_proc
来获取触发函数的来源:
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)
pg_get_triggerdef()
函数用法:
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)
在Sqitch verify
脚本中您可以使用匿名代码块,例如:
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屋!
查看全文