0

The rule is already in place, and I'm hoping to fix the rule itself rather than change the whole process on how this works due to some bureaucracy I'd rather not deal with. Or in other words, I know there are other ways to achieve my goal, but I specifically want to know if it can be done somehow with a rule.

The current rule is that when an update occurs, another update fires to concatenate a few fields together. That update performed by the rule seems to call the rule again and generates an "infinite recursion" error.

Rule example:

ON UPDATE TO people DO 
UPDATE people 
SET "fullname" = concat(new."firstName", ' ', new."lastName") 
WHERE people.id = new.id

Is there any way to tell the update that the rule does to not then call the rule again? Or to flag the rule with some sort of runOnce? It's my understanding rules alter the query, so I can't do something simple like AND people.fullname != new.fullname

1
  • 1
    You will need a trigger or a generated column for that – a_horse_with_no_name Nov 25 '20 at 6:27
1

If fullname column is never altered manually (i.e. it must match firstname and lastname values always) then simply use generated column. It will be (re)calculated automatically during insert/update:

ALTER TABLE people
    DROP COLUMN fullName,
    ADD COLUMN fullName VARCHAR(200) GENERATED ALWAYS AS (firstName || ' ' || lastName) STORED;

DEMO

3
  • Can you index a generated column for searching? That seems like a great answer as long as it can be indexed. – invertedSpear Nov 25 '20 at 15:19
  • Unfortunately it looks like generated columns are not available till version 12, and we're using 9.6. Looks like I'll have to fight for one change or another here. Thank you – invertedSpear Nov 25 '20 at 15:31
  • Accepting this answer as it absolutely would have been the solution for me if we were using an up to date version. But Since we're not, I had to make everything trigger/function based. – invertedSpear Nov 25 '20 at 17:49
-1

If you truly want it to only run once for that particular record, you could make fullname a nullable field and change the rule to:

ON UPDATE TO people DO 
UPDATE people 
SET "fullname" = concat(new."firstName", ' ', new."lastName") 
WHERE people.id = new.id
    AND fullname IS NULL
2
  • That will still cause the error message about infinite recursion. Rules are not procedural. – Laurenz Albe Nov 25 '20 at 11:15
  • 1
    Ah ok I've never used Rules so I didn't know the check for recursion was upfront. It's likely just checking that the Rule isn't self referencing which then I think what you're requesting is impossible. You would unfortunately have to use an alternative like a computed column, a view, or a trigger, etc. Rules are very much recommended against these days. I know you said you're trying to avoid the bureaucracy of changing the process but would it be easier if you provided proof of how Rules are a bad design choice by most recommendations, or can you sidestep the whole thing by making a view, etc? – J.D. Nov 25 '20 at 12:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.