pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRULE and default nextval() column
2+ messages / 2 participants
[nested] [flat]
* RULE and default nextval() column
@ 2004-10-27 13:19 Dmitry P. Ovechkin <odp@inpris.ru>
2004-10-27 14:11 ` Re: RULE and default nextval() column Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Dmitry P. Ovechkin @ 2004-10-27 13:19 UTC (permalink / raw)
To: pgsql-sql
Hello.
I'mtrying to implement history tables using rules.
I have
test_table
----------
create sequence history_seq start 1;
create sequence test_sequence;
# source table
drop table test_table;
create table test_table (
i integer default nextval('test_sequence'),
c character(10)
);
# history table
create table test_table_history (
hist integer default nextval('history_seq'),
i integer,
c character(10)
);
# rule to save history
create rule test_table_history_insert as on insert to test_table do
insert into test_table_history values ( nextval('history_seq'),
new.i,
new.c
);
#
Then I try to insert into test_table;
test=> insert into test_table values( nextval('test_sequence'), 'a');
INSERT 3299176 1
test=> insert into test_table (c) values('a');
INSERT 3299178 1
drweb=> select * from test_table;
i | c
---+------------
1 | a
3 | a
(2 rows)
test=> select * from test_table_history;
hist | i | c
------+---+------------
1 | 2 | a
2 | 4 | a
(2 rows)
==========
Problem is : value of "i" field increments during insertion into both
test_table and test_table history.
This also happens if I omit "i" in insert statement and let it be filled
by default.
Can something be done to avoid it?
Sincerely, Dmitry Ovechkin.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: RULE and default nextval() column
2004-10-27 13:19 RULE and default nextval() column Dmitry P. Ovechkin <odp@inpris.ru>
@ 2004-10-27 14:11 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2004-10-27 14:11 UTC (permalink / raw)
To: Dmitry P. Ovechkin <odp@inpris.ru>; +Cc: pgsql-sql
On Wed, 27 Oct 2004, Dmitry P. Ovechkin wrote:
> Hello.
> I'mtrying to implement history tables using rules.
> I have
> test_table
> ----------
> create sequence history_seq start 1;
> create sequence test_sequence;
> # source table
> drop table test_table;
> create table test_table (
> i integer default nextval('test_sequence'),
> c character(10)
> );
> # history table
> create table test_table_history (
> hist integer default nextval('history_seq'),
> i integer,
> c character(10)
> );
> # rule to save history
> create rule test_table_history_insert as on insert to test_table do
> insert into test_table_history values ( nextval('history_seq'),
> new.i,
> new.c
> );
> #
> Then I try to insert into test_table;
> test=> insert into test_table values( nextval('test_sequence'), 'a');
> INSERT 3299176 1
> test=> insert into test_table (c) values('a');
> INSERT 3299178 1
> drweb=> select * from test_table;
> i | c
> ---+------------
> 1 | a
> 3 | a
> (2 rows)
>
> test=> select * from test_table_history;
> hist | i | c
> ------+---+------------
> 1 | 2 | a
> 2 | 4 | a
> (2 rows)
>
> ==========
> Problem is : value of "i" field increments during insertion into both
> test_table and test_table history.
> This also happens if I omit "i" in insert statement and let it be filled
> by default.
> Can something be done to avoid it?
Pretty much the only real answer is to use a trigger rather than a rule.
Rules are basically like macro expansions and have many of the same
problem with side effects that macros do.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-10-27 14:11 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-27 13:19 RULE and default nextval() column Dmitry P. Ovechkin <odp@inpris.ru>
2004-10-27 14:11 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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