Rules with sequence columns

Поиск
Список
Период
Сортировка
От Ray Madigan
Тема Rules with sequence columns
Дата
Msg-id 460A96ED.9000001@madigans.org
обсуждение исходный текст
Ответы Re: Rules with sequence columns
Список pgsql-sql
I have the following situation that I would appreciate your input on:

I have a table with a column that I use to uniquely identify its rows.
The table also has a rule on insert that stores the row identifier into 
another table for reference at some other point.

The table is defined as

CREATE SEQUENCE foo_seq;

CREATE TABLE foo ( fooK INTEGER     DEFAULT NEXTVAL ( 'foo_seq' ),                                    fooN VARCHAR(32)
NOTNULL UNIQUE,                                    link      INTEGER     NOT NULL 
 
DEFAULT 0 );

The rule does an insert into another table and I have implemented the 
rule in two ways.

CREATE RULE insertCD AS ON INSERT TO fooDO INSERT INTO cdFoo ( contextK, componentK )SELECT currval ( 'foo_seq' ),
componentKFROMComponentWHERE componentN = 'Division';
 

or

CREATE RULE insertCD AS ON INSERT TO fooDO INSERT INTO cdFoo ( contextK, componentK )SELECT new.fooK, componentKFROM
ComponentWHEREcomponentN = 'Division';
 

The situation is that every time the rule fires, the foo sequence is 
incremented
for each row in the foo table. and the reference value is not the same 
in the table.

I have tried to take the default nextval ( 'foo_seq' ) from the row 
initialization and move it to the insert

insert into foo ( fook, fooN ) values ( nextval ( 'foo_seq' ), 'Name' );
with the same result.

The only way I have been able to make it work is ugly.

int fooK = select nextval ( 'foo_seq' );
insert into foo ( fooK, fooN ) values ( fooK, 'Name' );

Does anyone have any suggestion?


В списке pgsql-sql по дате отправления:

Предыдущее
От: Gerardo Herzig
Дата:
Сообщение: Re: union with count?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Rules with sequence columns