Re: Inserting Data

I thought the NEW qualified the select.

If not, how is select qualified??

Thanks

Bob



----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Michael Fuhr" <mike@fuhr.org>; "Postgresql"
<pgsql-general@postgresql.org>
Sent: Wednesday, August 23, 2006 3:18 PM
Subject: Re: [GENERAL] Inserting Data


> Bob Pawley <rjpawley@shaw.ca> 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