I have a table with a trigger that inserts records into a second table on insert. I have a view that has a rule that inserts a record into the first table then updates the records that the first table's trigger inserted into the second table. From what I can see, when the view rule executes, the records in the second table are not yet inserted so they do not get updated. Please see code below (simplified for readability). Any ideas on why this is or another solution would be appreciated. Thanks. -- Tables CREATE TABLE a (a_id SERIAL, name VARCHAR(10), type_id INT); CREATE TABLE item_type (item_type_id SERIAL, a_type_id INT, type_name VARCHAR(10));CREATE TABLE a_item (a_item_id SERIAL, a_id INT, type_id INT, val NUMERIC); -- Add some type data insert into item_type values (default,6,'quantity'); insert into item_type values (default,6,'price'); insert into item_type values (default,6,'discount'); -- Show type data select * from item_type; -- item_type_id | a_type_id | type_name ----------------+-----------+----------- -- 1 | 6 | quantity -- 2 | 6 | price -- 3 | 6 | discount --(3 rows) -- Trigger function to add items CREATE LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION add_items() RETURNS "trigger" AS $BODY$BEGIN INSERT INTO a_item (a_id, type_id) SELECT a.a_id, item_type.item_type_id FROM a JOIN item_type ON a.type_id = item_type.a_type_id WHERE a.a_id = NEW.a_id; RETURN NULL; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE; -- Create trigger CREATE TRIGGER insert_a_items AFTER INSERT ON a FOR EACH ROW EXECUTE PROCEDURE add_items(); -- Insert an "a" record insert into a values (default,'Test',6); -- Trigger works select * from a_item; -- a_item_id | a_id | type_id | val -------------+------+---------+----- -- 1 | 1 | 1 | -- 2 | 1 | 2 | -- 3 | 1 | 3 | --(3 rows) -- The view is a flattened version of the "a" and "a_item" table for a specific case CREATE OR REPLACE VIEW options AS SELECT a.a_id AS options_id, a.name AS options_name, (SELECT a_item.val FROM a_item WHERE a_item.a_id = a.a_id AND a_item.type_id = 1) AS quantity, (SELECT a_item.val FROM a_item WHERE a_item.a_id = a.a_id AND a_item.type_id = 2) AS price, (SELECT a_item.val FROM a_item WHERE a_item.a_id = a.a_id AND a_item.type_id = 3) AS discount FROM a WHERE a.type_id = 6; select * from options; -- options_id | options_name | quantity | price | discount --------------+--------------+----------+-------+---------- -- 1 | Test | | | --(1 row) -- The rule inserts into the "a" table and then updates the "a_item" -- table with the records that the trigger is supposed -- to execute before the next commands are run. CREATE OR REPLACE RULE insert_options AS ON INSERT TO options DO INSTEAD -- Insert into table "a" (INSERT INTO a (a_id, type_id, name) VALUES (new.options_id, 6, new.options_name); -- Update 3 records in table "a_item" -- This doesn't work because the records do not appear to exist yet? UPDATE a_item SET val = new.quantity WHERE a_item.a_id = new.options_id AND a_item.type_id = 1; UPDATE a_item SET val = new.price WHERE a_item.a_id = new.options_id AND a_item.type_id = 2; UPDATE a_item SET val = new.discount WHERE a_item.a_id = new.options_id AND a_item.type_id = 3; ); -- Insert into the view insert into options values (nextval('a_a_id_seq'),'Test 2',1,2,3); -- View data select * from options; options_id | options_name | quantity | price | discount ------------+--------------+----------+-------+---------- 1 | Test | | | 2 | Test 2 | | | (2 rows) If I change the insert_options rule to insert into a_item, then I get 6 records in a_item (3 from insert_a_items and 3 from insert_options). The first 3 have null vals and the second 3 have the correct vals. It should be: options_id | options_name | quantity | price | discount ------------+--------------+----------+-------+---------- 1 | Test | | | 2 | Test 2 | 1 | 2 | 3 Any ideas why this is or if there is another approach? Thanks, Dan |
