Re: Inserting Data

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(at)fuhr(dot)org>
To: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
Cc: "Postgresql" <pgsql-general(at)postgresql(dot)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