All we need is an easy explanation of the problem, so here it is.
Let’s say with the below function, I need to debug what is executed inside the
EXECUTE statement. (a long, dynamic statement with dynamic variables provided in
USING in reality)
CREATE OR REPLACE FUNCTION fn() RETURNS integer LANGUAGE 'plpgsql' AS $$ BEGIN EXECUTE $x$ SELECT $1, $2, $3 $x$ USING 1, '2020-01-01'::date, NULL::date; RETURN 0; END; $$;
It seems that in such a case the best you can do is to get back the string
SELECT $1, $2, $3. Is there any way to print/raise
SELECT 1, '2020-01-01'::date, NULL::date?
(I would imagine something like
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
This is more sophisticated than one might expect. PL/pgSQL
EXECUTE strips comments and parses the query string, identifying tokens. Only actual
$-parameters are then replaced with values from the
USING clause, before planning the query. Not
$n contained in comments, strings or identifiers (like
-- $1 is required,
'My text says $2' or
"costs$3"). We would have to duplicate much of the parser’s functionality to be as accurate.
But I have been wishing for the same functionality in the past. So here goes:
replace() will be fooled by any occurrence of
$n that is not an actual parameter. Here is a poor-man’s implementation that should do a better job. And it includes type data types. (But it’s still far from perfect!)
First a little helper function: Postgres provides a couple of functions to format strings as identifier, literal etc:
quote_ident() quote_literal() quote_nullable()
We need this one:
Converts the given value to text and then quotes it as a literal; or,
if the argument is null, returns
NULL. Embedded single-quotes and
backslashes are properly doubled.
But with an appended cast to the original data type. So I call it
CREATE OR REPLACE FUNCTION quote_nullable_typed(anyelement) RETURNS text LANGUAGE sql PARALLEL SAFE IMMUTABLE AS $func$ SELECT quote_nullable($1) || '::' || pg_typeof($1); $func$;
format() has format specifiers inlining the functionality of the mentioned
%L. Not for our custom function, obviously. So apply it explicitly and pass the result with
%s. Nested in another helper function:
CREATE OR REPLACE FUNCTION f_sql_string_with_params(_sql text, VARIADIC x text) RETURNS text LANGUAGE sql PARALLEL SAFE IMMUTABLE AS $func$ SELECT format(regexp_replace(replace(_sql, '%', '%%') , '(?<=[^[:alnum:]_''"])\$(\d+)(?=[^[:alnum:]_''"])' , '%\1$s' , 'g' ) , x,x,x,x,x,x,x,x,x,x) -- allow more? $func$;
VARIADIC parameter accepts a variable number of parameters. The function iterates through the first 10 (arbitrarily). If you need more, expand the list.
Core functionality is:
regexp_replace(replace(_sql, '%', '%%'), '(?<=[^[:alnum:]_''"])\$(\d+)(?=[^[:alnum:]_''"])', '%\1$s', 'g')
Double up any
% to prepare for
The regexp pattern
(?<=) … positive lookbehind
[^[:alnum:]_''"] … character class excluding word characters and
\$ … literal
(\d+) … one or more digits in capturing parentheses (noted for replacement)
(?=) … positive lookahead
So it only replaces
$n when not between word characters or next to quotes. Far from perfect. But should cover typical cases.
Ideally, we would strip comments first. Who ever feels in the mood to improve it further …
Applied to your original example:
CREATE OR REPLACE FUNCTION fn() RETURNS void LANGUAGE plpgsql AS $func$ DECLARE _sql text := $q$SELECT $1, $2, $3, 'false $1', format('demo: %I', 'xX')$q$; -- sql string, extended with potential hazards _v1 int := 1; _v2 date := '2020-01-01'; _v3 date; -- defaults to NULL BEGIN RAISE NOTICE E'Query string with $-parameters:\n%', _sql; RAISE NOTICE E'Query string with typed values:\n%' , f_sql_string_with_params(_sql , quote_nullable_typed(_v1) , quote_nullable_typed(_v2) , quote_nullable_typed(_v3) ); EXECUTE _sql USING _v1, _v2, _v3; END $func$;
NOTICE will report:
SELECT '1'::integer, '2020-01-01'::date, NULL::date;
db<>fiddle here – with extended test case
RAISE NOTICE should do the trick:
RAISE NOTICE '$1 = "%", $2 = "%", $3 = "%"', 1, '2020-01-01'::date, NULL::date;
To interpolate the values in the query, you can use
replace( replace( replace('SELECT $1, $2, $3', '$1', '1'), '$2', '''2020-01-01''::date' ), '$3', 'NULL::date' )
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