I want my client app to communicate some information to the Postgres server.
My app logs into Postgres with its own user-name, rather than the human user's name. I have a trigger to record row changes to a history (audit trail) table, and I want to include the human user's name. How can the app (client connection) communicate such info to the Postgres server?
I know we can use the "SET" and "SHOW" commands to set a few pre-determined variables such as "application_name". Is there some way to save to my own defined variables on the server?
Similarly, I'd like to include some text from the server about the context of what the user is doing at the moment, to be used in that same history logging mentioned above.
If there is no way to define my own variables on the server, I thought of two workarounds:
(a) Append the user name to the "application_name" variable. For example: "Acme App•Wendy Melvoin". Then have trigger function parse the "application_name" to retrieve the user name, "Wendy Melvoin". But how does one parse a string value in the PL/pgSQL function?
(b) Write to a temp table. But then the trigger would have to execute a SELECT to retrieve the value. How does a PL/pgSQL function extract a value from the resulting table of the SELECT?