I have three modules, a Business which is associated Tags and Categories though join tables business_tags and business_categories. I intent to improve search hits on the business using tsvector on the database and I have a column searchable on the business which initially was built from the business’ name and description. I want to extend this to the tags and categories such that when tags and categories are assigned to the categories, these would be integrated into the searchable column.

Here’s my migration

execute("""

      CREATE OR REPLACE FUNCTION public.businesses_searchable_trigger()
       RETURNS trigger
       LANGUAGE plpgsql
      AS $function$ begin
      update
    businesses b
      set
    searchable = setweight(to_tsvector('pg_catalog.english', coalesce(b.name, '')), 'A') || setweight(to_tsvector('pg_catalog.english', coalesce(b.description, '')), 'D') || setweight(to_tsvector('pg_catalog.english', coalesce(( select string_agg(distinct t.name, ' ') from business_tags bt join tags t on t.id = bt.tag_id where bt.business_id = b.id ), '') ), 'C') || setweight(to_tsvector('pg_catalog.english', coalesce(( select string_agg(distinct c.name, ' ') from business_categories bc join categories c on c.id = bc.category_id where bc.business_id = b.id ), '') ), 'C')
      where
    b.id = coalesce(NEW.business_id, OLD.business_id);

      return coalesce(new, old);
      end;

      $function$
      ;
    """)

    # Triggers on join tables
    execute("""
    CREATE OR REPLACE TRIGGER business_tags_rebuild_searchable_trigger AFTER INSERT OR UPDATE OR DELETE ON business_tags FOR EACH ROW EXECUTE FUNCTION businesses_searchable_trigger();
    """)

    execute("""
    CREATE OR REPLACE TRIGGER business_categories_rebuild_search_trigger
    AFTER INSERT OR UPDATE OR DELETE ON business_categories
    FOR EACH ROW
      EXECUTE PROCEDURE businesses_searchable_trigger();
    """)

When I attempt to insert a business, it breaks with the following error message:

error] ** (Postgrex.Error) ERROR 42703 (undefined_column) record “new” has no field “business_id”

I will appreciate any form of guidance