Re: audit information

On Mon, 25 Apr 2005 11:01:51 -0400, Sean Davis wrote
> On Apr 25, 2005, at 10:41 AM, Keith Worthington wrote:
>
> > Hi All,
> >
> > I need to add an audit trail to my application. I thought that
> > I would add the user id and timestamp to the tables where I need
> > to track 'last modified by...'. Currently there is a table
> > tbl_employee that I would like to be able to cross reference to
> > in order to obtain the user's real name when necessary.
> >
> > MYDB=# \d tbl_employee
> > Table "tbl_employee"
> > Column | Type | Modifiers
> > ----------------+-----------------------+-----------
> > id | character varying(20) | not null
> > first_name | character varying(15) | not null
> > middle_initial | character(1) |
> > last_name | character varying(20) | not null
> > inactive | boolean | not null
> > email | character varying(64) |
> > Indexes:
> > "tbl_employee_pkey" PRIMARY KEY, btree (id)
> >
> > Is there a way to obtain the postgres user id or must I store
> > the output of session_user in the tables?
>
> Not sure on this one. What is the "id" in your table? Is it the
> postgres username?
>
> > Are there some existing functions and or tools for
> > implementing audit trails?
>
> Would a trigger on the table that you want to audit do the trick?
>
> Sean

The tbl_employee.id column contains data from the financal software.

Yes, I believe a BEFORE trigger would do the trick nicely. I can
simply insert the approriate data on the way by.

My question is do I have to use session_user which returns type name
or is there a function that returns the usesysid which is type int4?

Regardless of whether the data I use is int4 or name I need to add a
column to tbl_employee for cross referencing to the employee's real
name.

Kind Regards,
Keith