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
解决方案
终于成功了 :) 也许有些奇怪的方式,但有效。
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'''
)
推荐阅读
- javascript - 将 PHP 数组读入 NodeJS 函数
- python - 在 Django 视图中检索 Select2 多值以在后端使用
- vba - Word VBA - 如何插入标题编号的引用;标题文字;要评论的列表项编号和列表项页码?
- c++ - 在 C++ Cpp 中添加文件和文本?
- amazon-web-services - 如何将 JDBC 连接器连接到 AWS Athena 联合查询?
- javascript - 如何在 React Redux 中增加滚动时 Api 分页响应的结果
- python - 多处理似乎没有按预期打印输出
- ios - 使用 iOS 13+ 在 iPad 上区分 iPad 和 mac
- python - 将部分函数输出保存到 Python 中的文件
- javascript - 为什么捆绑 Rollup 并在本地链接包会导致“Invalid hook call”错误?