Обсуждение: Can a client connection set variables on the server-side?

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

Hi Basil,

> 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? 

Does

Select current_user

do what you want?

> 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? 


I don't think you need to. You have a wonderful database that can tackle these kind of things.

> (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? 


declare
mycoolvariable text;
begin
select somefield from sometable where sometable.someotherfield=someothervalue into mycoolvariable;

..maybe?

Maximilian Tyrtania Software-Entwicklung
Dessauer Str. 6-7
10969 Berlin
http://www.contactking.de

Am 16.04.2011 um 05:36 schrieb Basil Bourque:
>
>
>
> Similarly, I'd like to include some text from the server about the context of what the user is doing at the moment,
tobe 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
functionparse the "application_name" to retrieve the user name, "Wendy Melvoin". But how does one parse a string value
inthe PL/pgSQL function? 
>