Re: Inserting Data
От | Bob Pawley |
---|---|
Тема | Re: Inserting Data |
Дата | |
Msg-id | 005401c6c6ff$e8a2eeb0$8e904618@owner обсуждение исходный текст |
Ответ на | Inserting Data (Bob Pawley <rjpawley@shaw.ca>) |
Ответы |
Re: Inserting Data
|
Список | pgsql-general |
Michael Perhaps we can look at the following as a simple example of what is happening- --------- create or replace function loop_association() returns trigger as $$ begin Insert Into p_id.loops (monitor) select new.devices_id from p_id.devices ; return null ; end ; $$ language plpgsql ; create trigger loop after insert on p_id.devices for each row execute procedure loop_association(); ------ This trigger and procedure gives a single row on the first insert on an otherwise blank table. However it produces two identical rows of the second device_id on the second insert and three identical rows of the third device_id on the third insert. (This is the only trigger on the table) If I read your message correctly the trigger is firing on each row of the originating table and each time it fires it produces a row on the secondary table for the current NEW.device_id. How can I correct this action? Bob ----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Postgresql" <pgsql-general@postgresql.org> Sent: Tuesday, August 22, 2006 1:58 PM Subject: Re: [GENERAL] Inserting Data > On Tue, Aug 22, 2006 at 09:02:52AM -0700, Bob Pawley wrote: >> 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. > > If it was doing that then it would be a good idea to understand > why. If the INSERT ... SELECT matched several rows then several > rows would be inserted, and if the trigger fired for several rows > then several INSERTs would be run. > >> 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'. > [...] >> This trigger results in three rows of each "new" field. > > What's the exact update command and how many rows in p_id.devices > does it affect? If the update modifies three rows then the trigger > will fire three times (because it's defined FOR EACH ROW), resulting > in three inserts. That could explain the insert-vs-update difference > because an ordinary insert affects only one row. If you add a RAISE > statement to the trigger function then you'll see when and how many > times it's being called. > > -- > Michael Fuhr > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
В списке pgsql-general по дате отправления: