Viewing the statement in EXECUTE with USING

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 format('%1$s', 'xyz')?)

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.

Method 1

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:

Plain 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:

quote_nullable ( anyelement ) → text

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 quote_nullable_typed():

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 quote_*() functions: %I, %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[1],x[2],x[3],x[4],x[5],x[6],x[7],x[8],x[9],x[10])  -- allow more?
$func$;

The 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 format(). See:

The regexp pattern (?<=[^[:alnum:]_''"])\$(\d+)(?=[^[:alnum:]_''"]) explained:

(?<=) … 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$;

The second NOTICE will report:

SELECT '1'::integer, '2020-01-01'::date, NULL::date;

db<>fiddle here – with extended test case

Method 2

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , ,

Leave a Reply