I want my client app to communicate some information to the Postgres server.
Specifically:
My app logs into Postgres with its own user-name, rather than the human user's name. I have a trigger to record row
changesto 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
theresome 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
beused 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
functionparse the "application_name" to retrieve the user name, "Wendy Melvoin". But how does one parse a string value
inthe 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/pgSQLfunction extract a value from the resulting table of the SELECT?
--Basil Bourque