tsvector列上的postgresql触发器获取错误:列“不存在” [英] postgresql trigger on tsvector column get ERROR: column "does not exist"
问题描述
以下是我的模式
列|键入
-------------------------- + ------------------ ----------
id |整数
title |字符变化(255)
总结|字符变化(255)
readable_content |文本
created_at |不带时区的时间戳
updated_at |没有时区的时间戳
textsearchable_index_col | tsvector
$ b $索引:
site_articles_pkeyPRIMARY KEY,btree(id)
index_site_articles_on_textsearchable_index_colgin(textsearchable_index_col)
触发器:
site_articles_before_insert_update_row_tr BEFORE INSERT OR更新on site_articles FOR EACH ROW EXECUTE PROCEDURE site_articles_before_insert_update_row_tr()
以下是触发器函数:
$ prefid =lang-sql prettyprint-override>
CREATE FUNCTION site_articles_before_insert_update_row_tr()RETURNS触发器
LANGUAGE plpgsql
AS $$
BEGIN
new.tsv:= tsvector_update_trigger(textsearchable_index_col,'pg_catalog.simple',title,summary,readable_content);
new.tsv:= setweight(to_tsvector('pg_catalog.simple',coalesce(new.title,'')),'A')||
setweight(to_tsvector('pg_catalog.simple',coalesce(new.summary,'')),'B')||
setweight(to_tsvector('pg_catalog.simple',coalesce(new.readable_content,'')),'C');
RETURN NEW;
END;
$$;
然而,当我更新这样的记录时:
UPDATEsite_articlesSETupdated_at='2013-12-13 05:43:59.802580'WHEREsite_articles。id= 1
我得到
错误:列textsearchable_index_col不存在
LINE 1:SELECT tsvector_update_trigger(textsearchable_index_col,'pg ...
^
QUERY:SELECT tsvector_update_trigger(textsearchable_index_col,'pg_catalog .simple',title,summary,readable_content)
我很确定列名是正确的。不知道是否重要,我在添加tsvector列(我正在使用Rails迁移)之后像这样连接这些行
def up
add_column:site_articles,:textsearchable_index_col,:tsvector
sql =<< -SQ L
UPDATE site_articles SET textsearchable_index_col =
to_tsvector('simple',coalesce(site_articles。title:: text,'')
|| ''|| coalesce(site_articles。summary:: text,'')
|| ''|| coalesce(site_articles。readable_content:: text,'')
);
SQL
执行sql
add_index:site_articles,:textsearchable_index_col,using:'gin'
end
我错过了什么,或者每列应该有它自己的tsvector列(不是连在一起)?
确实。它应该是 new.textsearchable_index_col
。对于后续的字段也是如此。
(顺便说一下,分配两次tsv没什么意义,可以使用你的第一个(更正的)语句,或者你的第二个。第二个覆盖第一个,并且tsv计算起来很昂贵。)
Here's my schema
Column | Type
--------------------------+----------------------------
id | integer
title | character varying(255)
summary | character varying(255)
readable_content | text
created_at | timestamp without time zone
updated_at | timestamp without time zone
textsearchable_index_col | tsvector
Indexes:
"site_articles_pkey" PRIMARY KEY, btree (id)
"index_site_articles_on_textsearchable_index_col" gin (textsearchable_index_col)
Triggers:
site_articles_before_insert_update_row_tr BEFORE INSERT OR UPDATE ON site_articles FOR EACH ROW EXECUTE PROCEDURE site_articles_before_insert_update_row_tr()
and here's the trigger function:
CREATE FUNCTION site_articles_before_insert_update_row_tr() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
new.tsv := tsvector_update_trigger(textsearchable_index_col, 'pg_catalog.simple', title, summary, readable_content);
new.tsv := setweight(to_tsvector('pg_catalog.simple', coalesce(new.title,'')), 'A') ||
setweight(to_tsvector('pg_catalog.simple', coalesce(new.summary,'')), 'B') ||
setweight(to_tsvector('pg_catalog.simple', coalesce(new.readable_content,'')), 'C');
RETURN NEW;
END;
$$;
However, when I update a record like this:
UPDATE "site_articles" SET "updated_at" = '2013-12-13 05:43:59.802580' WHERE "site_articles"."id" = 1
I get
ERROR: column "textsearchable_index_col" does not exist
LINE 1: SELECT tsvector_update_trigger(textsearchable_index_col, 'pg...
^
QUERY: SELECT tsvector_update_trigger(textsearchable_index_col, 'pg_catalog.simple', title, summary, readable_content)
I'm pretty sure the column name is correct. Not sure if it matters, I concatenate the rows like this after I added the tsvector column (I'm using Rails migration)
def up
add_column :site_articles, :textsearchable_index_col, :tsvector
sql = <<-SQL
UPDATE site_articles SET textsearchable_index_col =
to_tsvector('simple', coalesce("site_articles"."title"::text,'')
|| ' ' || coalesce("site_articles"."summary"::text, '')
|| ' ' || coalesce("site_articles"."readable_content"::text, '')
);
SQL
execute sql
add_index :site_articles, :textsearchable_index_col, using: 'gin'
end
Did I miss something or each column should have its own tsvector column (not concatenated in one)?
Indeed. It should be new.textsearchable_index_col
. The same for subsequent fields.
(Btw, there's little point in assigning tsv twice. Either use your first (corrected) statement, or your second. But not both, since the second overrides the first, and a tsv is expensive to compute.)
这篇关于tsvector列上的postgresql触发器获取错误:列“不存在”的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!