tsvector更新中的Postgresql regex

Question

我有以下tsvector列的更新触发器

CREATE TRIGGER tsvector_user_update
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE PROCEDURE 
tsvector_update_trigger(user_tsv, 'pg_catalog.english', firstname, surname, email, card_id);

这很好，但是我的card_id列(文本)包含一个用户不知道的前导码(它是在扫描卡后添加的)，所以我想在生成tsvector值时去掉前导码，我已经尝试了触发功能作为启动

CREATE FUNCTION user_change_trigger() RETURNS trigger AS $$
BEGIN
NEW.user_tsv = setweight(to_tsvector('pg_catalog.english', coalesce(NEW.firstname,'')), 'A') ||
setweight(to_tsvector('pg_catalog.english', coalesce(NEW.surname,'')), 'A') ||
setweight(to_tsvector('pg_catalog.english', coalesce(REGEXP_REPLACE(NEW.card_id, '^d+PRE', ''),'')), 'B') ||
setweight(to_tsvector('pg_catalog.english', coalesce(NEW.email,'')), 'C');
return new;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON users FOR EACH ROW EXECUTE PROCEDURE user_change_trigger();

它执行，但我得到以下内容：

WARNING:  nonstandard use of escape in a string literal

并且没有更新tsvector

前导码是一个后跟"pre"的整数。

(PostgreSQL 9.0)

Answer 1

基本触发器设计

这个问题具有主要性质。在PostgreSQL中，您创建了一个触发器函数来完成这项工作。我在问题中看不出你的触发功能。

然后创建一个触发器，该触发器使用此函数。您只能将常量传递给触发器函数。考虑一下CREATE TRIGGER手册中的这句话

函数名

用户提供的函数，声明为不带参数的并且返回型触发器，在触发器触发时执行。

自变量

要提供给函数。参数是文字字符串常量。可以编写简单的名称和数字常量这里也是，但它们都将转换为字符串。请检查触发器函数的实现语言的描述了解如何在函数中访问这些参数；它可能与正常函数参数不同。

加粗强调mine。

使用NEW访问触发器函数内的列值。你不需要把它们当作论据来传递。首先掌握基本概念。从这里开始。

regexp_replace()

用途：

regexp_replace(card_id, '^d+PRE', '')

因为前导字符应该仅是数字(以及至少一个数字)。

正确的触发器&函数

下面的测试用例适用于PostgreSQL 9.1.6上的我。你的版本在我看来基本不错，我只做了一些小改动。但继续阅读。。。

创建测试环境(将在最后回滚)：

BEGIN;
CREATE SCHEMA test;
SET search_path = test;
CREATE TABLE users (
users_id serial primary key
,firstname text
,surname text
,card_id text
,email text
,user_tsv tsvector
);

触发功能：

CREATE FUNCTION user_change_trigger()
RETURNS trigger AS
$func$
BEGIN
NEW.user_tsv :=
setweight(to_tsvector('pg_catalog.english', coalesce(NEW.firstname,'')), 'A')
|| setweight(to_tsvector('pg_catalog.english', coalesce(NEW.surname,'')), 'A')
|| setweight(to_tsvector('pg_catalog.english', coalesce(regexp_replace(NEW.card_id, '^d+PRE', ''),'')), 'B')
|| setweight(to_tsvector('pg_catalog.english', coalesce(NEW.email,'')), 'C');
RETURN NEW;
END
$func$ LANGUAGE plpgsql;

plpgsql的赋值运算符是:=，与使用=的SQL不同。

触发器：

CREATE TRIGGER tsvectorupdate
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE PROCEDURE user_change_trigger();

测试：

INSERT INTO users (firstname, surname, card_id, email)
VALUES ('Erwin', 'Brandstetter', '123PRE456', '[e-mail]')
RETURNING *;
-- looks good!
UPDATE users SET firstname = 'Walter' WHERE TRUE
RETURNING *;
-- looks good, too!

清理：

ROLLBACK;

standard_conforming_strings

探索standard_conforming_strings的设置。警告提示您没有打开此设置，这将要求您将中的反斜杠加倍

'^\d+PRE'