Re: Old/New - Mailing list pgsql-general

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@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Friday, January 22, 2010 9:56 AM
Subject: Re: [GENERAL] Old/New


> "Bob Pawley" <rjpawley@shaw.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general