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

I don't understand what you are trying to do? You don't need an update trigger for the UPDATE itself to work. If you want to change the value of some columns, simply assign them the value: e.g. new.date_col := current_date, not update clause needed — a_horse_with_no_name
Great question, though it's always a good idea to mention your PostgreSQL version. (select version()). Good to see proper use of identifier quoting for once. You may find using the regclass pseudo-type more convenient than separate schema and table names, though. — Craig Ringer
@CraigRinger I am using Postgresql 9.2. And thanks for the tip about regclass. — Richard

Pavel Stehule Pavel Stehule · Accepted Answer · 2013-05-12T18:32:02

I am not sure, but your triggers can do recursion calls - it does UPDATE same table from UPDATE trigger. This is usually bad practice, and usually is not good idea to write too generic triggers. But I don't know what you are doing, maybe you need it, but you have to be sure, so you are protected against recursion.

For debugging of triggers is good push to start and to end of function body debug messages. Probably you use GET DIAGNOSTICS statement after EXECUTE statement for information about impact of dynamic SQL

DECLARE 
  _updated_rows int;
  _query text;     
BEGIN
  RAISE NOTICE 'Start trigger function xxx';
  ...
  _query := format('UPDATE ....);
  RAISE NOTICE 'dynamic sql %, %', _query, new;
  EXECUTE _query USING new;
  GET DIAGNOSICS _updated_rows = ROW_COUNT;
  RAISE NOTICE 'Updated rows %', _updated_rows;
  ...

a postgres update trigger performs everything else except the actual update

EDIT

2 Answers