title: PostgreSQL triggers and temporary table
body: <p>I have created an before update and after update trigger on a postgresql db table.</p>
  
  <p>There is a requirement to preserve historical record and at the same time create a new record for the said data. Old record is to be marked as archived.</p>
  
  <p>I was planning on using temporary table to keep track of the NEW values and reset the NEW values such that it is marked as archived.</p>
  
  <p>In my after update trigger I would read the data from the temporary table, and create a brand new active record.</p>
  
  <p>My problem is temporary table created in before update trigger is not visible to after update trigger. Moreover I cannot even pass on any argument (of type record) to the after update trigger as it is not allowed.</p>
  
  <p>I have already achieved the desired result in Oracle db, using Global Temporary table, but having a tough time in PostgreSQL.</p>
  
  <p>Here is the sample code for before update trigger function:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION trigger_fct_trig_trk_beforeupdate()
  RETURNS trigger AS
  $BODY$
  DECLARE
  
      some variable declarations;
      BEGIN
  
      Drop table  IF EXISTS track_tmp_test;
  
      CREATE TEMPORARY TABLE track_tmp_test(
      ...
      );
  
      Insert into track_tmp_test (........)
      values(NEW., NEW..., NEW.., NEW...);
  
      NEW... := OLD...;
      NEW... := OLD.... ;
      NEW... := OLD...;
      Mark the NEW.status : = 'archived';
  
  RETURN NEW;
  END
  $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
  
  CREATE TRIGGER trig_trk_test_beforeupdate
  BEFORE UPDATE ON test
  FOR EACH ROW EXECUTE PROCEDURE trigger_fct_trig_trk_beforeupdate() ;
  </code></pre>
  
  <p>NOW the after UPDATE trigger function:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION trigger_fct_trg_trk_afterupdate()
    RETURNS trigger AS
  $BODY$
  DECLARE
  
      some variables;
  
  -- insert into  original table the data from temporary that was inserted in before update trigger 
      INSERT into TEST (....)
      select ....
      from track_tmp_test ;
  
      -- delete data from temporary table after insert
      delete from track_tmp_test ;
  
   EXCEPTION
       WHEN OTHERS THEN
         -- Consider logging the error and then re-raise
         RAISE;
  RETURN NEW;
  END
  $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
  </code></pre>
  
  <p>Is there a way that after update trigger can access the temporary table created in before update trigger function?</p>
  
  <p>I cannot have a permanent table hold he values, as trigger can be fired by many users updating the data in the table.</p>