Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search for
  Advanced Search

Rules with sequence columns



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) NOT NULL 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 foo
DO INSERT INTO cdFoo ( contextK, componentK )
SELECT currval ( 'foo_seq' ), componentK
FROM Component
WHERE componentN = 'Division';

or

CREATE RULE insertCD AS ON INSERT TO foo
DO INSERT INTO cdFoo ( contextK, componentK )
SELECT new.fooK, componentK
FROM Component
WHERE componentN = '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?



Home | Main Index | Thread Index

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group