Accessing transaction context in a Postgres trigger

Question

I have a Postgres 11.3 database that has lots of triggers on it. These triggers are automatically generated through some metaprogramming via SQLAlchemy, and essentially wrap every CREATE/UPDATE/DELETE action on every record, storing a "version" of it. This process is entirely automatic and requires no effort from the end user.

I'd like to be able to add a user_id foreign key to my versions table. This is user_id as in application user, not the connected postgres/system within which the transaction is executing. This field will need to be provided by the application calling method.

My initial attempt has involved automating out a set of stored procedures/postgres functions for each table. This works, but I can't figure out a way to access the context from the current transaction. Here is some psuedo code to demonstrate:

CREATE OR REPLACE FUNCTION delete_address(resource_id INTEGER, user_id INTEGER)
    RETURNS INTEGER as
    $BODY$
        DECLARE
            context USER_ID INTEGER; // something like this magical context keyword
        BEGIN
            USER_ID = user_id;

            DELETE FROM address WHERE id = resource_id;

            RETURN 1;
        END;
    $BODY$
    LANGUAGE plpgsql;

In the DELETE trigger for this table:

CREATE OR REPLACE FUNCTION update_address_history_on_delete()
    RETURNS trigger AS
    $BODY$
    BEGIN
        UPDATE "versions"
        SET 
          date_archived = now()::timestamp,
          user_id = context USER_ID  // theres that magical context keyword again
        WHERE
            resource_id = OLD.id;
        RETURN OLD;
    END
    $BODY$
    LANGUAGE plpgsql

You will see in this hypothetical I'm trying to access what would essentially be a lexically scoped/closure value, sitting in the context of a single transaction.

Is this possible, or is there another way I can go about something like this?

Cheers!

Answer 1

You can do this with a transaction-local custom configuration variable.

Assign the variable in your delete_address() function with:

PERFORM set_config('my_vars.user_id', user_id, true);

...and retrieve the value in your trigger using:

current_setting('my_vars.user_id')