postgresql - 更新表后插入数据，如数组

Question

我有两个表

Table A
col1        col2         col3

Table B  
table_name        column_name        new_value     old_value

如果 表 A 发生任何更新，它将在 表 B 上插入数据

表B的输出为==>

    table_name         column_name         new_value             old_value
 ----------------     ------------------  --------------        -----------
      A                  {col1}   
      A                  {col1,col2}        {col1.new_value,      {col1.old_value,
                                             col2.new_value}       col2.old_value}, 

所以任何人都可以告诉我如何捕获 column_names 并将数据目标表存储为数组

Answer 1

试试这个

使用触发函数

    CREATE OR REPLACE FUNCTION update_history()
      RETURNS trigger AS
    $BODY$ 
     DECLARE col_name VARCHAR[]; 
     DECLARE od_value VARCHAR[]; 
     DECLARE ne_value VARCHAR[]; 
     DECLARE each_column RECORD; 
     DECLARE each_entity RECORD; 
     DECLARE column_name VARCHAR; 
     DECLARE old_value VARCHAR; 
     DECLARE new_value VARCHAR; 

     FOR each_column IN 
          select c.column_name  --- Get the all column names in affected table 
          from information_schema.columns c 
       where(table_name = tg_relname And c.TABLE_SCHEMA = TG_TABLE_SCHEMA)
           LOOP 
           FOR each_entity IN --- Its used to get old and new columns value 
       EXECUTE 'SELECT text((' || quote_literal(OLD.*) || '::"' || tg_table_schema || '"."' || tg_relname || '")."'  || each_column.column_name || '") as old_val, 
          text((' || quote_literal(NEW.*) || '::"' || tg_table_schema || '"."' || tg_relname || '")."'  || each_column.column_name || '") 
         AS new_val  
          FROM "' || tg_table_schema || '"."' || tg_relname || '";' 
          LOOP 
             old_value = each_entity.old_val; 
             new_value = each_entity.new_val; 
              IF old_value != new_value THEN 
              i=i+1; 
              col_name[i]=each_column.column_name; 
              od_value[i]=old_value; 
              ne_value[i]=new_value; 
              END IF; 
          END LOOP; 
          END LOOP; 

 INSERT INTO B  
            (   

              tablename,  
             columnnames,  
            oldvalues, 
 newvalues 
           )   
 VALUES 
         (   

            tg_relname,  
            col_name, 
             od_value, 
  ne_value
             ); 
        End if; 
      RETURN NEW; 
  END; 
  $BODY$
  LANGUAGE plpgsql VOLATILE