Re: Old/New

"Bob Pawley" <rjpawley(at)shaw(dot)ca> writes:
> 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';

Hmm, maybe for small values of "great success". new.pump1 is simply a
local variable in the plpgsql function. That means that the above
command will have one of two behaviors:

* if new.pump1 has the value 'True', every row in p_id.processes will be
copied into p_id.devices, because the WHERE condition succeeds at
every row;
* if new.pump1 has any other value, nothing gets copied, because the
WHERE condition succeeds nowhere.

Maybe that's actually what you intended, but I rather doubt it. It
seems more likely to me that what you want is something like

if new.pump1 = 'True' then
Insert into p_id.devices (p_id_id, process_id, fluid_id, status,
process_graphics_id, device_description)
values (new.p_id_id, new.process_id, new.fluid_id, 'Pump #1', '11', 'Pump');
end if;

which would have the effect of inserting based on the contents of NEW.*
and nothing else.

regards, tom lane