4

The PostgreSQL documentation lists a number of System Information Functions which provide state from the current connection, such as inet_client_addr() or pg_backend_pid(). I would like to create an audit trail for updates to a table which includes the application_name, but there does not seem to be a function for accessing this.

The application_name is visible from the pg_stat_activity view and is set by clients in the format of

username:progname@hostname

How can this information be accessed from a trigger?

  • 1
    Does selecting from the pg_stat_activity view not work? You can filter based on pid to select pid of current query, I think – JoshRagem Aug 29 '16 at 19:17
8

You can use the function current_setting()[1], eg:

sebastian=# select current_setting('application_name');
 current_setting
-----------------
 psql
(1 row)

Testing a new value:

sebastian=# set application_name to db_overflow;
SET
sebastian=# select current_setting('application_name');
 current_setting
-----------------
 db_overflow
(1 row)

References:

  1. https://www.postgresql.org/docs/current/static/functions-admin.html
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.