tsvector更新中的Postgresql正则表达式

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-amble是一个整数，后跟'PRE‘。

(PostgreSQL 9.0)

Answer 1

基本触发器设计

这个问题是主要的。在PostgreSQL中，您可以创建一个触发器函数来完成这项工作。我在问题中看不到你的触发器函数。

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

function_name

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

参数

触发器执行时要提供给函数的可选参数列表(以逗号分隔)。参数是文字字符串常量。简单的名称和数字常量也可以写在这里，但它们都将被转换为字符串。请检查触发器函数的实现语言的描述，以了解如何在函数中访问这些参数；它可能与普通函数参数不同。

大胆强调我的观点。

使用NEW访问触发器函数中的列值。您不需要将它们作为参数传递。首先掌握基本概念。Start here.

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;

assignment operator of plpgsql is -不同于使用=的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', 'foo@dummy.org')
RETURNING *;

-- looks good!

UPDATE users SET firstname = 'Walter' WHERE TRUE
RETURNING *;

-- looks good, too!

清理：

ROLLBACK;

standard_conforming_strings

探索您的standard_conforming_strings设置。该警告表明您没有启用此设置，这将要求您在以下内容中使用双反斜杠：

'^\\d+PRE'