I'm trying to create some rules for my DB to prevent duplicate inserts, but since we're also hibernate, the .save() is called insert xxx RETURNING *
I can't seem to make the insert rule play well with the insert returning.
CREATE TABLE test ( aaa int, bbb int);
insert into test VALUES(1, 2);
CREATE RULE "testRule1" AS ON INSERT TO test
WHERE EXISTS(SELECT 1 FROM test
WHERE (aaa, bbb)=(NEW.aaa, NEW.bbb))
DO INSTEAD NOTHING;
If I try to insert with just 1 conditional rule
insert into test VALUES(1, 2);
ERROR: cannot perform INSERT RETURNING on relation "test"
HINT: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.
CREATE RULE "testRule2" AS ON INSERT TO test
DO INSTEAD INSERT INTO test VALUES(new.*)
RETURNING test.*;
If I try to insert with both conditional and unconditional rules
insert into test VALUES(1, 2);
ERROR: infinite recursion detected in rules for relation "test"
Is this impossible to achieve with my setup?