CODEWITHSUNDEEP
postgresqlrules
Shiwangini
Shiwangini

Reputation: 836

'Where' condition on the combination of multiple columns for a rule

I'm new to postgres.I need to create Rule on one of my table in postgres. I'm using postgres 11.4.

Here, is the syntax i'm using for this:

      CREATE RULE flagrule AS ON insert TO ms
  WHERE NEW.a = OLD.a and new.b = old.b and new.c = old.c and
new.d = old.d and new.e = old.e

DO instead
update ms set flag = 0  where 
a = NEW.a and
b= new.b and
c = new.c and
d = new.d and
e = new.e;

and getting below error:

     SQL Error [42P01]: ERROR: invalid reference to FROM-clause entry for 
   table "old"
    Hint: There is an entry for table "old", but it cannot be referenced 
      from this part of the query.
     Position: 76

I google about this a lot, but I got example only with single column condition in 'where' clause. If any one has any idea about this then please let me know. Any help will be appreciated.

Upvotes: 0

Views: 309

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

The documentation states:

Within condition and command, the special table names NEW and OLD can be used to refer to values in the referenced table. NEW is valid in ON INSERT and ON UPDATE rules to refer to the new row being inserted or updated. OLD is valid in ON UPDATE and ON DELETE rules to refer to the existing row being updated or deleted.

Since you are trying to create an ON INSERT rule, you cannot use OLD. What should that mean anyway?

Upvotes: 1

Related Questions