Postgresql, replace arguments in queries from logs, regexp_replace function idea?

Question

In the postgresql logs, the queries are presented with $ as arguments and they are showed at the end of the query like that:

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

Sometimes there might be dozens of the arguments and to verify the suspicious queries I need to manually replace the arguments.

I'm trying to create a function that will replace all of the arguments but I don't know how to 'iterate' over the string with parameters to use them in the replacing loop.

To replace single parameter I came up with something like this: (of course it makes no sense for single replacement to use this, but that's something I managed to start with)

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')

Expected working function should have just 2 parameters, input text to replace in, and string with parameters

So:

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

Should return

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

@EDIT

I managed something like this, but I find some troubles with quotes.

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'

When executing:

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

It returns an error

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'||'''''') ;

We can see that first argument was replaced with single quotes even if in the code I used '''''' so double quotes.

BUT when executing it with single parameter to replace, it works well and output is with double quotes (which I can remove later on)

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

Output: select * from table where col1 = ''3'' and col2 = $2 and col3 = $3

user_5861505 · Answer

Finally made it :) Maybe some strange way, but works.

CREATE OR REPLACE FUNCTION parameters_replace (_query text, _parameters text)
        RETURNS TEXT
        AS $body$
    DECLARE
        rec record;
        SQL2 text;
        SQL text;
    BEGIN
    DROP TABLE IF EXISTS 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''' 
    )

Postgresql, replace arguments in queries from logs, regexp_replace function idea?

1 Answers