Cycloning in trigger

Question

I have table like this

 id | col1 | col2

I need write trigger so: after inserting or updating column col1 , I need insert (update) same value in column col2

This is my trigger function:

CREATE FUNCTION  upd () RETURNS TRIGGER AS '
   BEGIN 
       UPDATE mytable SET
       col2 =  col1;
       RETURN NULL;
   END;
'
LANGUAGE plpgsql;

and this is self trigger:

CREATE TRIGGER upd_trigger
AFTER INSERT OR UPDATE ON mytable
FOR EACH ROW
EXECUTE PROCEDURE upd()

This not works because happening cycloning at UPDATE event, right?

What is right syntax for do this?

score 1 · Answer 1 · 2013-05-12T14:44:06.170

You don't need an update, just assign the value:

CREATE FUNCTION  upd () RETURNS TRIGGER AS 
$body$
   BEGIN 
      new.col2 := to_tsvector(new.col1);
      RETURN new;
   END;
$body$
LANGUAGE plpgsql;

And you need to do this in the before trigger:

CREATE TRIGGER upd_trigger
BEFORE INSERT OR UPDATE ON mytable
FOR EACH ROW
EXECUTE PROCEDURE upd()

Edit:

As @DavidAldridge has pointed out in his comment, it's probably easier to index the ts_vector() directly:

create index col1_vector 
    on mytable (to_tsvector(col1)) 
    using gin;

A query using

where to_tsvector(col1) = '...'

would then be able to use that index.

score 1 · Accepted Answer · answered May 12 '13 at 22:07

1

@a_horse and @David have already pointed out the superior solution with a GIN index for your case.

But one more thing concerning the answer to your original question: since you only need the trigger after inserting or updating column col1, you should be using the more specific event UPDATE OF ..., available since Postgres 9.0:

CREATE TRIGGER upd_trigger
BEFORE INSERT OR UPDATE OF col1 ON mytable
FOR EACH ROW
EXECUTE PROCEDURE upd()

This way you could even prevent endless loops / recursion ("cycloning" as you call it) in an AFTER trigger. But it's still generally more sensible to use a BEFORE trigger, like @a_horse already provided.

answered May 12 '13 at 22:07

Erwin Brandstetter

605,456
145
1,078
1,228

`UPDATE OF` really helps me, thanks. But `BEFORE` trigger not works, `AFTER` works, why? what is wrong in my trigger function? – Oto Shavadze May 13 '13 at 09:25
@OTARIKI: Hard to say. One would need the complete picture. Maybe other triggers interfere? Did you try the improved trigger function a_horse provided? – Erwin Brandstetter May 13 '13 at 12:27

score 0 · Answer 3 · answered May 12 '13 at 12:02

0

Well if the value for col2 is deterministically dependent only on that of col1 then col2 shouldn't be stored at all.

answered May 12 '13 at 12:02

David Aldridge

51,479
8
68
96

`col2` is for saving `tsvector` data, original data is saved in `col1`, (For full text search). But in question, for clearing, I dont write this. – Oto Shavadze May 12 '13 at 12:05
1

Have you looked at creating a gin index on ts_vector(col1)? – David Aldridge May 12 '13 at 14:38

Cycloning in trigger

3 Answers3

Linked