Re: Old/New

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(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: Friday, January 22, 2010 9:56 AM
Subject: Re: [GENERAL] 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general