I am trying to create a trigger in postgres to update a field of a newly inserted record with a value returned from a query of another table in the same database. The function below is my attempt of doing this, but it seems to get stuck in a loop whenever i try to run this trigger. I am also concerned if this will add a lot of overhead to my database.
I appreciate any help given.
CREATE OR REPLACE FUNCTION billingblock() RETURNS trigger AS '
SET billingblock = (select code from routing_table
where dialed_number like routing_table.code || '%' order by routing_table.code desc limit 1);
' LANGUAGE 'plpgsql'