title: a postgres update trigger performs everything else except the actual update
body: <p>Let's use a test table :</p>
  
  <pre><code>CREATE TABLE labs.date_test
  (
    pkey int NOT NULL,
    val integer,
    date timestamp without time zone,
    CONSTRAINT date_test_pkey PRIMARY KEY (pkey)
  );
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The actual trigger definition is as follows:</p>
  
  <pre><code>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)');
  </code></pre>
  
  <p>Now say I do: <code>INSERT INTO TABLE</code>date_test(pkey) values(1);`</p>
  
  <p>Then I perform an update as follows:</p>
  
  <pre><code>UPDATE labs.date_test SET val = 1 WHERE pkey = 1;
  </code></pre>
  
  <p>Now the date gets inserted as expected. But the <code>val</code> field is still <code>NULL</code>. It does not have <code>1</code> as one would expect (or rather as I expected).</p>
  
  <p>What am I doing wrong? The RAISE NOTICE in the trigger shows that NEW is still what I expect it to be. Aren't <code>UPDATE</code>s allowed in <code>BEFORE UPDATE</code> triggers? One <a href="https://stackoverflow.com/questions/15340990/postgres-update-statement-as-a-trigger#comment21678390_15342749">comment</a> about <a href="https://stackoverflow.com/questions/15340990/postgres-update-statement-as-a-trigger">postgres triggers</a> 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?</p>
  
  <h2>EDIT</h2>
  
  <p>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</p>