是否可以动态循环表的列？

发布于 2024-09-06 09:41:32 字数 432 浏览 5 评论 0原文

我有一个用于表测试的触发器函数，其中包含以下代码片段：

IF TG_OP='UPDATE' THEN
    IF OLD.locked > 0 AND
 (       OLD.org_id <> NEW.org_id OR
            OLD.document_code <> NEW.document_code OR
            -- other columns ...
 )
THEN
    RAISE EXCEPTION 'Message';
-- more code

因此，我静态检查所有列的新值及其先前的值，以确保完整性。现在，每当我的业务逻辑发生变化并且我必须向该表中添加新列时，我每次都必须修改此触发器。我认为如果我可以动态检查该表的所有列，而无需显式键入它们的名称，那就更好了。

怎么办呢？

原文

I have a trigger function for a table test which has the following code snippet:

IF TG_OP='UPDATE' THEN
    IF OLD.locked > 0 AND
 (       OLD.org_id <> NEW.org_id OR
            OLD.document_code <> NEW.document_code OR
            -- other columns ...
 )
THEN
    RAISE EXCEPTION 'Message';
-- more code

So I am statically checking all the column's new value with its previous value to ensure integrity. Now every time my business logic changes and I have to add new columns into that table, I will have to modify this trigger each time. I thought it would be better if somehow I could dynamically check all the columns of that table, without explicitly typing their name.

How can it be done?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

月依秋水 2024-09-13 09:41:32

从 9.0 beta2 文档开始，有关触发器中 WHEN 子句的文档可能可以在触发器主体中的早期版本中使用：

OLD.* IS DISTINCT FROM NEW.*

或可能（来自 8.2 发行说明）

IF 行（新。*）与行（旧。*）不同

回复收藏 0 原文

别挽留 2024-09-13 09:41:32

看一下information_schema，有一个视图“columns”。执行查询以从触发触发器的表中获取所有当前列名：

SELECT 
    column_name 
FROM 
    information_schema.columns 
WHERE 
    table_schema = TG_TABLE_SCHEMA 
AND 
    table_name = TG_TABLE_NAME;

循环遍历结果即可！

更多信息可以在精细手册中找到。

Take a look at the information_schema, there is a view "columns". Execute a query to get all current columnnames from the table that fired the trigger:

SELECT 
    column_name 
FROM 
    information_schema.columns 
WHERE 
    table_schema = TG_TABLE_SCHEMA 
AND 
    table_name = TG_TABLE_NAME;

Loop through the result and there you go!

More information can be found in the fine manual.

回复收藏 0 原文

可可 2024-09-13 09:41:32

在 Postgres 9.0 或更高版本中添加 WHEN子句到您的触发器定义（CREATE TRIGGER语句）：

CREATE TRIGGER foo
BEFORE UPDATE
FOR EACH ROW
WHEN (OLD IS DISTINCT FROM NEW)  -- parentheses required!
EXECUTE PROCEDURE ...;

仅适用于触发器BEFORE / AFTER UPDATE，其中定义了 OLD 和 NEW。尝试将此 WHEN 子句与 INSERT 或 DELETE 触发器一起使用时，您会遇到异常。

并相应地从根本上简化触发器功能：

...
IF OLD.locked > 0 THEN
   RAISE EXCEPTION 'Message';
END IF;
...

无需测试IF TG_OP='UPDATE' ...，因为该触发器仅适用于UPDATE 。

或者也将该条件移到 WHEN 子句中：

CREATE TRIGGER foo
BEFORE UPDATE
FOR EACH ROW
WHEN (OLD.locked > 0
  AND OLD IS DISTINCT FROM NEW)
EXECUTE PROCEDURE ...;

在触发函数中仅留下无条件的 RAISE EXCEPTION ，该函数仅在需要时才调用。

阅读细则：

在 BEFORE 触发器中，WHEN 条件在
函数正在执行或将要执行，因此使用 WHEN 并不重要
与开始时测试相同条件不同
触发功能。请特别注意，NEW 行所看到的
条件是当前值，可能被之前的修改
触发器。此外，BEFORE 触发器的 WHEN 条件不允许
检查 NEW 行的系统列（例如 oid），因为这些
尚未设置。
在 AFTER 触发器中，WHEN 条件在
发生行更新，并确定事件是否排队
在语句末尾触发触发器。因此，当 AFTER 触发器的
WHEN 条件不返回 true，则无需对队列进行排队
事件也不重新获取语句末尾的行。这可能会导致
修改许多行的语句中的显着加速，如果
只需要为几行触发触发器。

还要解决问题标题

是否可以动态循环表的列？

是的。示例：

In Postgres 9.0 or later add a WHEN clause to your trigger definition (CREATE TRIGGER statement):

CREATE TRIGGER foo
BEFORE UPDATE
FOR EACH ROW
WHEN (OLD IS DISTINCT FROM NEW)  -- parentheses required!
EXECUTE PROCEDURE ...;

Only possible for triggers BEFORE / AFTER UPDATE, where both OLD and NEW are defined. You'd get an exception trying to use this WHEN clause with INSERT or DELETE triggers.

And radically simplify the trigger function accordingly:

...
IF OLD.locked > 0 THEN
   RAISE EXCEPTION 'Message';
END IF;
...

No need to test IF TG_OP='UPDATE' ... since this trigger only works for UPDATE anyway.

Or move that condition in the WHEN clause, too:

CREATE TRIGGER foo
BEFORE UPDATE
FOR EACH ROW
WHEN (OLD.locked > 0
  AND OLD IS DISTINCT FROM NEW)
EXECUTE PROCEDURE ...;

Leaving only an unconditional RAISE EXCEPTION in your trigger function, which is only called when needed to begin with.

Read the fine print:

In a BEFORE trigger, the WHEN condition is evaluated just before the
function is or would be executed, so using WHEN is not materially
different from testing the same condition at the beginning of the
trigger function. Note in particular that the NEW row seen by the
condition is the current value, as possibly modified by earlier
triggers. Also, a BEFORE trigger's WHEN condition is not allowed to
examine the system columns of the NEW row (such as oid), because those
won't have been set yet.
In an AFTER trigger, the WHEN condition is evaluated just after the
row update occurs, and it determines whether an event is queued to
fire the trigger at the end of statement. So when an AFTER trigger's
WHEN condition does not return true, it is not necessary to queue an
event nor to re-fetch the row at end of statement. This can result in
significant speedups in statements that modify many rows, if the
trigger only needs to be fired for a few of the rows.