Re: Can a client connection set variables on the server-side?

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

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 there some 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/pgSQL function 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, 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?
>