Convert tsvector column back to a TEXT column?

Question

Is it possible to convert a Postgres column of type tsvector back to a regular TEXT column?

I assume it's lossy, but how can I create a new column from my existing tsvector column with type TEXT? Having it create multiple variants of words in the TEXT column is fine.

All of my inserts to the existing column were done with to_tsvector so I don't have the original raw TEXT.

I can do tsvector_to_array to convert a tsvector back to a string array. I can then convert the array to a string with array_to_string.

e.g:

SELECT array_to_string(tsvector_to_array(ts_column), ' ') FROM mytable

However this doesn't generate the full words, only the lexemes (and I'd want the full words even if lossy).

Answer 1

Here ya go. It works for all my test cases.

CREATE OR REPLACE FUNCTION tsvector_to_string(input tsvector)
RETURNS text AS $$
DECLARE
  result text := '';
  lexemes text[];
  indices int[];
  lexeme text;
  index int;
BEGIN
  FOR lexeme, indices IN
    SELECT alias, string_to_array(word, ',')::int[]
    FROM
    (SELECT
      trim(both '''' from split_part(word, ':', 1)) as alias,
      trim(trailing '}' from trim(leading '{' from split_part(word, ':', 2))) as word
     FROM
     unnest(string_to_array(input::text, ' ')) as word) as subquery
  LOOP
    FOREACH index IN ARRAY indices
    LOOP
      result := result || ' ' || lexeme;
    END LOOP;
  END LOOP;
  RETURN trim(leading ' ' from result);
END
$$ LANGUAGE plpgsql;

As a note, I am a total PL/pgSQL novice. But I just wrote the logic in Javascript and then ChatGPT obligingly converted it to PL/pgSQL. It's a crazy world we live in nowadays. Javascript implementation for reference:

function convertString(str) {
      const indexToString = {};
      str.split(" ").forEach((val, i) => {
        const [lexeme, indicesStr] = val.split(":");
        const indices = indicesStr.split(",");
        indices.forEach(index => {
          indexToString[index] = lexeme.replace(/'/g, "");
        });
      });

      return Object.keys(indexToString)
        .sort()
        .map(index => indexToString[index])
        .join(" ");
    }