postgresql 是否可以动态循环遍历表的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3083749/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to dynamically loop through a table's columns?
提问by MD Sayem Ahmed
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?
怎么做到呢？
回答by Frank Heikens
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:
看一下information_schema，有一个视图“列”。执行查询以从触发触发器的表中获取所有当前列名：
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.
更多信息可以在精细手册中找到。
回答by Stephen Denne
From 9.0 beta2 documentation about WHENclause in triggers, which might be able to be used in earlier versions within the trigger body:
来自关于WHEN触发器中子句的9.0 beta2 文档，它可能能够在触发器主体中的早期版本中使用：
OLD.* IS DISTINCT FROM NEW.*
OLD.* IS DISTINCT FROM NEW.*
or possibly (from 8.2 release notes)
或者可能（来自 8.2 发行说明）
IF row(new.*) IS DISTINCT FROM row(old.*)
IF row(new.*) IS DISTINCT FROM row(old.*)
回答by bartolo-otrit
回答by bartolo-otrit
Use pl/perl or pl/python. They are much better suited for such tasks. muchbetter.
使用 pl/perl 或 pl/python。它们更适合于此类任务。多好。
You can also install hstore-new, and use it's row->hstore semantics, but that's definitely not a good idea when using normal datatypes.
您也可以安装hstore-new，并使用它的 row->hstore 语义，但这在使用普通数据类型时绝对不是一个好主意。
回答by Erwin Brandstetter
In Postgres 9.0 or later add a WHENclauseto your trigger definition (CREATE TRIGGERstatement):
在 Postgres 9.0 或更高版本中，在触发器定义（语句）中添加一个WHEN子句CREATE TRIGGER：
CREATE TRIGGER foo
BEFORE UPDATE
FOR EACH ROW
WHEN (OLD IS DISTINCT FROM NEW) -- parentheses required!
EXECUTE PROCEDURE ...;
Only possible for triggers BEFORE/ AFTERUPDATE, where both OLDand NEWare defined. You'd get an exception trying to use this WHENclause with INSERTor DELETEtriggers.
仅适用于触发器BEFORE/ AFTERUPDATE，其中OLD和NEW都已定义。尝试将此WHEN子句与INSERTorDELETE触发器一起使用时会出现异常。
And radically simplify the trigger functionaccordingly:
并相应地从根本上简化触发功能：
...
IF OLD.locked > 0 THEN
RAISE EXCEPTION 'Message';
END IF;
...
No need to test IF TG_OP='UPDATE' ...since this trigger only works for UPDATEanyway.
无需测试，IF TG_OP='UPDATE' ...因为此触发器仅适用于UPDATE任何情况。
Or move that condition in the WHEN clause, too:
或者也在 WHEN 子句中移动该条件：
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 EXCEPTIONin your trigger function, which is only called when needed to begin with.
RAISE EXCEPTION在您的触发器函数中只留下一个无条件的，它只在需要开始时被调用。
In a
BEFOREtrigger, theWHENcondition is evaluated just before the function is or would be executed, so usingWHENis not materially different from testing the same condition at the beginning of the trigger function. Note in particular that theNEWrow seen by the condition is the current value, as possibly modified by earlier triggers. Also, aBEFOREtrigger'sWHENcondition is not allowed to examine the system columns of theNEWrow (such asoid), because those won't have been set yet.In an
AFTERtrigger, theWHENcondition 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 anAFTERtrigger'sWHENcondition 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.
在
BEFORE触发器中，WHEN条件在函数被执行或将被执行之前被评估，因此使用WHEN与在触发器函数开始时测试相同的条件没有实质性的不同。请特别注意，NEW条件看到的行是当前值，可能会被较早的触发器修改。此外，不允许BEFORE触发器的WHEN条件检查行的系统列NEW（例如oid），因为尚未设置这些列。在
AFTER触发器中，WHEN在行更新发生后立即评估条件，并确定事件是否排队以在语句结束时触发触发器。因此，当AFTER触发器的WHEN条件未返回 true 时，无需将事件排队，也无需在语句结束时重新获取行。如果只需要为少数行触发触发器，这可能会显着提高修改多行的语句的速度。
Related:
有关的：
To also address the question title
还要解决问题标题
Is it possible to dynamically loop through a table's columns?
是否可以动态循环遍历表的列？
Yes. Examples:
是的。例子：
