Make trigger behavior depend on query

Question

My goal is to make trigger behavior to depend on some client identifier.

For example I execute a query

begin;
<specify-some-client-identifier>
insert into some_table
values('value')
commit;

And I have trigger function executing before insert:

NEW.some_filed := some_func(<some-client-identifier-spicified-above>)

So, how do I <specify-some-client-identifier> and get <some-client-identifier-spicified-above>?

client-identifier?? by this variable are you planning to process for differently for eack client (sessions) or this you want to add for different - different procedure where this table is getting updated or inserted data? — pratik garg, Jun 28 '13 at 08:55
@pratik, I need to differentiate sessions. Query is generated by the client. — Lol4t0, Jun 28 '13 at 09:01
In future please always specify your PostgreSQL version (`SELECT version()`) and try to provide some more info about what exactly you want. In this case "client" and "identifier" aren't what you'd call super-informative. Username? source IP? Some kind of cookie code? — Craig Ringer, Jun 28 '13 at 10:10

score 2 · Accepted Answer · answered Jun 28 '13 at 08:55

You basically need some kind of variables in SQL. It is possible to do it, with multiple ways:

using GUCs
using table with variables
using temp table with variables
using %_SHARED in plperl functions

All this is possible. If you're interested in implementation details and/or comparison - check this blogpost - just in case it wasn't obvious from domain - it's my blog.

score 2 · Answer 2 · edited May 23 '17 at 10:25

You will find this prior answer informative. There I explain how to pass an application-defined username through so it is visible to PostgreSQL functions and triggers.

You can also use the application_name GUC, which can be set by most client drivers or explicitly by the application. Depending on your purposes this may be sufficient.

Finally, you can examine pg_stat_activity to get info about the current client by looking it up by pg_backend_pid(). This will give you a client IP and port if TCP/IP is being used.

Of course, there's also current_user if you log in as particular users at the database level.

As usual, @depesz points out useful options I hadn't thought of, too - using shared context within PL/Perl, in particular. You can do the same thing in PL/Python. In both cases you'll pay the startup overhead of a full procedural language interpreter and the function call costs of accessing it, so it probably only makes sense to do this if you're already using PL/Perl or PL/Python.

Make trigger behavior depend on query

2 Answers2