sql - regexp_matches postgreSQL 函数中的简单参数替换

Question

我有一个结构如下的表...

the_geom 数据

geom1    data1+3000||data2+1000||data3+222

geom2    data1+500||data2+900||data3+22232

我想创建一个根据用户请求返回记录的函数。

Example: for data2, retrieve geom1,1000 and geom2, 900

直到现在我创建了这个函数(见下文)，它工作得很好但我面临参数替换问题......(你可以看到我无法用“data2”替换 $1 ......但是是的我以后可以用$1

regexp_matches(t::text, E'(data2[\+])([0-9]+)'::text)::text)[2]::integer

我的函数

create or replace function get_counts(taxa varchar(100))

returns setof record

as $$

SELECT t2.counter,t2.the_geom

FROM  (

   SELECT (regexp_matches(t.data::text, E'(data2[\+])([0-9]+)'::text)::text)[2]::integer as counter,the_geom

   from (select the_geom,data from simple_inpn2 where data ~ $1::text) as t

    ) t2

   $$
 language sql;

SELECT get_counts('data2') will work **but we should be able to make this substitution**:

regexp_matches(t::text, E'($1... instead of E'(data2....

我认为它更多的是一个语法问题，因为函数执行没有给出错误，只是将 $1 解释为一个字符串并且没有给出任何结果。

提前致谢，

Answer 1

E'$1' 是一个字符串文字(使用 escape string 语法)，包含一个美元符号后跟一个一。未加引号的 $1 是函数的第一个参数。所以这个:

regexp_matches(t, E'($1[\+])([0-9]+)'))[2]::integer

如您所见，不会用函数的第一个参数插入 $1。

正则表达式只是一个字符串，一个具有内部结构但仍然只是一个字符串的字符串。如果你知道 $1 是一个普通的词，那么你可以说:

regexp_matches(t, E'(' || $1 || E'[\+])([0-9]+)'))[2]::integer

将您的字符串粘贴到合适的正则表达式中。但是，最好是有点偏执，迟早有人会用 'ha ha (' 这样的字符串调用你的函数，所以你应该为此做好准备。我最简单的方法可以想到在正则表达式中加入任意字符串就是对所有的非单词字符进行转义:

-- Don't forget to escape the escaped escapes! Hence all the backslashes.
str := regexp_replace($1, E'(\\W)', E'\\\\\\1', 'g');

然后将 str 粘贴到上面的正则表达式中:

regexp_matches(t, E'(' || str || E'[\+])([0-9]+)'))[2]::integer

或者更好的是，在 regexp_matches 之外构建正则表达式以减少嵌套括号:

re := E'(' || str || E'[\+])([0-9]+)';
-- ...
select regexp_matches(t, re)[2]::integer ...

PostgreSQL 没有 Perl's \Q...\E和 (?q) metasyntax应用到正则表达式的末尾，所以我想不出比转义所有内容并让 PostgreSQL 对其进行排序更好的方法来将任意字符串作为非正则表达式文字值粘贴到正则表达式的中间。

使用这种技术，我们可以做如下事情:

=> do $$
    declare
        m text[];
        s text;
        r text;
    begin
        s = E'''{ha)?';
        r = regexp_replace(s, E'(\\W)', E'\\\\\\1', 'g');
        r = '(ha' || r || ')';
        raise notice '%', r;
        select regexp_matches(E'ha''{ha)?', r) into m;
        raise notice '%', m[1];
    end$$;

并得到预期的结果

NOTICE:  ha'{ha)?

输出。但是如果你省略了 regexp_replace 转义步骤，你只会得到一个

invalid regular expression: parentheses () not balanced

错误。

顺便说一句，我认为您不需要所有转换，所以我删除了它。正则表达式和转义已经够吵了，没有必要把一堆冒号混在一起。另外，我不知道你的 standard_conforming_strings设置为或您正在使用的 PostgreSQL 版本，所以我到处都使用 E'' 字符串。您还需要将过程切换到 PL/pgSQL(language plpgsql)以使转义更容易。