CREATE OR REPLACE FUNCTION tsvector_to_text(v tsvector) RETURNS text AS $$ -- tests: -- select tsvector_to_text(''::tsvector) IS NULL; -- -> t -- select tsvector_to_text(E'foo x\\:y'::tsvector || setweight(to_tsvector('ghi'), 'A') || to_tsvector('abc def abc def def ghi')); -- -> ghi abc def abc def def ghi foo x:y DECLARE r record; rr record; e text; splitpos int; lexeme text; arpos text[]; -- lexemes with position arnopos text[]; -- lexemes without position BEGIN IF v IS NULL OR length(v) = 0 THEN RETURN NULL; END IF; FOR r IN SELECT a FROM regexp_split_to_table(v::text, ' ') AS a LOOP e := r.a; splitpos := strpos(e, ''':'); -- colon preceded by quote IF splitpos > 0 THEN -- with position lexeme := substr(e, 2, splitpos-2); FOR rr IN SELECT b FROM regexp_split_to_table(substr(e, splitpos+2), ',') AS b LOOP arpos[rtrim(rr.b, 'ABCD')::int] := lexeme; END LOOP; ELSE -- without position lexeme := btrim(e, ''''); arnopos := array_append(arnopos, lexeme); END IF; END LOOP; RETURN ' ' || array_to_string(array_cat(arpos, arnopos), ' ') || ' '; END $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;