Thread: Problem: Sequence X Rule Reference!

Problem: Sequence X Rule Reference!

From
"Junior - Cotrijui"
Date:
/***************************************************************************
*******************************
  Hello my friends,
    I have a problem, please, see the example above:
****************************************************************************
*******************************/

create table test1(myfield numeric(10));
create sequence test1_seq;
create table test2(myfield text);

create or replace rule test1_insert as on insert to test1 do
  insert into test2 values(new.myfield::text || new.myfield::text ||
new.myfield::text || new.myfield::text);

insert into test1 values (nextval('test1_seq'));
insert into test1 values (nextval('test1_seq'));
insert into test1 values (nextval('test1_seq'));
insert into test1 values (nextval('test1_seq'));
insert into test1 values (nextval('test1_seq'));
insert into test1 values (nextval('test1_seq'));
insert into test1 values (nextval('test1_seq'));
insert into test1 values (nextval('test1_seq'));
insert into test1 values (nextval('test1_seq'));

select * from test2;

/***************************************************************************
********************************
problem description:
  for each reference of 'new.campo' on rule 'teste1_insert' the sequence is
called!

  is this a bug? how can i do to solve my problem?

obtained result on table2                                  expected result
on  table2
--- table test1 ---   --- table test 2 ---                 --- table test1
---   --- table test 2 ---
1                      2345                                1
1111
6                      78910                               2
2222
11                     12131415                            3
3333
16                     17181920                            4
4444
21                     22232425                            5
5555
26                     27282930                            6
6666
31                     32333435                            7
7777
36                     37383940                            8
8888
41                     42434445                            9
9999

Thanks!

Junior - COTRIJUI
e-mail / msn: junior@cotrijui.coop.br
icq: 58055700 - skype: selvinojunior
****************************************************************************
*******************************/


---
Outgoing mail is certified Virus Free. ABC
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.725 / Virus Database: 480 - Release Date: 19/07/2004