Printing command string passed to EXECUTE command in plpgsql (after argument resolution)

Hello

2010/10/28 Allan Kamau <kamauallan@gmail.com>:

I am debugging a plpgsql function which contains a long sql query
consisting of several parameters which is executed using EXECUTE
command. I would like to output this command string including the
actual values of the parameters contained within it so I can obtain
the actual query and run it directly (manually) in psql (or other
client such as pgAdmin3). Or at least is possible to output the
command string as is, followed by the parameter resolution details
such as $1='2', $2='abc' and so on.

if you has a some modern version of PostgreSQL, you can use a
autoexplain contrib module. just do

load 'auto_explain';
set auto_explain.log_min_duration = 0;
set auto_explain.log_nested_statements = true;

select your_function();

look to log

Regards

Pavel Stehule

other solution is just a using a RAISE NOTICE

var := 'SELECT .... ';
RAISE NOTICE '%', var;
EXECUTE var USING ..

Regards

Pavel Stehule

On Thursday 28. October 2010 16.25.47 Allan Kamau wrote:

I am debugging a plpgsql function which contains a long sql query
consisting of several parameters which is executed using EXECUTE
command. I would like to output this command string including the
actual values of the parameters contained within it so I can obtain
the actual query and run it directly (manually) in psql (or other
client such as pgAdmin3). Or at least is possible to output the
command string as is, followed by the parameter resolution details
such as $1='2', $2='abc' and so on.

If I understand you correctly, you can assign the SQL string to a variable x,
and then do a RAISE NOTICE 'Query is: %', x

regards,
Leif B. Kristensen

On Thu, Oct 28, 2010 at 5:47 PM, Leif Biberg Kristensen
<leif@solumslekt.org> wrote:

On Thursday 28. October 2010 16.25.47 Allan Kamau wrote:

I am debugging a plpgsql function which contains a long sql query
consisting of several parameters which is executed using EXECUTE
command. I would like to output this command string including the
actual values of the parameters contained within it so I can obtain
the actual query and run it directly (manually) in psql (or other
client such as pgAdmin3). Or at least is possible to output the
command string as is, followed by the parameter resolution details
such as $1='2', $2='abc' and so on.

If I understand you correctly, you can assign the SQL string to a variable x,
and then do a RAISE NOTICE 'Query is: %', x

regards,
Leif B. Kristensen

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I could use the RAISE NOTICE could work but I will have to write
another command string and use % in place of the $<somenumber> for the
parameters, one string for RAISE NOTICE and the other for EXECUTE.
This may potentially introduce some differences (due to human error)
between the output of RAISE NOTICE and the command string executed
after parameter solution during the call to EXECUTE.

Pavel's suggestion to use 'auto_explain' contrib module may be one of
the probable solutions.

Allan.

I could use the RAISE NOTICE could work but I will have to write
another command string and use % in place of the $<somenumber> for the
parameters, one string for RAISE NOTICE and the other for EXECUTE.
This may potentially introduce some differences (due to human error)
between the output of RAISE NOTICE and the command string executed
after parameter solution during the call to EXECUTE.

you can simply minimalize these risks

CREATE OR REPLACE FUNCTION notice(text, boolena)
RETURNS text AS $$
BEGIN
IF $2 THEN
RAISE NOTICE '%', $1;
END IF;
RETURN $1;
END;
$$ LANGUAGE plpgsql;

and then you can use it in EXECUTE

EXECUTE notice('SELECT ....', true) USING ...

Regards

Pavel Stehule