时间戳

首页 > 解决方案 > Postgresql，从日志中替换查询中的参数，regexp_replace 函数的想法？

sql - Postgresql，从日志中替换查询中的参数，regexp_replace 函数的想法？

问题描述

在 postgresql 日志中，查询以 $ 作为参数显示，它们显示在查询的末尾，如下所示：

select * from table where col1 = $1 and col2 = $2 and col3 = $3
parameters: $1 = '100', $2 = 'X', $3 = 'Y' etc.

有时可能有几十个参数，为了验证可疑查询，我需要手动替换参数。

我正在尝试创建一个将替换所有参数的函数，但我不知道如何使用参数“迭代”字符串以在替换循环中使用它们。

为了替换单个参数，我想出了这样的东西：（当然，单个替换使用这个是没有意义的，但这是我设法开始的）

create or replace function 
parameters_replace(query text, parameter_no integer, parameter text)
returns text as 
$body$
declare
result text;
BEGIN

select regexp_replace(query, '\$'||parameter_no||'\y', ''''||parameter||'''') into result;
return result;      
END;
$body$
LANGUAGE 'plpgsql'

select parameters_replace('select * from table where col1 = $1 and col2 = $2 and col3 = $3',1, '100')

预期的工作函数应该只有 2 个参数、要替换的输入文本和带参数的字符串

所以：

select parameters_replace('select * from table where col1 = $1 and col2 = $2 and col3 = $3', '$1 = '100', $2 = 'X', $3 = 'Y'')

应该返回

select * from table where col1 = '100' and col2 = 'X' and col3 = 'Y'

@编辑

我管理过这样的事情，但我发现引号有些麻烦。

create or replace  FUNCTION parameters_replace2(_query text, parameters text)
returns text as 
$body$
declare
result text;
rec record;
_sql text;
BEGIN

FOR rec in (SELECT 
array_to_string(REGEXP_MATCHES(parameters, '(?<=\$)(.*?)(?=\ )', 'g'), ';') as argument_no, -- extract number between $ sign and space
array_to_string(REGEXP_MATCHES(parameters, '(?<=\= )(.*?)(?=\,)', 'g'), ';') as argument_value -- extract characters between  '= ' and ', '
 )
 LOOP
 
 _sql := format(
$fff$    
select regexp_replace('%1$s', '\$'||%2$s||'\y', ''''''||%3$s||'''''') ; -- replace $||argument_no with argument_value with double quotes
$fff$, _query, rec.argument_no, rec.argument_value );

execute _sql into _query;

END LOOP;

return _query;      
END;
$body$
LANGUAGE 'plpgsql'

执行时：

select parameters_replace2('select * from table where col1 = $1 and col2 = $2 and col3 = $3 ', '$1 = ''3'', $2 = ''100'', $3 = ''1'',' )

它返回一个错误

ERROR:  syntax error at or near "3"
LINE 2: ...regexp_replace('select * from table where col1 = '3' and col...
                                                             ^
QUERY:       
select regexp_replace('select * from table where col1 = '3' and col2 = ''100'' and col3 = $3 ', '\$'||3||'\y', ''''''||'1'||'''''') ;

我们可以看到第一个参数被单引号替换，即使在代码中我使用了 '''''' 所以双引号。

但是当使用单个参数替换它时，它运行良好并且输出是双引号（我可以稍后删除）

select parameters_replace2('select * from table where col1 = $1 and col2 = $2 and col3 = $3 ', 
'$1 = ''3'', $2 = ''100'', $3 = ''1'',' )

输出：select * from table where col1 = ''3'' and col2 = $2 and col3 = $3

标签： sqlregexpostgresql

解决方案

终于成功了 :) 也许有些奇怪的方式，但有效。

CREATE OR REPLACE FUNCTION parameters_replace (_query text, _parameters text)
        RETURNS TEXT
        AS $body$
    DECLARE
        rec record;
        SQL2 text;
        SQL text;
    BEGIN
    DROP TABLE query_parameters_replace;
        CREATE TEMPORARY TABLE IF NOT EXISTS query_parameters_replace (query text );
        
    INSERT INTO query_parameters_replace VALUES (_query);
        SQL := '';
        FOR rec IN (
            SELECT
                array_to_string(REGEXP_MATCHES(_parameters || ',', '(?<=\$)(.*?)(?=\ )', 'g'), ';') AS argument_no,
                array_to_string(REGEXP_MATCHES(_parameters || ',', '(?<=\= )(.*?)(?=\,)', 'g'), ';') AS argument_value)
            LOOP
                SELECT
                    INTO SQL2 format('update query_parameters_replace set query = regexp_replace(query,''\$''||%s||''\y'', ''''''''||%s||'''''''', ''ig''); '
                    , rec.argument_no, rec.argument_value);
                SQL := SQL || SQL2;
            END LOOP;
        EXECUTE sql;
        RETURN (SELECT * FROM query_parameters_replace);
        DROP TABLE query_parameters_replace;
    END;
    $body$
    LANGUAGE 'plpgsql'
    
    select parameters_replace(
    'select * from test where col = $1 and col2 = $2', 
    '$1 = ''1'', $2 = ''test''' 
    )

推荐阅读