时间戳

首页 > 解决方案 > 构建例程以在 Postgres 中生成 CREATE TRIGGER 代码

postgresql - 构建例程以在 Postgres 中生成 CREATE TRIGGER 代码

问题描述

我还没有找到一种直接的方法来检索触发器定义代码。我的意思是触发器/绑定声明，而不是触发器函数。我想我会使用一些系统目录来构建一个脚本。以下不完整的版本会产生合理的输出：

CREATE OR REPLACE FUNCTION dba.ddl_get_build_trigger_code(trigger_id oid) -- I starting from having the OID.
    RETURNS text

AS $BODY$

DECLARE

trigger_name_in text;
code text;

BEGIN
/*
What the original declaration looks like:

CREATE TRIGGER trigger_hsys_after_delete
    AFTER DELETE
    ON data.hsys
    REFERENCING OLD TABLE AS deleted_rows
    FOR EACH STATEMENT
    EXECUTE PROCEDURE data.trigger_function_log_deletion_count();
*/

SELECT tgname FROM pg_trigger WHERE oid = trigger_id INTO trigger_name_in; -- information_schema tables don't use PG OIDs.

RETURN 
'CREATE TRIGGER ' || trigger_name || chr(10) ||
 chr(9) || action_timing || ' ' || event_manipulation || chr(10) ||
 chr(9) || 'ON ' || event_object_schema || '.' || event_object_table ||

 CASE WHEN action_reference_old_table IS NOT NULL THEN
 chr(10) || chr(9) || 'REFERENCING OLD TABLE AS ' || action_reference_old_table || chr(10) END ||

--  CASE WHEN action_reference_new_table IS NOT NULL THEN
--  chr(10) || chr(9) || 'REFERENCING NEW TABLE AS ' || action_reference_new_table || chr(10) END ||

 chr(9) || 'FOR EACH ' || action_orientation || chr(10) ||
 chr(9) || action_statement || ';' as create_trigger_code

  FROM information_schema.triggers 
 WHERE trigger_name = trigger_name_in;

END;
 $BODY$
LANGUAGE plpgsql;

这是一个示例，与我的实际情况相匹配：

CREATE TRIGGER trigger_hsys_after_delete
    AFTER DELETE
    ON data.hsys
    REFERENCING OLD TABLE AS deleted_rows
    FOR EACH STATEMENT
    EXECUTE PROCEDURE trigger_function_log_deletion_count();

其中还有几个属性information_schema.triggers可能具有值，例如 action_reference_new_table。当我启用下面action_reference_new_table的NULL行时，脚本返回NULL：

CASE WHEN action_reference_new_table IS NOT NULL THEN
chr(10) || chr(9) || 'REFERENCING NEW TABLE AS ' || action_reference_new_table || chr(10) END ||

我不明白为什么NULLfor 的值action_reference_new_table会破坏我的连接代码并产生整个结果NULL。

除了对这个特定问题的帮助之外，请随时指出我应该做些什么来编写更明智的 PL/PgSQL 代码。事实证明，我要掌握它比我想象的要难。

标签： postgresqltriggersplpgsql

解决方案

只需使用

SELECT pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgname = trigger_name_in;

此外，在编写 SQL 代码时，切勿使用字符串连接。SQL注入的危险太大了。将format()函数与%I占位符一起使用。

推荐阅读