There are as many ways to audit table changes as there are programmers. This is an interesting methodology submitted by David Fetter that uses composite, or RECORDS, to store the before and after pictures of the base table. This method is not recommended when you are short on storage, but is very good for people intent on having all information available.

Suppose we have a small table people and an audit table people_audit. Whenever people is changed, the before and after records are stored in the people_audit table. The old and the new records for each change are to be stored as two single columns in that table. Click on the diagram to see the table definitions.

A trigger function and trigger is required to update the people_audit table for INSERT, UPDATE and DELETE of any records in the base table, people. This trigger function can work for all of these operations, but it must take into account that OLD is not available for INSERTs and NEW is not available for DELETES. Also, in order to continue the original INSERT, UPDATE or DELETE NEW must be returned for INSERT and UPDATE and OLD for DELETE.

   CREATE OR REPLACE FUNCTION people_audit_trig ()
   RETURNS TRIGGER
   STRICT
   LANGUAGE plpgsql
   AS $$
   DECLARE
       rows_affected INTEGER;
   BEGIN
       IF TG_OP = 'INSERT' THEN
           INSERT INTO people_audit (action, new_people)
              VALUES ('INSERT', NEW );
       ELSIF TG_OP = 'UPDATE' THEN
           INSERT INTO people_audit (action, old_people, new_people)
              VALUES ('UPDATE', OLD , NEW );
       ELSIF TG_OP = 'DELETE' THEN
           INSERT INTO people_audit (action, old_people)
              VALUES ('DELETE', OLD );
       ELSE
           RAISE EXCEPTION 'TG_OP % is none of INSERT, UPDATE or DELETE.', TG_OP;
       END IF;
   
       GET DIAGNOSTICS rows_affected = ROW_COUNT;
       IF rows_affected = 1 THEN
           IF TG_OP IN ('INSERT', 'UPDATE') THEN
               RETURN NEW;
           ELSE
               RETURN OLD;
           END IF;
       ELSE
           RAISE EXCEPTION 'INSERT failed on people_audit';
       END IF;
   END;
   $$;

And of course the trigger needs to be defined:

   CREATE TRIGGER people_audit_trig
       BEFORE INSERT OR UPDATE OR DELETE ON people
       FOR EACH ROW EXECUTE PROCEDURE people_audit_trig();

   db=# select * from people;
    person_id |  first_name   | middle_name | last_name
   -----------+---------------+-------------+-----------
            1 | First INSERT  |             |
            2 | Second INSERT |             |
            3 | Third INSERT  |             |
            4 | Fourth INSERT |             |
            5 | Fifth INSERT  |             |
   (5 rows)
   
   db=# select * from people_audit;
    actor | action | action_timestamp | old_people |      new_people
   -------+--------+------------------+------------+-----------------------
    [date reformatted for width]
    elein | INSERT | 17:27:35-08      |            | (1,"First INSERT",,)
    elein | INSERT | 17:27:35-08      |            | (2,"Second INSERT",,)
    elein | INSERT | 17:27:36-08      |            | (3,"Third INSERT",,)
    elein | INSERT | 17:27:36-08      |            | (4,"Fourth INSERT",,)
    elein | INSERT | 17:27:36-08      |            | (5,"Fifth INSERT",,)
   (5 rows)

   db=#UPDATE people SET first_name= first_name || ' updated once' WHERE person_id < 4;
   UPDATE 3
   db=#select * from people;
    person_id |         first_name         | middle_name | last_name
   -----------+----------------------------+-------------+-----------
            4 | Fourth INSERT              |             |
            5 | Fifth INSERT               |             |
            1 | First INSERT updated once  |             |
            2 | Second INSERT updated once |             |
            3 | Third INSERT updated once  |             |
   (5 rows)

   db=# select * from people_audit;
    actor | action | action_timestamp |      old_people       |             new_people
   -------+--------+------------------+-----------------------+------------------------------------
    [date reformatted for width]
    elein | INSERT | 17:27:35-08      |                       | (1,"First INSERT",,)
    elein | INSERT | 17:27:35-08      |                       | (2,"Second INSERT",,)
    elein | INSERT | 17:27:36-08      |                       | (3,"Third INSERT",,)
    elein | INSERT | 17:27:36-08      |                       | (4,"Fourth INSERT",,)
    elein | INSERT | 17:27:36-08      |                       | (5,"Fifth INSERT",,)
    elein | UPDATE | 17:29:55-08      | (1,"First INSERT",,)  | (1,"First INSERT updated once",,)
    elein | UPDATE | 17:29:55-08      | (2,"Second INSERT",,) | (2,"Second INSERT updated once",,)
    elein | UPDATE | 17:29:55-08      | (3,"Third INSERT",,)  | (3,"Third INSERT updated once",,)
   (8 rows)

For completion's sake, let's do a DELETE. Notice that the audit table only grows and that deleted rows do not have values in the new_people column.

   =# DELETE FROM people WHERE person_id > 3;
   DELETE 2
   db=# select * from people;
    person_id |         first_name         | middle_name | last_name
   -----------+----------------------------+-------------+-----------
            1 | First INSERT updated once  |             |
            2 | Second INSERT updated once |             |
            3 | Third INSERT updated once  |             |
   (3 rows)
   
   elein=# select * from people_audit;
    actor | action | action_timestamp |      old_people       |             new_people
   -------+--------+------------------+-----------------------+------------------------------------
    [ some rows not displayed ] [date reformatted for width]
    elein | UPDATE | 17:29:55-08      | (1,"First INSERT",,)  | (1,"First INSERT updated once",,)
    elein | UPDATE | 17:29:55-08      | (2,"Second INSERT",,) | (2,"Second INSERT updated once",,)
    elein | UPDATE | 17:29:55-08      | (3,"Third INSERT",,)  | (3,"Third INSERT updated once",,)
    elein | DELETE | 17:46:44-08      | (4,"Fourth INSERT",,) |
    elein | DELETE | 17:46:44-08      | (5,"Fifth INSERT",,)  |
   (10 rows)

