I'm using PostgreSQL 9.1. My database is structured so that there is actual tables that my application uses. For every table there is history table that stores only change history. History tables contain same fields that actual tables plus fields form some extra information eg. edit time. History tables are only handled by triggers.

I have 2 kind of triggers:

  1. Before INSERT trigger to add some extra information to tables when they are created (eg. create_time).
  2. Before UPDATE trigger and before DELETE triggers to copy old values from actual table to history table.

Problem is that I'd like to use triggers to store also the id of user who made those changes. And by id I mean id from php application, not PostgreSQL user id.

Is there any reasonable way to do that?

With INSERT and UPDATE it could be possible to just add extra field for id to actual tables and pass user id to SQL as part of SQL query. As far as I know this doesn't work with DELETE.

All triggers are structured as follows:

CREATE OR REPLACE FUNCTION before_delete_customer() RETURNS trigger AS $BODY$
BEGIN
    INSERT INTO _customer (
        edited_by,
        edit_time,
        field1,
        field2,
        ...,
        fieldN
    ) VALUES (
        -1, // <- This should be user id.
        NOW(),
        OLD.field1,
        OLD.field2,
        ...,
        OLD.fieldN
    );
    RETURN OLD;
END; $BODY$
LANGUAGE plpgsql
Edit
Report