Thread: Printing command string passed to EXECUTE command in plpgsql (after argument resolution)
Printing command string passed to EXECUTE command in plpgsql (after argument resolution)
From
Allan Kamau
Date:
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. Allan.
Re: Printing command string passed to EXECUTE command in plpgsql (after argument resolution)
From
Pavel Stehule
Date:
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 > Allan. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: Printing command string passed to EXECUTE command in plpgsql (after argument resolution)
From
Leif Biberg Kristensen
Date:
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
Re: Printing command string passed to EXECUTE command in plpgsql (after argument resolution)
From
Allan Kamau
Date:
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.
Re: Printing command string passed to EXECUTE command in plpgsql (after argument resolution)
From
Pavel Stehule
Date:
> 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
> Pavel's suggestion to use 'auto_explain' contrib module may be one of
> the probable solutions.
>
> Allan.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>