Forum OpenACS Q&A: Converting Oracle to PostgreSQL--"old" keyword

In Oracle, the old value of a table is stored in "old".  This is used
in Karl Goldstein's data model in some triggers he's written.  An

create or replace trigger ad_content_revisions_tr
begin update on ad_content_local
for each row
insert into ad_content_revisions (
  ) values (

The colon before old.content_id is a preprocessor that Oracle uses to
look up the value of old.  Old is stored from a previous buffer.

The question is, does anyone know of a similar construct I can apply
to PostgreSQL?  If I understand the whole system correctly, we're
enforing audit table integrity with this trigger.  So, the tradeoff
would be that we'd have no real audit capabilities without thinking of
something that works in PostgreSQL.

Posted by Ben Adida on
You should check out the OpenACS data model code, and the
Postgres documentation site for PLPGSQL. All triggers are
easily implemented in PLPGSQL, with a function and a trigger
that calls the function.
Posted by Don Baccus on
To expand on Ben's answer, it works conceptually exactly the same way in PL/pgSQL.  The only difference is that the syntax is a bit different - when the trigger calls your PL/pgSQL function it has access to both the new and old row being affected by the DML statement.  Just like in Oracle.

Check portal_tables_audit_update in portals.sql and the trigger definition that calls it for an example of how we're maintaining audit  tables.

The major difference is that the trigger definition must call a separately defined function, as there's no notion of an inline PL/pgSQL block.  In Oracle you can define the relevant code right in the trigger.  Slightly clumsy, I guess, but it means you can write the  trigger in assembly or C if you don't like PL/pgSQL :)