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@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Thursday, January 21, 2010 3:16 PM
Subject: Re: [GENERAL] Old/New


> "Bob Pawley" <rjpawley@shaw.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