From: Chris Ruprecht <chris@ruprecht.org>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Subject: Create/update trigger auto-populate fields.
Date: Thu, 16 Dec 2010 15:42:37 -0500
Message-Id: <0E78342B-BB79-4DB4-B7CA-004911A95F8D@ruprecht.org>
To: pgsql-sql@postgresql.org
Mime-Version: 1.0 (Apple Message framework v1082)

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)     |=20
 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=20
AS $person_stamp$=20
BEGIN
    -- Remember who changed the record and when=20

    NEW.cre_ts :=3D current_timestamp;=20
    NEW.us_id  :=3D TG_ARG[0];=20
    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 =3D 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 =3D MyAppUserId ...?

Thanks,
Chris