PostgreSQL : update only inserted row

Question

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 !

you need to add something like `where pk_column = new.pk_column` to limit the update. There is an example for that in the manual: http://www.postgresql.org/docs/current/static/rules-update.html (you need to scroll down quite a bit) — , Aug 22 '14 at 12:32
In almost all cases you should be using triggers, not rules, for this sort of thing. They're a *lot* easier to get right. — Craig Ringer, Aug 23 '14 at 14:45

Andrzej Reduta · Accepted Answer · 2014-08-22T14:26:31.627

0

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       
;

edited Aug 22 '14 at 14:26

answered Aug 22 '14 at 12:43

Andrzej Reduta

767
1
7
15

1

He/She wants to turn an `insert` into an `update` - not supply a default value during instert. Additionally: `"test"` is a column name, not a string literal. `'test'` is a string literal. – Aug 22 '14 at 12:56
No, I want to turn an `update` into an `insert` ! The @a_horse_with_no_name solution seems on the right way but it doesn't work for me : `UPDATE myTable SET col1 = 'test' WHERE gid = NEW.gid ;`. The `col1` remains empty after the INSERT is run. – wiltomap Aug 22 '14 at 13:26
1

@wiltomap: "*I want to turn an update into an insert*" contradicts the code you have shown in your question, which turns an insert into an update. But again: for both problems there are examples in the Postgres manual. – Aug 22 '14 at 13:30
In solution i gave You, after inserting new rows You will have value 'test' in col1 in this new rows. Does it meet Your needs? – Andrzej Reduta Aug 22 '14 at 13:36
Thank you @Andrzej, but I would prefer a simplier way to treat that. What I want to do is well what I put in my question, sorry about misunderstanding. I can't find out the example in the manual. What is wrong in my previous comment statement ? – wiltomap Aug 22 '14 at 13:55
For the things that can be implemented by both, which is bestdepends on the usage of the database. A trigger is fired for any affected row once. A rule manipulates the query or generates an additional query. So if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that is called for every single row and must execute its operations many times. However, the trigger approach is conceptually far simpler than the rule approach, and is easier for novices to get r http://stackoverflow.com/questions/5894142/what-are-postgresql-rules-good-for – Andrzej Reduta Aug 22 '14 at 14:29

PostgreSQL : update only inserted row

1 Answers1