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.

Search Discussions

  • Pavel Stehule at Oct 28, 2010 at 2:41 pm
    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
  • Leif Biberg Kristensen at Oct 28, 2010 at 2:47 pm

    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
  • Allan Kamau at Oct 28, 2010 at 3:00 pm

    On Thu, Oct 28, 2010 at 5:47 PM, Leif Biberg Kristensen 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.
  • Pavel Stehule at Oct 28, 2010 at 3:05 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedOct 28, '10 at 2:25p
activeOct 28, '10 at 3:05p
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase