Postgres Full Text Search customize tsvector conversion

Question

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!

quambo · Accepted Answer · 2015-11-20 17:55:36Z

1

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.

answered Nov 20, 2015 at 17:55

quambo

4805 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Full Text Search customize tsvector conversion

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related