英文:
Generated column of tsvector for text array in Postgres (Supabase)
问题
在Postgres（Supabase）中，我试图从包含短标题变体文本数组的另一列自动生成一列。
tsvector正常工作，如预期一样。另一种可能性是使用array_to_tsvector，但这不是一个选项，因为短标题文本数组中包含的不仅仅是单词，还有短标题的变体（句子）。
但我遇到了这个错误：
运行SQL查询失败：生成表达式不是不变的
另一方面，当为不同语言的全标题的JSONB添加此列时，我成功了。
非常感谢任何提示和帮助。
SQL对我来说相当新，以前只用过MongoDB，所以对我的问题表示歉意。
英文:
in Postgres (Supabase) I am trying to automatically generate a column from another column which contains a text array of short title variants.
The tsvector works just fine and as expected. The other possibility which would be to use array_to_tsvector is not an option as short title text array contains not just single words but variants of short titles (sentences).
alter table "MOVIES"
add column fts_short_title
tsvector GENERATED ALWAYS AS (
to_tsvector('simple',
array_to_string( title_short,' '::text))
) STORED;
but I get this error
Failed to run sql query: generation expression is not immutable
On the other hand I was successful when adding such a column for JSONB of full titles for different languages
alter table "MOVIES"
add column fts
tsvector GENERATED ALWAYS AS (
to_tsvector('simple',
coalesce(title->>'en', '') || ' ' ||
coalesce(title->>'de', '') || ' ' ||
coalesce(title->>'it', '') || ' ' ||
coalesce(title->>'fr', ''))
) STORED;
Thank you very much for any tip and help.
.. SQL is rather new to me, have used only from MongoDB previously, so sorry for my question.
答案1
得分: 2
您可以为原本不可变的函数定义“immutable”包装器。在线演示
create or replace function array_to_string_immutable (
arg text[],
separator text,
null_string text default null)
returns text immutable parallel safe language sql as $$
select array_to_string(arg,separator,null_string) $$;
alter table "MOVIES"
add column fts_short_title
tsvector GENERATED ALWAYS AS (
to_tsvector('simple',
array_to_string_immutable( title_short,' '::text))
) STORED;
table "MOVIES";
尽管位于 || 运算符后面的 textcat() 函数是不可变的，但我非常确定 array_to_string() 仅为 stable，原因与 concat() 相同，因此您需要在使用此解决方法时要相当小心。
您可以对其他列执行相同的操作，使用 concat_ws()，避免重复的 ||' '||coalesce()：
create or replace function concat_ws_immutable (
separator text,
variadic arg text[])
returns text immutable parallel safe language sql as $$
select concat_ws(separator,variadic arg) $$;
alter table "MOVIES"
add column fts
tsvector GENERATED ALWAYS AS (
to_tsvector('simple',concat_ws_immutable(' ',title->>'en',title->>'de',title->>'it',title->>'fr'))
) STORED;
您也可以自由地以您喜欢的方式对列进行操作，可以使用 plpgsql 函数，该函数由 trigger after insert or update on "MOVIES" 使用。
英文:
You could define immutable wrappers for otherwise non-immutable functions. online demo
create or replace function array_to_string_immutable (
arg text[],
separator text,
null_string text default null)
returns text immutable parallel safe language sql as $$
select array_to_string(arg,separator,null_string) $$;
alter table "MOVIES"
add column fts_short_title
tsvector GENERATED ALWAYS AS (
to_tsvector('simple',
array_to_string_immutable( title_short,' '::text))
) STORED;
table "MOVIES";
While the textcat() function behind || operator is immutable, I'm pretty sure array_to_string() is only stable for the same reason concat() is so you need to be reasonably careful with where you use this workaround.
You could do the same for the other column to use a concat_ws() and avoid the repeated ||' '||coalesce():
create or replace function concat_ws_immutable (
separator text,
variadic arg text[])
returns text immutable parallel safe language sql as $$
select concat_ws(separator,variadic arg) $$;
alter table "MOVIES"
add column fts
tsvector GENERATED ALWAYS AS (
to_tsvector('simple',concat_ws_immutable(' ',title->>'en',title->>'de',title->>'it',title->>'fr'))
) STORED;
You are also free to do pretty much whatever you want, however you want to the column in a plpgsql function used by a trigger after insert or update on "MOVIES".
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论