发表文章

[Java] 在 Postgres SQL 中, 触发器信息只显示触发器名称。 "Postgres SQL에서 트리거 정보가 트리거 명칭만 표시됩니다."[TadpoleForDBTools]

jeongjaehong 3月前 15

트리거명、소유자、데이베이스정보、트리거실행시점、실행되는함수등과같은내용을아래와같이조회할수있는듯합니다。

9.3.5.1 버젼에서확인된쿼리입니다。

选择
(current_database ()):: information_schema sql_identifier 为数据库,
(t. tgname):: information_schema sql_identifier 作为触发器,
(文本):: information_schema character_data 作为事件,
(current_database (): information_schema sql_identifier 为定义
(c. relname):: information_schema sql_identifier 为 Table_name,
(pg_has_role (relowner, "使用情况":: 文本) 然后 (
选择 rm m [1] 作为 m
从 regexp_matches (pg_get_triggerdef (t. oid), "。35,} ((. +)) 执行过程 ":: 文本" rm (m) 限制 1)
其他空:: 文本结尾):: information_schema character_data 为 action_condition,
("子串" (pg_get_triggerdef (t. oid), ("位置" ("子字符串" (pg_get_triggerdef (t. oid), 48), "执行过程":: 文本) + 47)):: information_schema. character_data 作为声明,
((t. tgtype):: 整数 & 1) 当1然后 ' 行 ':: 文本其他 ' 声明 ':: 文本结尾):: information_schema. character_data | |'||
((t. tgtype):: 整数 & 66)
当2然后 ' 之前 ':: 文本
当64然后 ' 而不是 ':: 文本
其他 "后":: 文本结束):: information_schema character_data 作为时间,
(NULL:: 时间戳带时区):: information_schema time_stamp 创建的
(tgenabled = "O" 然后 ' 有效的 ' else "结束) 作为状态
从 pg_namespace n,
pg_class 角
pg_trigger t,
(值 (4, "插入":: 文本), (8, "删除":: 文本), (16, "更新":: 文本)) em (num, 文本)
(((((((((((relnamespace) (旧 = c) 和 (c. tgrelid))
(((t. tgtype):: 整数 & em) < > 0))
(不是 t. tgisinternal))
(不是 pg_is_other_temp_schema (老)))
(pg_has_role (c. relowner, "用法":: 文本)
或 has_table_privilege (c. oid, "插入、更新、删除、截断、引用、触发器":: 文本))
或 has_any_column_privilege (c. oid, "插入, 更新, 参考":: 文本));

原文:

트리거명, 소유자, 데이베이스정보, 트리거실행시점, 실행되는함수등과 같은 내용을 아래와 같이 조회할 수 있는듯 합니다.

9.3.5.1 버젼에서 확인된 쿼리 입니다.

SELECT
(current_database())::information_schema.sql_identifier AS Database,
(t.tgname)::information_schema.sql_identifier AS trigger,
(em.text)::information_schema.character_data AS Event,
(current_database())::information_schema.sql_identifier AS Definer,
(c.relname)::information_schema.sql_identifier AS Table_name,
(CASE WHEN pg_has_role(c.relowner, 'USAGE'::text) THEN (
SELECT rm.m[1] AS m
FROM regexp_matches(pg_get_triggerdef(t.oid), '.{35,} WHEN ((.+)) EXECUTE PROCEDURE'::text) rm(m) LIMIT 1)
ELSE NULL::text END)::information_schema.character_data AS action_condition,
("substring"(pg_get_triggerdef(t.oid), ("position"("substring"(pg_get_triggerdef(t.oid), 48), 'EXECUTE PROCEDURE'::text) + 47)))::information_schema.character_data AS Statement,
(CASE ((t.tgtype)::integer & 1) WHEN 1 THEN 'ROW'::text ELSE 'STATEMENT'::text END)::information_schema.character_data ||' '||
(CASE ((t.tgtype)::integer & 66)
WHEN 2 THEN 'BEFORE'::text
WHEN 64 THEN 'INSTEAD OF'::text
ELSE 'AFTER'::text END)::information_schema.character_data AS Timing,
(NULL::timestamp with time zone)::information_schema.time_stamp AS created,
(case when t.tgenabled = 'O' then 'VALID' else '' end) as Status
FROM pg_namespace n,
pg_class c,
pg_trigger t,
( VALUES (4,'INSERT'::text), (8,'DELETE'::text), (16,'UPDATE'::text)) em(num, text)
WHERE ((((((n.oid = c.relnamespace) AND (c.oid = t.tgrelid))
AND (((t.tgtype)::integer & em.num) <> 0))
AND (NOT t.tgisinternal))
AND (NOT pg_is_other_temp_schema(n.oid)))
AND ((pg_has_role(c.relowner, 'USAGE'::text)
OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text))
OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES'::text)));

相关推荐
最新评论 (1)
hangum 3月前
1

演示 Pgsql 也可以正常工作。

原文:

데모 pgsql도 정상 동작하면 적용하지요.

返回
发表文章
jeongjaehong
文章数
44
评论数
44
注册排名
92846