Re: Old/New

Following is the format with which I have had great success using "New" in
After Insert triggers.

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';

However when the above is used on an After Update trigger on a table with
two rows, I get both rows inserted.

(For a while I thought it was a quirk in the actions of the interface, but
it happens even when I update pump1 using only PostgreSQL.

This is the reason that I have been avoiding Update triggers until now, so,
if anyone can help my understanding of what is happening I would appreciate
it.

Bob

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
Cc: "Postgresql" <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, January 21, 2010 3:16 PM
Subject: Re: [GENERAL] Old/New

> "Bob Pawley" <rjpawley(at)shaw(dot)ca> writes:
>> I am getting a strange result when using the following -
>
>> Select fluid_id into fluidid
>> from p_id.processes
>> where new.pump1 = 'True'
>> and old.pump1 = 'False'
>> or old.pump1 is null;
>
>> The fluid_id return is fine when there is a single row. However with two
>> rows, and updating only one of the rows, I quite often get the fluid_id
>> for the other row.
>
> That WHERE condition isn't constraining the SELECT at all; you're
> getting the result from the first row in the table. I think you have
> some fundamental confusion about how to work with OLD and NEW in
> triggers. They're just rowtype variables, you do not need to select
> from the table to examine them.
>
> regards, tom lane