Re: Inserting Data - Mailing list pgsql-general

Hi Michael

I set aside the procedure you sent to me as it resulted in multiple rows of
the same information. (In fact one variation produced 100 rows for each of
the 9 "new" fields creating a 900 row table.

I went back to an earlier procedure which has been performing successfully.

------
  create or replace function base() returns trigger as $$
 begin

 insert into p_id.specifications (fluid_id) values (new.fluid_id);

 if new.ip_op_equipment = 'ip'or new.ip_op_equipment = 'op'
 then
 insert into p_id.pipes (fluid_id) values (new.fluid_id);
 elseif
 new.ip_op_equipment = 'eq'
 then
 insert into p_id.equipment (fluid_id) values (new.fluid_id);
 end if;
 return null;
 end;
 $$ language plpgsql ;

 create trigger fluid after insert on p_id.processes
 for each row execute procedure base();
------------

In contrast here is the trigger for the tables with which I am now working.
As best as I can determine the two triggers are the same format.
Note the trigger is an 'after update' as opposed to 'after insert'.

CREATE OR REPLACE FUNCTION p_id.valves_mon()
  RETURNS "trigger" AS
$$
 begin

 if new.type_ = 'end'
 then
 insert into p_id.association (valve) values (new.devices_id) ;
 elseif
 new.type_ = 'mon'
 then
 insert into p_id.loops (monitor) values (new.devices_id) ;
 end if ;
 return null;
 end ;
 $$
  LANGUAGE 'plpgsql' VOLATILE;
 CREATE TRIGGER loop
  AFTER UPDATE
  ON p_id.devices
  FOR EACH ROW
  EXECUTE PROCEDURE p_id.valves_mon();

This trigger results in three rows of each "new" field.

I must admit I am having a little trouble fully understanding the basic
PostgreSQL structure.
What seems to me to be a logical procedure almost always has problems that I
need to sort through.

Help is greatly appreciated.

Bob.

----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Monday, August 21, 2006 4:47 PM
Subject: Re: [GENERAL] Inserting Data


> On Mon, Aug 21, 2006 at 08:27:58AM -0700, Bob Pawley wrote:
>> Yes - Multiple rows of the same data are created in each secondary table.
>>
>> I have two triggers that are identical in format although handling
>> different tables. One is triggeres after insert and with this there is no
>> multiplying factor.
>>
>> The other is triggered after an update.
>
> The insert-vs-update distinction might be a red herring; the
> difference in behavior might be a result of the queries run inside
> the trigger functions.  Or maybe the statements executed by the
> update trigger are firing additional triggers.  Without more
> information we can only guess.
>
>> Both triggers use NEW.* in the same manner. However, the trigger after
>> update gives multiple results of the same information.
>
> How are the triggers using NEW?  In your original message the
> function didn't use NEW at all.
>
>> Is there any way around this problem? Is there perhaps a method
>> restricting
>> the trigger to an update to a particular column rather than the table as
>> a
>> whole?
>
> Do you mean "particular row" instead of "particular column"?
>
> If you're executing INSERT ... SELECT statements from inside a
> trigger function as in your original message, then the restriction
> on the SELECT determines how many rows are inserted.  It's possible
> that those inserts are causing additional triggers to fire.  Have
> you added any RAISE statements to the trigger functions to see when
> they're being called?
>
> Could you post a simple, self-contained example that exhibits both
> the desired and undesired behavior?  That is, all SQL statements
> that somebody could load into an empty database to create and
> populate the tables, create the triggers, and perform whatever
> actions are necessary to elicit both behaviors.
>
> --
> Michael Fuhr