CODEWITHSUNDEEP
postgresqltriggersfull-text-search
Josh C.
Josh C.

Reputation: 4373

Trigger not updating tsvector on insert/update

I'm trying to munge data from a few columns across a few tables to make it full-text searchable, but I'm not having much luck.

Here's my function:

CREATE OR REPLACE FUNCTION on_customer_save_udpate_tsv() RETURNS trigger AS $$ 
declare 
   tsv_text text; 
begin 
SELECT string_agg(cust_text.text, ' ') as agg_text into tsv_text 
FROM (SELECT concat("name", ' ', "phone") as text 
   FROM "Customers" 
   where "id" = NEW.id 
   UNION 
   SELECT concat("firstName", ' ', "lastName", ' ', "phone", ' ', "email") as text 
   FROM "Contacts" 
   where "customerId" = NEW.id 
   UNION 
   SELECT concat("streetLine1", ' ', "city", ' ', "state", ' ', "zip") as text 
   FROM "Addresses" 
   where "customerId" = NEW.id) cust_text; 
NEW.tsv := to_tsvector(coalesce(tsv_text,'')); 
return NEW; 
end 
$$ LANGUAGE plpgsql;

Here's my trigger:

CREATE TRIGGER cust_tsv_trigger BEFORE INSERT OR UPDATE 
ON "Customers" FOR EACH ROW EXECUTE PROCEDURE on_customer_save_udpate_tsv();

However, the "Customers".tsv column is blank after inserts/updates.

When I run the bulk of the function manually, it works as expected.

DO $$ 
declare 
   tsv_text text; 
begin 
SELECT string_agg(cust_text.text, ' ') as agg_text into tsv_text 
FROM (SELECT concat("name", ' ', "phone") as text 
   FROM "Customers" 
   where "id" = 17 
   UNION 
   SELECT concat("firstName", ' ', "lastName", ' ', "phone", ' ', "email") as text 
   FROM "Contacts" 
   where "customerId" = 17
   UNION 
   SELECT concat("streetLine1", ' ', "city", ' ', "state", ' ', "zip") as text 
   FROM "Addresses" 
   where "customerId" = 17) cust_text; 

   UPDATE "Customers"
   SET tsv = to_tsvector(coalesce(tsv_text, ''))
   WHERE "id" = 17;
end 
$$ LANGUAGE plpgsql;

I'm fairly new to postgres. What am I missing?

Upvotes: 2

Views: 969

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61676

As the trigger runs BEFORE INSERT or UPDATE, having it select from the table to get the new values is flawed, because the new values are not there already.

Consider this part of the query:

SELECT concat("name", ' ', "phone") as text 
   FROM "Customers" 
   where "id" = NEW.id

In the case of an INSERT, you'll always get NULL here and in the case of an UPDATE, you'll get the values that are about to be replaced rather than the new ones.

What should be used as a drop-in replacement for the code above is:

SELECT (NEW.name || ' ' || NEW.phone) AS text

Another point that looks suspicious is: if any of the fields in the user details from any of the 3 tables is NULL, then the whole concatened contents are going to be empty, because the code applies coalesce at the very end instead of doing it on each individual field.

Upvotes: 2

Related Questions