Re: Passing a variable from the user interface to PostgreSQL

mladen(dot)gogala(at)vmsinfo(dot)com (Mladen Gogala) writes:
> Jasen Betts wrote:
>> On 2011-01-21, Chris Campbell <ccampbell(at)cascadeds(dot)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