Open slimnate opened 9 months ago
Tried using this query:
ALTER TABLE events
ADD COLUMN search_tsv tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector(coalesce(type, '')), 'A') || ' ' ||
setweight(to_tsvector(coalesce(name, '')), 'A') || ' ' ||
setweight(to_tsvector(coalesce(application, '')), 'A') || ' ' ||
setweight(to_tsvector(coalesce(message, '')), 'A')
)
STORED
But it gives this error: errorpq: generation expression is not immutable
https://stackoverflow.com/questions/77037223/creating-a-computed-column-in-postgres-throws-generation-expression-is-not-immut#:~:text=Postgres%20doesn%27t%20allow%20you%20to%20directly%20perform%20operations,instance%20where%20the%20data%20is%20added%2C%20ie.%20immutable.
I'm going to try using insert triggers instead of a generated column (https://stackoverflow.com/a/75472090), since they aren't subject to the same immutability restrictions. however, I think this generated column should work - I have found blog posts using the same code as mine...
Thinking I might just table this issue for now, I tried creating an immutable wrapper for the setweight
function, that still didn't work (or I didn't do it right):
CREATE OR REPLACE FUNCTION setweight_immutable(
vector tsvector,
rank text
) RETURNS tsvector immutable AS
$$
BEGIN
return setweight(vector, rank);
END
$$ LANGUAGE plpgsql;
I don't think it will have a huge impact currently, and is something I want to revisit after implementing some of the more basic functionality that is still lacking
Consider implementing rankings on text search:
https://www.postgresql.org/docs/16/textsearch-controls.html#TEXTSEARCH-RANKING