Concatenate tsvectors with a custom position offset

Question

The tsvector concatenation operator (tsvector || tsvector), as the official documentation states, combines the lexemes and positional information of given vectors. Specifically:

Positions appearing in the right-hand vector are offset by the largest position mentioned in the left-hand vector

So this:

select 'one:10'::tsvector || 'two:5'::tsvector;

gives

'one':10 'two':15

But what if I need a larger offset between the concatenated phrases? Say, a 100. So it would be:

'one':10 'two':115

How can I achieve that?

Answer 1

One way would be to create a helper function to offset vectors positions. It can easily be implemented by using the same concatenation operator on a vector with a placeholder lexeme and a hardcoded position equal to the desired offset, and then deleting the placeholder lexeme from the resulting vector.

CREATE FUNCTION tsvector_offset(tsvector, int) RETURNS tsvector
    AS $$select ts_delete(('$#%FAKE_LEXEM%#$:' || $2)::tsvector || $1, '$#%FAKE_LEXEM%#$');$$
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

Now you can use it like this:

select 'one:10'::tsvector || tsvector_offset('two:5'::tsvector, 100);

And get the desired result:

'one':10 'two':115