a postgres update trigger performs everything else except the actual update

Question

Let's use a test table :

CREATE TABLE labs.date_test
(
  pkey int NOT NULL,
  val integer,
  date timestamp without time zone,
  CONSTRAINT date_test_pkey PRIMARY KEY (pkey)
);

I have a trigger function defined as below. It is a function to insert a date into a specified column in the table. Its arguments are the primary key, the name of the date field, and the date to be inserted:

CREATE OR REPLACE FUNCTION tf_set_date()
  RETURNS trigger AS
$BODY$
DECLARE
    table_name text;
    pkey_col text := TG_ARGV[0];
    date_col text := TG_ARGV[1];
    date_val text := TG_ARGV[2];
BEGIN
    table_name := format('%I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME);
    IF TG_NARGS != 3 THEN
        RAISE 'Wrong number of args for tf_set_date()'
        USING HINT='Check triggers for table ' || table_name;
    END IF;
    EXECUTE format('UPDATE %s SET %I = %s' ||
            ' WHERE %I = ($1::text::%s).%I', 
            table_name, date_col, date_val,
            pkey_col, table_name, pkey_col )
    USING NEW;
    RAISE NOTICE '%', NEW;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

The actual trigger definition is as follows:

CREATE TRIGGER t_set_ready_date
  BEFORE UPDATE OF val
  ON labs.date_test
  FOR EACH ROW
  EXECUTE PROCEDURE tf_set_date('pkey', 'date', 'localtimestamp(0)');

Now say I do: INSERT INTO TABLEdate_test(pkey) values(1);`

Then I perform an update as follows:

UPDATE labs.date_test SET val = 1 WHERE pkey = 1;

Now the date gets inserted as expected. But the val field is still NULL. It does not have 1 as one would expect (or rather as I expected).

What am I doing wrong? The RAISE NOTICE in the trigger shows that NEW is still what I expect it to be. Aren't UPDATEs allowed in BEFORE UPDATE triggers? One comment about postgres triggers seems to indicate that original the UPDATE gets overwritten if there is an UPDATE statement in a BEFORE UPDATE trigger. Can someone help me out?

EDIT

I am trying to update the same table that invoked the trigger, and that too the same row which is to be modified by the UPDATE statement that invoked the trigger. I am running Postgresql 9.2

Answer 1

Given all the dynamic table names it isn't entirely clear if this trigger issues an update on the same table that invoked the trigger.

If so: That won't work. You can't UPDATE some_table in a BEFORE trigger on some_table. Or, more strictly, you can, but if you update any row that is affected by the statement that's invoking the trigger results will be unpredictable so it isn't generally a good idea.

Instead, alter the values in NEW directly. You can't do this with dynamic column names, unfortunately; you'll just have to customise the trigger or use an AFTER trigger to do the update after the rows have already been changed.