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();
update ordering."order" SET tsv = NULL;BTWorderis a bad name for a table, since it is a keyword in SQL