What's the PostgreSQL equivalent of SQL Servfer's CONTEXT_INFO?

Question

In relation to my other question "What’s the best way to audit log DELETEs?". What's the PostgreSQL equivalent of CONTEXT_INFO?

I want to log deletes using a trigger, but since I'm not using the database user as my app's logical user, I cannot log the CURRENT_USER from the trigger code as the user who deleted the record. But for INSERT and UPDATE it is possible to log the record changes from trigger since you can just add a user field in the record, say inserted_by and last_updated_by, and use these fields to log to audit table.

Please remember that not everybody knows MSSQL, so saying "what's the equivalent of XXX" is not really well stated question - explain what CONTEXT_INFO is, and what it is used for. Then - somebody with Pg skills can check how to get given functionality. — , Mar 30 '09 at 17:58

score 2 · Accepted Answer · edited Sep 15 '13 at 06:00

2

http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks_II#Any_other_session_variables

PostgreSQL provides more variants, please, look to this site.

edited Sep 15 '13 at 06:00

Pavel Stehule

42,331
5
91
94

answered Mar 30 '09 at 08:58

Milen A. Radev

60,241
22
105
110

2

Link-only answers are bad. What if that site goes offline? – melpomene Jun 24 '19 at 06:43

score 1 · Answer 2 · answered May 15 '20 at 04:54

The accepted answer is outdated.

In recent versions of postgresql (since 9.3 or 9.4 I think), one can set / retrieve config variables are only alive for the current session [or transaction]. Documentation Reference

example of setting a session variable:

hal=# select set_config('myvar.foo', 'bar', false);
 set_config 
------------
 bar
(1 row)

hal=# select current_setting('myvar.foo');
 current_setting 
-----------------
 bar
(1 row)

It is possible to use the function current_setting as one would use other set returning functions.

example:

create table customers (id int);
insert into customers values (1), (2), (3), (4);

select c.*, s.*
from customers c
left join current_setting('myvar.foo') s
       on c.id = length(s)

What's the PostgreSQL equivalent of SQL Servfer's CONTEXT_INFO?

2 Answers2

Linked

Related