是否可以动态循环遍历表的列? [英] Is it possible to dynamically loop through a table's columns?
问题描述
我有一个表测试的触发器函数,它具有以下代码片段:
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.
怎么做?
推荐答案
看一下information_schema,有一个视图columns".执行查询以从触发触发器的表中获取所有当前列名:
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;
遍历结果即可!
更多信息可以在详细手册中找到.
More information can be found in the fine manual.
这篇关于是否可以动态循环遍历表的列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!