Thread: Passing a variable from the user interface to PostgreSQL
Using:
Windows 7 64bit
VB.net 2010
The latest devart.data.PostgreSQL (.net data connection)
PostgreSQL 9.0.2
Hi all,
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?
I’m thinking it’s not possible but thought I would ask anyway.
Thanks for any comments,
Chris Campbell
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
> -----Original Message----- > From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Thomas Kellerer > Sent: Friday, January 21, 2011 11:04 AM > To: pgsql-novice@postgresql.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 variableI'm writing could change from transaction to transaction, however, within the given delete transaction and subsequenttrigger events, the variable will always be the same. So a query to this "temp" table holding my value would onlyreturn 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 mytrigger spawned function call regardless of any other transactions that may be occurring at the same time. Sound aboutright?
Chris Campbell wrote: > > Using: > > Windows 7 64bit > > VB.net 2010 > > The latest devart.data.PostgreSQL (.net data connection) > > PostgreSQL 9.0.2 > > > > Hi all, > > > > 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? > > > > I’m thinking it’s not possible but thought I would ask anyway. > > > > Thanks for any comments, > > > > Chris Campbell > > ccampbell@CascadeDS.com <mailto:ccampbell@CascadeDS.com> > > > Well, obviously, it isn't possible to pass a variable from UI to a trigger. What is possible is to send the record primary key, along with any messages you need, to a background process, using some kind of a message passing mechanism, and there are plenty of those. The background process can delete the row and write message in a single transaction. It can even converse with the UI using message passing system. An excellent example of such a system, priced exactly right, is Apache ActiveMQ. Apache ActiveMQ price is exactly half of the Postgres price. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com
On 2011-01-21, Chris Campbell <ccampbell@cascadeds.com> wrote: > --_000_453A24085F801842AEA8D0B6B269065D45E2521023HDMCcdslocal_ > Content-Type: text/plain; charset="us-ascii" > Content-Transfer-Encoding: quoted-printable > > Using: > Windows 7 64bit > VB.net 2010 > The latest devart.data.PostgreSQL (.net data connection) > PostgreSQL 9.0.2 > > Hi all, > > Question: Is there a way to pass a variable from an application's user inte= > rface to the database such that it would be visible inside a database trigg= > er and any corresponding function call? no, but you could store the value and their backend's process id in a table beforehand. the trigger could then retreive it from there. or store it in a temporary table and the trigger can use dynamic sql to retreive it. See also, the "System Information Functions" section of the manual -- ⚂⚃ 100% natural
On 2011-01-21, Chris Campbell <ccampbell@cascadeds.com> wrote: > --_000_453A24085F801842AEA8D0B6B269065D45E2521023HDMCcdslocal_ > Content-Type: text/plain; charset="us-ascii" > Content-Transfer-Encoding: quoted-printable > > Using: > Windows 7 64bit > VB.net 2010 > The latest devart.data.PostgreSQL (.net data connection) > PostgreSQL 9.0.2 > > Hi all, > > Question: Is there a way to pass a variable from an application's user inte= > rface to the database such that it would be visible inside a database trigg= > er and any corresponding function call? you can embed the extra value in an sql comment and retrieve it with current_query() "-- this is extra value delete from sometable where thiscolumn='thatvalue';" works in 8.4 -- ⚂⚃ 100% natural
Jasen Betts wrote: > On 2011-01-21, Chris Campbell <ccampbell@cascadeds.com> wrote: > >> --_000_453A24085F801842AEA8D0B6B269065D45E2521023HDMCcdslocal_ >> Content-Type: text/plain; charset="us-ascii" >> Content-Transfer-Encoding: quoted-printable >> >> Using: >> Windows 7 64bit >> VB.net 2010 >> The latest devart.data.PostgreSQL (.net data connection) >> PostgreSQL 9.0.2 >> >> Hi all, >> >> Question: Is there a way to pass a variable from an application's user inte= >> rface to the database such that it would be visible inside a database trigg= >> er and any corresponding function call? >> > > > you can embed the extra value in an sql comment and retrieve > it with current_query() > > "-- this is extra value > delete from sometable where thiscolumn='thatvalue';" > > works in 8.4 > > Once you have to resort to the parsing of the initial SQL within the database trigger, you know that the application design is fubar. It's time to think about the new application design. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com
mladen.gogala@vmsinfo.com (Mladen Gogala) writes:
> Jasen Betts wrote:
>> On 2011-01-21, Chris Campbell <ccampbell@cascadeds.com> wrote:
>>
>>> --_000_453A24085F801842AEA8D0B6B269065D45E2521023HDMCcdslocal_
>>> Content-Type: text/plain; charset="us-ascii"
>>> Content-Transfer-Encoding: quoted-printable
>>>
>>> Using:
>>> Windows 7 64bit
>>> VB.net 2010
>>> The latest devart.data.PostgreSQL (.net data connection)
>>> PostgreSQL 9.0.2
>>>
>>> Hi all,
>>>
>>> Question: Is there a way to pass a variable from an application's user inte=
>>> rface to the database such that it would be visible inside a database trigg=
>>> er and any corresponding function call?
>>>
>>
>>
>> you can embed the extra value in an sql comment and retrieve
>> it with current_query()
>>
>> "-- this is extra value
>> delete from sometable where thiscolumn='thatvalue';"
>>
>> works in 8.4
>>
>>
> Once you have to resort to the parsing of the initial SQL within the
> database trigger, you know that the application design is fubar.
> It's time to think about the new application design.
;-)
Stowing it in the table comment is about the worst mechanism I can think
of... If one is open to going that far astray, it should surely seem
reasonable to create a table in which to stow this sort of supplementary
data.
What *might* be reasonable...
create table variables_to_log (
variable_value text,
conn_pid integer default pg_catalog.pg_backend_pid(),
primary key (conn_pid, variable_value)
);
A query has to be run to stow the variable_value...
insert into variables_to_log (variable_value) values ('var I wanna log');
Let me presume that the log table has a particular form...
create table log_stuff (
user_variable text,
when_deleted timestamptz default now(),
source_id integer,
db_conn integer default pg_catalog.pg_backend_pid(),
primary key (source_id, user_variable, db_conn)
);
Later, the query runs against the table that has the trigger:
delete from some_records where id = 25;
That fires the trigger function:
create function log_stuff () returns trigger as $$
begin
insert into log_stuff (user_variable, source_id)
select variable_value, OLD.id
from variables_to_log where conn_pid = pg_catalog.pg_backend_pid();
delete from variables_to_log where conn_pid = pg_catalog.pg_backend_pid();
return OLD;
end
$$ language plpgsql;
I don't quite claim this is a 'good move,' but it's not as bad as
stowing queued work in a table comment :-).
--
http://linuxfinances.info/info/postgresql.html
"Gnome certainly is (serious competition to the Mac or Windows) ... I
get a charge out of seeing the X Window System work the way we
intended..." - Jim Gettys
mladen.gogala@vmsinfo.com (Mladen Gogala) writes: > Jasen Betts wrote: >> On 2011-01-21, Chris Campbell <ccampbell@cascadeds.com> wrote: >> >>> --_000_453A24085F801842AEA8D0B6B269065D45E2521023HDMCcdslocal_ >>> Content-Type: text/plain; charset="us-ascii" >>> Content-Transfer-Encoding: quoted-printable >>> >>> Using: >>> Windows 7 64bit >>> VB.net 2010 >>> The latest devart.data.PostgreSQL (.net data connection) >>> PostgreSQL 9.0.2 >>> >>> Hi all, >>> >>> Question: Is there a way to pass a variable from an application's user inte= >>> rface to the database such that it would be visible inside a database trigg= >>> er and any corresponding function call? >>> >> >> >> you can embed the extra value in an sql comment and retrieve >> it with current_query() >> >> "-- this is extra value >> delete from sometable where thiscolumn='thatvalue';" >> >> works in 8.4 >> >> > Once you have to resort to the parsing of the initial SQL within the > database trigger, you know that the application design is fubar. > It's time to think about the new application design. >;-) >Stowing it in the table comment is about the worst mechanism I can think >of... If one is open to going that far astray, it should surely seem >reasonable to create a table in which to stow this sort of supplementary >data. >What *might* be reasonable... It's always interesting to see how many different ways there are to skin a cat. (Not that I have anything against cats). I appreciate everyone taking the time to respond and offer up ideas. I'm new to dealing with what I call a "real"database like Postgresql. My approach has always been to deal with tasks through programming. Now I have an opportunityto take advantage of the power of a real database and thought that using table triggers to launch processes thatwrote to log tables was the correct approach. Perhaps in most cases it is. However, based on the fact that my visionof "easily" getting information from the UI to a trigger spawned database function ended up being just wishful thinking,I believe I'll initiate the log entries out of the UI. I don't believe it was too much of a stretch to think itwas a possibility; after all, you can pass parameters through a connection's command object to a database function, whynot to a trigger? Heck, if the Seattle Seahawks can win their division with an over-all record of 7-9, then defeat thedefending Super Bowl champs, I was ready to believe anything was possible. ;) Thanks again for your thoughts. Chris Campbell Ccampbell@Cascadeds.com -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice