From: Chris Ruprecht <chris@ruprecht.org>
To: pgsql-sql@postgresql.org
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> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: chris@ruprecht.org
Subject: Re: Create/update trigger auto-populate fields.
In-Reply-To: <0E78342B-BB79-4DB4-B7CA-004911A95F8D@ruprecht.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox