Hi there... I've spotted something weird in 7.0.3 with rules. By now I've
realised I probably need to use a trigger to do what I have in mind, but
even so, there no way I can explain the behaviour I am getting with a
rule.
Given this SQL script:
CREATE TABLE menu (menu_id SERIAL PRIMARY KEY,name TEXT,price integer
);
INSERT INTO menu(name, price) VALUES ('Beer', 5);
INSERT INTO menu(name, price) VALUES ('Vodka', 10);
INSERT INTO menu(name, price) VALUES ('Scotch', 8);
CREATE TABLE orders (order_id SERIAL PRIMARY KEY,menu_id INTEGER REFERENCES menu,price INTEGER NOT
NULLDEFAULT -1
);
CREATE RULE fix_order_price AS
ON INSERT TO orders
DOUPDATE ordersSET price = M.priceFROM menu MWHERE M.menu_id = new.menu_idAND new.price = -1;
INSERT INTO orders (menu_id) VALUES (2);
SELECT * FROM orders;
Here's what happens:
% createdb buggy
CREATE DATABASE
% psql buggy < ~/pg.bug
NOTICE: CREATE TABLE will create implicit sequence 'menu_menu_id_seq' for SERIAL column 'menu.menu_id'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'menu_pkey' for table 'menu'
CREATE
INSERT 259680 1
INSERT 259681 1
INSERT 259682 1
NOTICE: CREATE TABLE will create implicit sequence 'orders_order_id_seq' for SERIAL column 'orders.order_id'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'orders_pkey' for table 'orders'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
CREATE 259722 1
INSERT 0 3order_id | menu_id | price
----------+---------+------- 1 | 2 | -1 2 | 2 | -1 3 | 2 | -1
(3 rows)
How the heck can one insert and update generate three rows?
--
Tod McQuillin
