2

Very simple question indeed. Say I have a trigger to convert some text fields coming from other tables to tsv indexes so I can implement a full text search in a performatic way.

To achieve that, I have:

  • Created a function which performs my desired SQL query (works)
  • Created a trigger that calls that function when my ordering.order table gets updated (works)
  • Tried to reuse that same function before the trigger, because I want old data to be updated as well ( doesn't work )

migration failed: trigger functions can only be called as triggers

In short, is there any return type in a postgres function that allows me both to use it in a trigger and to be called in other places as a normal function? Thank you very much.

  CREATE OR REPLACE FUNCTION index_text_fields() RETURNS trigger AS $$
      BEGIN
         UPDATE ordering.order as uo SET tsv = setweight(to_tsvector
        (coalesce(s.name, '')), 'A')
        || setweight(to_tsvector(coalesce(split_part(o.order_ref,'-',2),'')), 'A')
        FROM ordering.order AS o  
          LEFT JOIN ordering.sender AS s  USING (order_id) 
          LEFT JOIN ordering.receiver AS r  USING (order_id)
        WHERE uo.order_id = o.order_id;
        RETURN NULL;
      END  
  $$ LANGUAGE plpgsql;

  DO $$ BEGIN
    PERFORM index_text_fields();
  END $$;


  CREATE TRIGGER orders_trigger AFTER
    INSERT OR UPDATE
      ON ordering.order FOR EACH ROW
        WHEN (pg_trigger_depth() < 1)
          EXECUTE PROCEDURE index_text_fields();
1
  • A way to invoke the trigger function is to perform a (dummy) update on (some rows of) the table, for instance update ordering."order" SET tsv = NULL; BTW order is a bad name for a table, since it is a keyword in SQL Commented Sep 23, 2019 at 11:50

1 Answer 1

1

It doesn't make sense to use the same function for these two tasks.

  • For the trigger, you'd use a BEFORE trigger, and the trigger function doesn't modify the table, but assign to NEW.tsv and return NEW.

  • For updating the existing data, you need an UPDATE statement.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.