Here is my $0.02 :
* when you create "id SERIAL", Postgres remembers to call function nextval on each
insertion,
* the rule's NEW.id item uses the function nextval itself instead of it's result
This explains why the ID's are what you see :
* first of all, you insert the log, calling nextval for the SERIAL (id=1 in the log)
* then you actually insert the data into the colors table (first row has id=2)
* then you insert a second time : first into the log (id=3) then into the actual table
(id=4)
This make me think about date constants : 'now' is a constant that have a different
value each time you call it. In your case, the rule must use then constant 'nextval',
which increments the actual sequence on each call.
Either this is a bug... or a feature...
I don't see any genral workaround here. Maybe there is another way of retreiving the
actual inserted data (other than NEW.id)
Yours,
Nicolas Huillard
G.H.S
Directeur Technique
Tél : +33 1 43 21 16 66
Fax : +33 1 56 54 02 18
mailto:[EMAIL PROTECTED]
http://www.ghs.fr
-----Message d'origine-----
De: Vladimir V. Zolotych [SMTP:[EMAIL PROTECTED]]
Date: lundi 8 mai 2000 18:00
À: [EMAIL PROTECTED]
Objet: [ADMIN] rules problem
Hello all,
Encountered the problem with using RULEs. Cannot log
(e.g. write some info about insertions into sepearate table)
insertions properly. Detailed description (not long or sophisticated)
follows:
I do:
1) CREATE TABLE colors (id SERIAL, color TEXT);
2) Create table for log info:
CREATE TABLE colors_log (color_id INT4, color TEXT);
3) Create RULE that actually makes log:
CREATE RULE log_color
AS ON INSERT
TO colors
DO INSERT INTO colors_log VALUES (NEW.id, NEW.color);
4) Make some insertions:
INSERT INTO colors (color) VALUES ('red');
The same for 'green', 'blue'.
5) SELECT * FROM colors;
id|color
--+-----
2|red
4|green
6|blue
Here appears the first question:
why 'id' is 2, 4, 6, not 1, 2, 3?
7) SELECT * FROM colors_log;
color_id|color
--------+-----
1|red
3|green
5|blue
The problem is: the 'id's differ. E.g.,
In colors_log table the saved 'id' are wrong.
Thanks!
--
Vladimir Zolotych [EMAIL PROTECTED]
