I thought the NEW qualified the select.
If not, how is select qualified??
Thanks
Bob
----- Original Message -----
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Bob Pawley" <[EMAIL PROTECTED]>
Cc: "Michael Fuhr" <[EMAIL PROTECTED]>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Wednesday, August 23, 2006 3:18 PM
Subject: Re: [GENERAL] Inserting Data
Bob Pawley <[EMAIL PROTECTED]> writes:
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)
Well, of course, because that's an unqualified "select", so each call
will copy *all* of p_id.devices into p_id.loops. Methinks what you
really want is to insert the NEW row, not the whole table.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
