是否可以动态遍历表的列? [英] 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;

浏览结果，然后就可以了！

Loop through the result and there you go!

更多信息，请参见高级手册.

这篇关于是否可以动态遍历表的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！