pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Rules with sequence columns
2+ messages / 2 participants
[nested] [flat]
* Rules with sequence columns
@ 2007-03-28 16:25 Ray Madigan <ray@madigans.org>
2007-03-28 16:17 ` Re: Rules with sequence columns Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Ray Madigan @ 2007-03-28 16:25 UTC (permalink / raw)
To: 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) 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?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Rules with sequence columns
2007-03-28 16:25 Rules with sequence columns Ray Madigan <ray@madigans.org>
@ 2007-03-28 16:17 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2007-03-28 16:17 UTC (permalink / raw)
To: Ray Madigan <ray@madigans.org>; +Cc: pgsql-sql
Ray Madigan <ray@madigans.org> writes:
> Does anyone have any suggestion?
Use a trigger to propagate the data to the other table. You can't make
this work reliably with a rule, because rules are macros and hence
inherently subject to double-evaluation problems when dealing with
volatile functions.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-03-28 16:25 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-28 16:25 Rules with sequence columns Ray Madigan <ray@madigans.org>
2007-03-28 16:17 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox