Re: Passing a variable from the user interface to PostgreSQL

> -----Original Message-----
> From: pgsql-novice-owner(at)postgresql(dot)org [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Thomas Kellerer
> Sent: Friday, January 21, 2011 11:04 AM
> To: pgsql-novice(at)postgresql(dot)org
> Subject: Re: [NOVICE] 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

Hi Thomas, thanks for your "dirty little hack" heh.

So let me talk this out and see if this holds up. Any number of deletes could be occurring at any given time. The variable I'm writing could change from transaction to transaction, however, within the given delete transaction and subsequent trigger events, the variable will always be the same. So a query to this "temp" table holding my value would only return the value that was written during the transaction that fired the trigger in the first place.

So given this, I could have multiple delete transactions occurring and the value I want would always be returned from my trigger spawned function call regardless of any other transactions that may be occurring at the same time. Sound about right?