在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
2条答案
按热度按时间8gsdolmq1#
终于做到了:)也许是一些奇怪的方式,但工作。
n6lpvg4x2#
做得很好,谢谢。如果你的服务器是非默认的
standard_conforming_strings=off
,根据https://www.postgresql.org/docs/current/functions-matching.html(* 如果您关闭了standard_conforming_strings,您在字符串常量中写入的任何反斜杠都需要加倍。*),您必须更改某些行。在两个array_to_string
行中,您应该将所有反斜杠加倍。在INTO SQL2
行中,您需要四个反斜杠,而不是一个,因为该行被服务器处理了两次。如果您不想在最后的SELECT(最后一行代码)中将每个
'
都加倍以使用这个精细的过程,只需使用美元引号https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING。