title: Dynamic column name postgresql trigger
body: <p>I'm trying to create a dynamic audit trigger that reads a column_name from the information schema and inserts into an audit table which column has been changed. So far I have the following code:</p>
  <pre><code>     CREATE OR REPLACE FUNCTION dynamic_column_audit()
          RETURNS trigger
          LANGUAGE 'plpgsql'
          COST 100
          VOLATILE NOT LEAKPROOF AS $BODY$
          &lt;&lt;main_function&gt;&gt;
      
      declare
      table_name_tr text;
      table_schema_tr text;
      
      column_name_trigger text;
      old_column_name_trigger record;
      new_column_name_trigger record;
      begin
      -- variables
      table_name_tr := TG_TABLE_NAME;
      table_schema_tr := TG_TABLE_SCHEMA;
      
      &lt;&lt;loop_sql&gt;&gt;
          FOR column_name_trigger IN (SELECT c.column_name FROM information_schema.columns c WHERE c.table_schema = table_schema_tr and c.table_name = table_name_tr ORDER BY ordinal_position asc)
              LOOP
              
              RAISE INFO E'\n Column name: %, table_name: %', column_name_trigger , table_schema_tr||'.'||table_name_tr;
              IF (TG_OP = 'UPDATE') THEN
              
              INSERT INTO my_loggingtable (operation, table_schema, table_name, column_name, old_value, new_value) 
              VALUES (TG_OP, table_name_tr, table_schema_tr, column_name_trigger, OLD.||column_name_trigger, NEW.||column_name_trigger);
  end if;
      END LOOP loop_sql;
      return new;
      end main_function;
      $BODY$;
  </code></pre>
  <p>I'm trying to see whether it's possible to do something like NEW. + column_name or OLD&gt; + column_name from the loop rather than hardcode the column name value for each table.</p>
  <p>The idea behind this, which it could not be possible is to do an audit trigger for around 20 tables without adding the name all the time.</p>