Search code examples
postgresqlsql-updaterule

PostgreSQL : update only inserted row

I need to set up a 'ON INSERT' rule that would update a column in the inserted row (and not update the column for the hole set of entries) :

CREATE OR REPLACE RULE _myrule AS ON INSERT TO myTable DO UPDATE myTable SET col1 = 'test' ;

The above statement will set the 'test' string for all entries... I can't find out how to specify this in the rule syntax.

Thanks in advance for help !

Solution

  • 1) FIRST SOLUTION (using triggers)

    CREATE OR REPLACE FUNCTION myRule() RETURNS trigger as $myRule$
   BEGIN
    NEW.col1 := 'test';
    RETURN NEW;
  END;
$myRule$
LANGUAGE plpgsql;



 -- DROP  TRIGGER myRule ON "myTable";

 CREATE TRIGGER myRule 
 BEFORE INSERT 
 ON "myTable"
 FOR EACH ROW 
 EXECUTE PROCEDURE myRule();

    2) SECOND SOLUTION (using rules)

    id - PUT HERE Your PK name

    CREATE OR REPLACE RULE  myTable_ins AS ON INSERT TO "myTable"
  DO ALSO
    UPDATE "myTable" SET 
        col1 = 'test'
    WHERE id = NEW.id       
;