Hello!
There is a bug in one of the database modules I use and sometimes it inserts 'undefined' into the database. I want to create a rule that will replace one of the values in the row with another. I've tried this:
CREATE RULE replace_undefined AS ON INSERT TO telegraf_session
WHERE NEW.session = 'undefined'
DO INSTEAD INSERT INTO telegraf_session VALUES(NEW.id, REPLACE(NEW.session, 'undefined', '{}');
But on inserting with this rule I get ERROR: infinite recursion detected in rules for relation "telegraf_session"
What am I doing wrong? I thing the condition isn't respected, but I'm not sure why.
Thanks!
Top comments (3)
Your rule on insert triggers a rule on insert which triggers a rule on insert which triggers a rule on insert :-)
You might want to use a view if the goal is to have the app see
{}
instead ofundefined
or you could look into a trigger.This works on PostgreSQL 11:
and this is the output:
Thanks a ton!
Do I understand correctly that it's impossible to have a rule on insert that does an insert to the same table as no matter the condition it will still trigger itself before the condition is checked thus giving the infinite recursion error?
I'm not 100% certain but as far as I know, yes it's not possible. The purpose of rules is to rewrite queries, sort of a macro system: "you write X and I write y". If the source and the target are the same, the rule itself gets re-activated.
A trigger instead is a precondition (or post condition) to a query.