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 nullminit | character varying(3) | lname | character varying(100) | not nullus_id
|bigint | default 0cre_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
theyare not created with 'createuser'. They are application users that have an entry in an application table. That is
theuser 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,
otherthan insert into person (..., us_id) values (..., MyAppUserId ) or update person set ..., us_id = MyAppUserId ...?
Thanks,
Chris
