From: | Bob Pawley <rjpawley(at)shaw(dot)ca> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Michael Fuhr <mike(at)fuhr(dot)org>, Postgresql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Inserting Data |
Date: | 2006-08-23 22:27:25 |
Message-ID: | 008801c6c703$650d7d50$8e904618@owner |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I thought the NEW qualified the select.
If not, how is select qualified??
Thanks
Bob
----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
Cc: "Michael Fuhr" <mike(at)fuhr(dot)org>; "Postgresql"
<pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, August 23, 2006 3:18 PM
Subject: Re: [GENERAL] Inserting Data
> Bob Pawley <rjpawley(at)shaw(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-08-23 22:31:16 | Re: Inserting Data |
Previous Message | Tom Lane | 2006-08-23 22:18:21 | Re: Inserting Data |