Search Postgresql Archives

Re: Old/New

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux