Re: audit information

On Mon, 25 Apr 2005 11:57:08 -0400, Tom Lane wrote
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > On Mon, Apr 25, 2005 at 10:41:09AM -0400, Keith Worthington wrote:
> >> Is there a way to obtain the postgres user id or must I
> >> store the output of session_user in the tables?
>
> > You could grab usesysid from pg_user:
> > SELECT usesysid FROM pg_user WHERE usename = session_user;
> > You can convert the user ID back to the name with
> > pg_get_userbyid(): SELECT pg_get_userbyid(1);
>
> My suggestion would be to store the user name as text.
> Depending on numeric user IDs to remain stable across the
> long haul (dump/reload, version updates, etc) doesn't seem
> like a good idea --- it's pretty much exactly the same
> mistake as relying on OIDs as primary keys in user tables.
>
> It's fairly likely that in 8.1 or so we will actually
> abandon the notion of "usesysid" per se --- the pg_roles
> project will probably use OIDs to identify roles, which
> will mean you can't forcibly assign a particular numeric
> ID to a particular user. If that happens then storing
> numeric IDs *will* break when you reload the data into 8.1.
>
> If you're really desperate for space you could keep your
> own auxiliary table to associate user names with integer
> keys, but I doubt the space savings would amount to all
> that much ...
>
> regards, tom lane

I have created the following function to create the necessary data for my
audit trail. Unfortunately I do not know how I can test it without putting
the whole modification in place. (This is a small part of a much bigger project.)

Can anyone suggest how to test this functionality without implementing it as a
trigger on a table?

Can this function be implemented as STABLE STRICT?

CREATE OR REPLACE FUNCTION interface.tf_audit_data()
RETURNS "trigger" AS
$BODY$
BEGIN
-- Set the user name.
IF NEW.user_name IS NULL THEN
NEW.user_name := SELECT * FROM session_user;
END IF;
-- Set the timestamp.
IF NEW.review_timestamp IS NULL THEN
NEW.review_timestamp := ('now'::text)::timestamp(6) with time zone;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Kind Regards,
Keith