PostgreSQL v10中如何预编译正则表达式？

嘿，这个问题我刚好碰到过，在PostgreSQL 10里完全可以通过预编译正则表达式来实现复用，完美解决你遇到的类型转换错误问题！下面给你两种实用的实现方式：

方法一：用pg_regex类型存储预编译正则 #

PostgreSQL 10引入了pg_regex这个内部类型，专门用来存储预编译后的正则表达式。你可以把常用的正则提前编译好存在表里，后续直接调用，彻底避开动态参数的类型推断问题。

步骤示例： #

1. 创建存储预编译正则的表：

CREATE TABLE reusable_regexes (
    id SERIAL PRIMARY KEY,
    compiled_regex pg_regex NOT NULL,
    regex_desc TEXT NOT NULL
);

2. 插入预编译的正则表达式（用regexp_make()函数完成编译）：

INSERT INTO reusable_regexes (compiled_regex, regex_desc)
VALUES 
    (regexp_make('^[a-z0-9]{5,10}$'), '匹配5-10位小写字母或数字'),
    (regexp_make('^[\w.-]+@[\w.-]+\.[a-zA-Z]{2,}$'), '匹配简单邮箱格式');

3. 查询时直接复用预编译正则：

-- 用~操作符匹配
SELECT username FROM users WHERE username ~ (SELECT compiled_regex FROM reusable_regexes WHERE id = 1);

-- 或者用regexp_match函数
SELECT regexp_match(email, (SELECT compiled_regex FROM reusable_regexes WHERE id = 2)) FROM contacts;

方法二：用PL/pgSQL函数封装预编译正则 #

如果不想用表存储，也可以写个轻量的函数把预编译正则封装起来，每次调用函数直接获取编译好的正则：

CREATE OR REPLACE FUNCTION get_email_regex() RETURNS pg_regex AS $$
BEGIN
    -- 提前编译好正则，函数返回预编译后的pg_regex类型
    RETURN regexp_make('^[\w.-]+@[\w.-]+\.[a-zA-Z]{2,}$');
END;
$$ LANGUAGE plpgsql STABLE;

调用时直接用函数返回值：

SELECT * FROM contacts WHERE email ~ get_email_regex();

• 不要手动输入pg_regex类型的字面量，必须通过regexp_make()函数生成，否则会触发类型错误。
• 预编译后的正则会跳过每次查询时的编译步骤，性能比每次传字符串正则更优。
• 你之前遇到的failed to find conversion function from unknown to text错误，本质是PostgreSQL无法自动推断动态参数的类型，而预编译成pg_regex后类型明确，彻底解决了这个问题。

内容的提问来源于stack exchange，提问作者Peter Krauss