I haven't found any documentation on how the underlying structure of
PostgreSQL actually operates. So I have had to extrapolate.
I think what you are saying is that on an update of a field the whole row
which includes that field is affected to the extent that the whole row falls
under the rules of New/Old.
Is that a fair statement?
However the present problem is that I get two or multiple rows returned when
I update the pump1 field to 'True' - even when there is only a single row in
the table.
The complete After Update trigger follows -
Begin
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;
If new.pump2 = '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 #2', '11',
'Pump');
End if ;
RETURN NULL;
END;
Bob
----- Original Message -----
From: "Tom Lane" <tgl@xxxxxxxxxxxxx>
To: "Bob Pawley" <rjpawley@xxxxxxx>
Cc: "Postgresql" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Friday, January 22, 2010 9:56 AM
Subject: Re: Old/New
"Bob Pawley" <rjpawley@xxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
