Re: audit information - Mailing list pgsql-novice

On Mon, 25 Apr 2005 11:57:08 -0400, Tom Lane wrote
> Michael Fuhr <mike@fuhr.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