Thread: Triggering from a specific column update

"Bob Pawley" <rjpawley@shaw.ca> writes:
> "PostgreSQL does not support specific column updates in triggers."
> I found this statement on a blog.

> Is there a workaround for this?

If you'd explain what you think that statement means, maybe we could
help you ...

            regards, tom lane

I'm trying to trigger from an update.

However the trigger functions when any column has been updated.

I have columns pump1 and pump2 and column serial.

When pump1 is updated the trigger function performs properly. (one row is
returned)

When pump2 is updated the trigger function returns  two rows )one row for
column pump1 and one for column pump2) I end up with two rows of  pump1 and
one row of pump2.

If I write the function with a null such as --
 If new.pump1 = 'True'
 then
 Insert into p_id.devices (p_id_id, process_id,  fluid_id, status,
process_graphics_id, device_description)
 select (p_id.processes.p_id_id), (p_id.processes.process_id),
(p_id.processes.fluid_id), ('Pump #1'), ('11'), ('Pump')
 from p_id.processes
 where new.pump1 = 'True'
 and p_id.processes.pump2 is null;

it works fine returning what I want. However, when the serial column is
updated I get a return which includes pump1 and pump2 as well as the serial
column.

Hope this elucidates you?

Bob


----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Sunday, November 15, 2009 3:43 PM
Subject: Re: [GENERAL] Triggering from a specific column update


> "Bob Pawley" <rjpawley@shaw.ca> writes:
>> "PostgreSQL does not support specific column updates in triggers."
>> I found this statement on a blog.
>
>> Is there a workaround for this?
>
> If you'd explain what you think that statement means, maybe we could
> help you ...
>
> regards, tom lane

"Bob Pawley" <rjpawley@shaw.ca> writes:
> Hope this elucidates you?

No, it's all handwaving.  In particular, showing only a fragment from
a case that does work as you expect doesn't illuminate what's not
working.   Please show the whole table definition, the whole trigger,
and the specific case that's not doing what you expect.

            regards, tom lane

On Sunday 15 November 2009 5:18:20 pm Tom Lane wrote:
> "Bob Pawley" <rjpawley@shaw.ca> writes:
> > Hope this elucidates you?
>
> No, it's all handwaving.  In particular, showing only a fragment from
> a case that does work as you expect doesn't illuminate what's not
> working.   Please show the whole table definition, the whole trigger,
> and the specific case that's not doing what you expect.
>
>             regards, tom lane

The above would help greatly with coming to a correct answer. In the mean time
the problem seems to be that the trigger fires and inserts a row everytime it
sees a NEW.pump* value = 'True'. Since an update in Postgres is basically an
insert/delete operation everytime you update you will get back the existing
values as well as any changed values in the current update. This means if you
do sequential updates changing the pump1 to 'True',pump2 to 'True' and serial
values the trigger will keep inserting rows because the new.pump1 value will
meet the the IF condition. The way I have dealt with this is to do NEW.*/OLD.*
comparisons to determine if I am truly looking at a changed value or a recycled
one.

--
Adrian Klaver
aklaver@comcast.net