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

On Thu, Oct 28, 2010 at 5:47 PM, Leif Biberg Kristensen
<leif(at)solumslekt(dot)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(at)postgresql(dot)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.