Create/update trigger auto-populate fields.

Hey guys,

To start, I need to mention that I did read chapter 39-3 on triggers and do understand how they work, in the current (9.0) manuals (OK, maybe the manuals are RC1, but the content is the same as in 8.4... :).

I have a simple table, person:

pe_id | bigint | not null default nextval(('"pe_seq"'::text)::regclass)
fname | character varying(100) | not null
minit | character varying(3) |
lname | character varying(100) | not null
us_id | bigint | default 0
cre_ts | timestamp with time zone | not null default now()

When a record gets inserted/updated, I want to auto-update the us_id and cre_ts fields.

In my trigger function, I do:

CREATE or REPLACE FUNCTION person_stamp() RETURNS trigger
AS $person_stamp$
BEGIN
-- Remember who changed the record and when

NEW.cre_ts := current_timestamp;
NEW.us_id := TG_ARG[0];
RETURN NEW;
END;
$person_stamp$ LANGUAGE plpgsql;

My real DB user(s) (webserver, one dbuser for everybody) makes the DB connection. Application users are not DB users as they are not created with 'createuser'. They are application users that have an entry in an application table. That is the user ID, that I need to use to auto-populate the us_id field, current_user will give me values like 'root', 'webserver' or 'postgres' or such, that is not what I need.

In the trigger definition, I can hard code something like
CREATE TRIGGER person_stamp BEFORE INSERT OR UPDATE ON person
FOR EACH ROW EXECUTE PROCEDURE person_stamp( 1 );

and use TG_ARGV[0] to assign that value to NEW.us_id, but that always assigns us_id = 1;, which is not what I want either.

How to I make the trigger pass the us_id to the person_stamp function, where us_id can be any value in appuser.us_id, other than insert into person (..., us_id) values (..., MyAppUserId ) or update person set ..., us_id = MyAppUserId ...?

Thanks,
Chris