PostgreSQL - Python - Trigger - Access to current_user

Question

I am writing an audit trigger in plPython. I cannot figure out how to get access to the current_user variable. Is it available via plpy somehow?

I would also like to know if it is possible to pass variables into a trigger function? If so how is this done? I tried:

create trigger stock_sys_audit before insert or update or delete on stock_tbl
FOR EACH ROW EXECUTE PROCEDURE sys_audit(current_user);

but it does not work.

Thanks.

Answer 1

You have an access to all Postgres functions and settings only via SQL. Use plpy.execute(), example:

create or replace function py_current_user()
returns text language plpython3u
as $$
    res = plpy.execute("select current_user")
    return res[0]["current_user"]
$$;

select py_current_user();

 py_current_user 
-----------------
 postgres
(1 row)