CODEWITHSUNDEEP
postgresql
quambo
quambo

Reputation: 480

Postgres Full Text Search customize tsvector conversion

I want to build a tsvector based on the id (1-111.1x) and the name fields of my row, all from a trigger and with tsvector_update_trigger(tsv, 'pg_catalog.german', id, name).

But my id ends up cut off like '-111.1' instead of '1-111.1x'.

Is there a way to customize the conversion, so that the id field is being retained (or to apply certain operations like lower()), all while the name field is properly converted?

Something like this (which doesn't work, as setweight expects a tsvector)?

CREATE FUNCTION tsv_trigger() RETURNS trigger AS $$
begin
  new.tsv :=
     setweight(new.id, 'A') ||
     setweight(to_tsvector('pg_catalog.german', coalesce(new.name,'')), 'D');
  return new;
end
$$ LANGUAGE plpgsql;

CREATE TRIGGER TS_tsv
  BEFORE INSERT OR UPDATE ON "model"
FOR EACH ROW EXECUTE PROCEDURE
  tsv_trigger();

Thanks!

Upvotes: 0

Views: 364

Answers (1)

quambo
quambo

Reputation: 480

I ended up creating another field '_id', that is a normalized represantation of 'id' (so instead of '1-111' -> '1111'). Search input must then be stripped of '-', '.', replacing these with empty strings.

Of course we need to be careful, as sometimes stripping certain characters might not be suitable; I've created a regex pattern that only strips them within matching ids, but not within text.

In my case, this seems like a viable solution, though its merely a workaround. I'd be happy to flag a real solution.

Upvotes: 1

Related Questions