postgresql - Trigger not updating tsvector on insert/update -

i'm trying munge data few columns across few tables make full-text searchable, i'm not having luck.

here's function:

create or replace function on_customer_save_udpate_tsv() returns trigger $$  declare     tsv_text text;  begin  select string_agg(cust_text.text, ' ') agg_text tsv_text  (select concat("name", ' ', "phone") text     "customers"     "id" = new.id     union     select concat("firstname", ' ', "lastname", ' ', "phone", ' ', "email") text     "contacts"     "customerid" = new.id     union     select concat("streetline1", ' ', "city", ' ', "state", ' ', "zip") text     "addresses"     "customerid" = new.id) cust_text;  new.tsv := to_tsvector(coalesce(tsv_text,''));  return new;  end  $$ language plpgsql;  

here's trigger:

create trigger cust_tsv_trigger before insert or update  on "customers" each row execute procedure on_customer_save_udpate_tsv(); 

however, "customers".tsv column blank after inserts/updates.

when run bulk of function manually, works expected.

do $$  declare     tsv_text text;  begin  select string_agg(cust_text.text, ' ') agg_text tsv_text  (select concat("name", ' ', "phone") text     "customers"     "id" = 17     union     select concat("firstname", ' ', "lastname", ' ', "phone", ' ', "email") text     "contacts"     "customerid" = 17    union     select concat("streetline1", ' ', "city", ' ', "state", ' ', "zip") text     "addresses"     "customerid" = 17) cust_text;      update "customers"    set tsv = to_tsvector(coalesce(tsv_text, ''))    "id" = 17; end  $$ language plpgsql;  

i'm new postgres. missing?

as trigger runs before insert or update, having select table new values flawed, because new values not there already.

consider part of query:

select concat("name", ' ', "phone") text     "customers"     "id" = new.id  

in case of insert, you'll null here , in case of update, you'll values replaced rather new ones.

what should used drop-in replacement code above is:

select (new.name || ' ' || new.phone) text 

---

another point looks suspicious is: if of fields in user details of 3 tables null, whole concatened contents going empty, because code applies coalesce @ end instead of doing on each individual field.