Re: Passing a variable from the user interface to PostgreSQL

Chris Campbell wrote on 21.01.2011 19:39:
> Question: Is there a way to pass a variable from an application’s
> user interface to the database such that it would be visible inside a
> database trigger and any corresponding function call?
>
> When a record gets deleted, a table trigger fires that writes to a
> log table. What I want to do is pass a variable from the user
> interface to the trigger function so I can write the variable’s value
> to the log record that I create in a function called from the trigger
> event. I’m not sure how this could even be accomplished unless
> somehow it was possible to pass a variable through the data
> connection itself. But even then, how would the trigger function be
> able to see it and pass it along? Is there some other mechanism or
> approach I should be taking?
>

This dirty hack might work:

Create some table to hold the variable (e.g. single row/column)

Now when you do your update from within your application do the following:

- Start a transaction
- Insert the variable into that table - but do not commit this insert
- Do you regular update
(As the trigger runs in the same transaction as the insert that supplied the value it will see the row)
- after the update completed (or whatever you did), delete the row from the variable table
- commit your transaction

Regards
Thomas