Re: Old/New

> 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
>
>

This is different from what you originally posted. Is the above still
causing problems?

Yes. The above inserts two versions of the same row.

Bob
----- Original Message -----
From: "Adrian Klaver" <adrian(dot)klaver(at)gmail(dot)com>
To: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>; "Postgresql"
<pgsql-general(at)postgresql(dot)org>
Sent: Friday, January 22, 2010 12:34 PM
Subject: Re: [GENERAL] Old/New

> On 01/22/2010 11:20 AM, Bob Pawley wrote:
>> 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?
>
> Maybe an example is in order.
>
> Existing row
> id desc pump1
> 1 test f
>
> UPDATE foo set pump1 ='t';
>
> OLD row
> id desc pump1
> 1 test f
>
> NEW row
> id desc pump1
> 1 test t
>
>
> At the point the AFTER UPDATE trigger is fired it has access to both the
> OLD and NEW rows via the OLD.* and NEW.* variables per Toms explanation.
>
>>
>> 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
>>
>>
>
> This is different from what you originally posted. Is the above still
> causing problems?
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>
> --
> 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