PostgreSQL – Insert RECORD into a Table in a Trigger Function

Unfortunately, Dynamic SQL is not permitted in Triggers. Notwithstanding, what you are asking for is still possible, but you will have to apply some elbow grease.

I wrote an answer to the post Disable trigger for just one table. What I did was create a user variable @TRIGGER_DISABLED as a flag to control if the code within the trigger was to be executed.

In your particular case, you will need 9 flags. You will also need a BEFORE UPDATE trigger:

DELIMITER $$
CREATE TRIGGER `setup_record_time` BEFORE UPDATE ON `my_table`
FOR EACH ROW 
BEGIN
  SET @rec_col1 = IF(NEW.col1=OLD.col1,0,1);
  SET @rec_col2 = IF(NEW.col2=OLD.col2,0,1);
  SET @rec_col3 = IF(NEW.col3=OLD.col3,0,1);
  SET @rec_col4 = IF(NEW.col4=OLD.col4,0,1);
  SET @rec_col5 = IF(NEW.col5=OLD.col5,0,1);
  SET @rec_col6 = IF(NEW.col6=OLD.col6,0,1);
  SET @rec_col7 = IF(NEW.col7=OLD.col7,0,1);
  SET @rec_col8 = IF(NEW.col8=OLD.col8,0,1);
  SET @rec_col9 = IF(NEW.col9=OLD.col9,0,1);
END $$
DELIMITER $$

The AFTER UPDATE trigger looks like this:

DELIMITER $$
CREATE TRIGGER `record_time` AFTER UPDATE ON `my_table`
FOR EACH ROW 
BEGIN
  IF @rec_col1 = 1 THEN INSERT INTO `record_changes` (col1) values (CUR_DATE()); END IF;
  IF @rec_col2 = 1 THEN INSERT INTO `record_changes` (col2) values (CUR_DATE()); END IF;
  IF @rec_col3 = 1 THEN INSERT INTO `record_changes` (col3) values (CUR_DATE()); END IF;
  IF @rec_col4 = 1 THEN INSERT INTO `record_changes` (col4) values (CUR_DATE()); END IF;
  IF @rec_col5 = 1 THEN INSERT INTO `record_changes` (col5) values (CUR_DATE()); END IF;
  IF @rec_col6 = 1 THEN INSERT INTO `record_changes` (col6) values (CUR_DATE()); END IF;
  IF @rec_col7 = 1 THEN INSERT INTO `record_changes` (col7) values (CUR_DATE()); END IF;
  IF @rec_col8 = 1 THEN INSERT INTO `record_changes` (col8) values (CUR_DATE()); END IF;
  IF @rec_col9 = 1 THEN INSERT INTO `record_changes` (col9) values (CUR_DATE()); END IF;
END $$
DELIMITER $$

Just to be on the safe side, you should initialize these 9 user variables with 0.

SET @rec_col1 = 0;
SET @rec_col2 = 0;
SET @rec_col3 = 0;
SET @rec_col4 = 0;
SET @rec_col5 = 0;
SET @rec_col6 = 0;
SET @rec_col7 = 0;
SET @rec_col8 = 0;
SET @rec_col9 = 0;
UPDATE mytable ... ;

That way, your session dictates the trigger's behavior.

Give it a Try !!!

Try this form:

 EXECUTE 'insert into ' || child || ' values ($1.*)' USING NEW;

It requires at least PostgreSQL 8.4, but previous versions ought to be retired nowadays.

An even more modern and cleaner version (quote the table's name if necessary):

 EXECUTE format('insert into %I values ($1.*)', child) USING NEW;