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

Hello

2010/10/28 Allan Kamau <kamauallan(at)gmail(dot)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

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