Now what if you want to undo the last update. You could just write a simple update statement if you knew the details. But what if you wanted to undo all of the last updates for all of the rows. This is the query that will undo all of the last updates on each row in people. This only works for updates, not inserts and deletes but it does undo all of them so beware.

   update people set
      first_name=(pa.old_people).first_name,
      middle_name=(pa.old_people).middle_name,
      last_name=(pa.old_people).last_name
   from people_audit pa
   where person_id = (pa.old_people).person_id and
      pa.action_timestamp =
      (select max(action_timestamp)
         from people_audit a, people p
         where p.person_id = (a.old_people).person_id);
   UPDATE 3
   db=# select * from people;
    person_id |  first_name   | middle_name | last_name
   -----------+---------------+-------------+-----------
            1 | First INSERT  |             |
            2 | Second INSERT |             |
            3 | Third INSERT  |             |
   (3 rows)

   db=#  select * from people_audit;
    actor | action | action_timestamp |             old_people             |             new_people 
   -------+--------+------------------+------------------------------------+------------------------------------
    [ some rows not displayed, date reformatted for width ]
    elein | INSERT | 17:27:36-08      |                                    | (5,"Fifth INSERT",,)
    elein | UPDATE | 17:29:55-08      | (1,"First INSERT",,)               | (1,"First INSERT updated once",,)
    elein | UPDATE | 17:29:55-08      | (2,"Second INSERT",,)              | (2,"Second INSERT updated once",,)
    elein | UPDATE | 17:29:55-08      | (3,"Third INSERT",,)               | (3,"Third INSERT updated once",,)
    elein | UPDATE | 17:48:55-08      | (1,"First INSERT updated once",,)  | (1,"First INSERT",,)
    elein | UPDATE | 17:48:55-08      | (2,"Second INSERT updated once",,) | (2,"Second INSERT",,)
    elein | UPDATE | 17:48:55-08      | (3,"Third INSERT updated once",,)  | (3,"Third INSERT",,)
   (13 rows)

If you know the key of the row for you want to undo an UPDATE, you could take the above query and put it into a function which takes the key as a parameter. Then you could undo the last change for a certain row without knowing exactly what has changed. This is the function. To undo the update to the first row call it like this: select undo_people_update(1);

   CREATE OR REPLACE FUNCTION undo_people_update(integer)
   RETURNS integer AS
   $$
   DECLARE
      rows_affected integer;
   BEGIN
      update people set
         first_name=(pa.old_people).first_name,
         middle_name=(pa.old_people).middle_name,
         last_name=(pa.old_people).last_name
      from people_audit pa
      where person_id = $1 and
         person_id = (pa.old_people).person_id and
         pa.action_timestamp =
         (select max(action_timestamp)
            from people_audit a, people p
            where (a.old_people).person_id = $1);
      GET DIAGNOSTICS rows_affected = ROW_COUNT;
      RETURN rows_affected;
   END;
   $$ language 'plpgsql';

Notice that to access the individual columns of a ROW type in a column in a table, use parentheses to identify the table and column, for example: (people_audit.old_people).first_name gets the first name field in the column old_people in the table people_audit.

Contributors: david at fetter.org, elein at varlena.com

Simon Riggs' Talk on PostgreSQL 8.0 Introducing PostgreSQL 8.0, A Talk 17-Mar-2005

Simon Riggs from 2ndquadrant.com has a good solid presentation on the 8.0 features and PostgreSQL. In this presentation he answers these questions about PostgreSQL:

What is it?
What's Familiar?
What's Cool?
Who's using it?
What's New in 8.0?
What's the Hype?
What Matters?
   Answering this with: Performance, Risk, Cost, and Community
And finally, What's in 8.1?

The PDF of Simon's Presentation is here: Introducing PostgreSQL 8.0.

Contributors: simon at 2ndquadrant.com

Converting HEX to DECIMAL and back [GENERAL] Help with converting hexadecimal to decimal 31-Mar-2005

To convert a decimal integer to hex you can use the to_hex() function.

   =# select to_hex(11);
    to_hex
   --------
    b
   (1 row)

Converting from a hex number back to decimal is a matter of using the proper cast. If you have the number represented as hex, x'ab' for example, you can simply cast it an an integer.

   =# select x'ab'::integer;
    int4
   ------
     171
   (1 row)

But if you have the hex number represented just as a string, you will need to construct the hex string. It is easier to do this in a function. Note that this function uses a loop to bypass the problem where SELECT INTO is not currently supported within EXECUTE.

   CREATE FUNCTION hex2dec(t text) RETURNS integer AS 
   $$
     DECLARE
       r RECORD;
     BEGIN
       FOR r IN EXECUTE 'SELECT x'''||t||'''::integer AS hex' LOOP
         RETURN r.hex;
       END LOOP;
     END
   $$ LANGUAGE plpgsql IMMUTABLE STRICT;

   =# select hex2dec('ac');
    hex2dec
   ---------
        172
   (1 row)

Contributors: Chandra Sekhar Surapaneni chandu at positivenetworks.net, Bruce Momjian pgman at candle.pha.pa.us, Doug Quale quale1 at charter.net, Dawid Kuroczko qnex42 at gmail.com