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

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?

--Basil Bourque

Search Discussions

  • Maximilian Tyrtania at Apr 18, 2011 at 12:48 pm
    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?

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedApr 16, '11 at 3:36a
activeApr 18, '11 at 12:48p
posts2
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase