Combining tsvectors from multiple rows into one

Question

I have a table with a text field and a tsvector containing the search index for that field:

CREATE TABLE test (pk bigint, value text, tsv tsvector);

How do I create a single tsvector that is the combination of the vectors for all rows satisfying some condition?

SELECT value FROM test
WHERE combine_my_vectors(SELECT tsv FROM test WHERE pk IN (some list))
      @@ to_tsquery('search me');

I know that tsvectors can be combined with ||, but that doesn't seem to be possible here. What do I use for combine_my_vectors?

I would prefer to avoid having to combine my value text fields first, and then create a tsvector out of them.

Valediction

What is the point of that? You'd have to create your own aggregate function. – Shearer

The point is that the resulting vector is used as a part of a larger one, and the system cares that only one result is returned (as opposed to one result per row) – Valediction

@LaurenzAlbe If there's a better way to achieve that though, I'm open to suggestions. – Valediction

Answer 1

The nice thing about PostgreSQL is that it is so extensible.

You can simply define your own aggregate for this:

CREATE AGGREGATE tsvector_agg(tsvector) (
   STYPE = pg_catalog.tsvector,
   SFUNC = pg_catalog.tsvector_concat,
   INITCOND = ''
);

You can use it like this:

SELECT tsvector_agg(tsv) FROM test;

Of course you cannot use a full text search index for this, because the matched value will not belong to a single row.

Shearer