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
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
