Hello,
the following is a rework of what I wanted to achieve when posting
yesterday. Since that post didn't seem to attract attention, I tried
to do what I wanted to do differently.
Now, creating a RULE for a view allows defining several operations for
it. I was happy to discover that actually and quickly rewrote my rule
to look this way:
CREATE OR REPLACE RULE "_UPDATE_not_existing" AS
ON UPDATE TO trade_material_view
WHERE NOT material_exists(new.diameter, new.material_length, new.weight, new.loss, new.bar_type_id,
new.metal_type_id)
DO (
INSERT INTO material (id, diameter, material_length, weight, loss, bar_type_id, metal_type_id)
VALUES (nextval('material_id_seq'::regclass), new.diameter, new.material_length, new.weight, new.loss,
new.bar_type_id,new.metal_type_id);
INSERT INTO trade_material (material_id)
VALUES (currval('material_id_seq'::regclass)
);
);
material_exists is my own, boolean-returning, custom function. Works
by checking whether a given material (with given characteristics)
exists in the appropriate table.
The problem is, PostgreSQL just performs the first INSERT and happily
ignores the rest. As much as I hate swearing, I shouted one large WFT
on this one...
Can someone PLEASE explain to me what's happening? First insert works
like a charm, second one is non-existent to Postgres. I can replace
the second INSERT with any other command, including one deleting
everything in say 'trade_material' and it doesn't work anyway.
--
Best regards,
Michal mailto:mpaluchowski@gmail.com