PostgreSQL Trigger function won't work on insert row for text search column

Question

I have a pgsql trigger and a trigger function. Works when updating a previous record and does not work when inserting a new record. Ideally, the text_search column should be populated with the tsvector data mentioned.

The id column of the table idea is of bigserial and some of the other fields added to text_search are nullable if that's important.

Trigger

CREATE TRIGGER update_idea_text_search
    BEFORE INSERT OR UPDATE 
    ON prod.idea
    FOR EACH ROW
    EXECUTE FUNCTION prod.update_idea_text_search();

Trigger Function

CREATE OR REPLACE FUNCTION prod.update_idea_text_search()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
 
DECLARE text_search TSVECTOR;
BEGIN
   SELECT
 ((
       setweight(to_tsvector('english'::regconfig, COALESCE(title, ''::text)), 'A'::"char") ||
       setweight(to_tsvector('english'::regconfig, COALESCE(subtitle, ''::text)), 'A'::"char") ||
       setweight(to_tsvector('english'::regconfig, COALESCE(strategy, ''::text)), 'A'::"char") ||
       setweight(to_tsvector('english'::regconfig, COALESCE(summary, ''::text)), 'B'::"char") ||
       setweight(to_tsvector('english'::regconfig, COALESCE(conditions, ''::text)), 'B'::"char") ||
       setweight(to_tsvector('english'::regconfig, COALESCE(strategy_data::text, ''::text)), 'B'::"char") ||
       setweight(to_tsvector('english'::regconfig, COALESCE(tags::text, ''::text)), 'A'::"char") ||
       setweight(to_tsvector('english'::regconfig, COALESCE(id::text, ''::text)), 'B'::"char") ||
       setweight(to_tsvector('english'::regconfig, COALESCE(buy::text, ''::text)), 'C'::"char") ||
       setweight(to_tsvector('english'::regconfig, COALESCE(sell::text, ''::text)), 'C'::"char")
    ))
 INTO text_search 
   FROM
      prod.idea
   WHERE
      idea.id = NEW.id;
                                                                                                    
NEW.text_search := coalesce(text_search, '');
                                                                                                    
RETURN NEW;
END
$BODY$;

If any more information is required let me know and other comments/suggestions are welcome!

This `BEFORE` and `idea.id = NEW.id;` are not going to work for an INSERT as the `id` value does not exist yet in the `prod.idea` table. — Adrian Klaver, May 07 '23 at 16:09
Just assign the to_tsvector() output directly to `text_search`. Probably easiest way is to eliminate the `... FROM prod.idea WHERE idea.id = NEW.id` portion of the query. — Adrian Klaver, May 07 '23 at 16:17
Just realized I should have added that the `COALESCE(title, ...)` portions will need to specify the `NEW` record, so `COALESCE(NEW.title, ...)`. — Adrian Klaver, May 07 '23 at 16:43
Yes you are correct it worked! Removed prod.idea table portion and added new in front of the incoming parameters title, subtitle ..etc. Post it as an answer I'll accept it — LordDraagon, May 08 '23 at 03:48
*"... some of the other fields added to text_search are nullable if that's important"* Yes, that's important. Lead with a `CREATE TABLE` statement showing data types and constraints. And *always* your version of Postgres. — Erwin Brandstetter, May 10 '23 at 02:34

score 0 · Answer 1 · answered May 10 '23 at 03:26

Adrian already helped with the syntax issues in your trigger function.

More fundamentally - in Postgres 12 or newer - use a STORED generated column instead of the trigger solution. Much cheaper and much less error prone! See:

Computed / calculated / virtual / derived columns in PostgreSQL

So your table definition looks like:

CREATE TABLE idea (
  id bigserial PRIMARY KEY
, title text
, subtitle text
-- , more ...
, text_search tsvector GENERATED ALWAYS AS (
      setweight(to_tsvector('english', COALESCE(title     , '')), 'A')
   || setweight(to_tsvector('english', COALESCE(subtitle  , '')), 'B')
-- || more ...
   ) STORED  -- !!
);

COALESCE only for nullable columns, obviously.
The text search index you'll have with it:

CREATE INDEX idea_text_search_gin_idx ON idea USING GIN (text_search);

fiddle

Your queries stay the same.

PostgreSQL Trigger function won't work on insert row for text search column

1 Answers1