How can I set a username for a Postgresql audit trigger?

Question

I am using a trigger in PostgreSQL 8.2 to audit changes to a table:

CREATE OR REPLACE FUNCTION update_issue_history() RETURNS trigger as $trig$
BEGIN
        INSERT INTO issue_history (username, issueid)
               VALUES ('fixed-username', OLD.issueid);
        RETURN NULL;
END;
$trig$ LANGUAGE plpgsql;

CREATE TRIGGER update_issue_history_trigger
AFTER UPDATE ON issue
      FOR EACH ROW EXECUTE PROCEDURE update_issue_history();

What I want to do is have some way to provide the value of fixed-username at the time that I execute the update. Is this possible? If so, how do I accomplish it?

Answer 1

Try something like this:

CREATE OR REPLACE FUNCTION update_issue_history() 
RETURNS trigger as $trig$
DECLARE
      arg_username varchar;
BEGIN
      arg_username := TG_ARGV[0];
      INSERT INTO issue_history (username, issueid)
             VALUES (arg_username, OLD.issueid);
      RETURN NULL;
END;
$trig$ LANGUAGE plpgsql;

CREATE TRIGGER update_issue_history_trigger
AFTER UPDATE ON issue
      FOR EACH ROW EXECUTE PROCEDURE update_issue_history('my username value');