Re: Deparsing rewritten query - Mailing list pgsql-hackers

Hi,

I sometimes have to deal with queries referencing multiple and/or complex
views.  In such cases, it's quite troublesome to figure out what is the query
really executed.  Debug_print_rewritten isn't really useful for non trivial
queries, and manually doing the view expansion isn't great either.

While not being ideal, I wouldn't mind using a custom extension for that but
this isn't an option as get_query_def() is private and isn't likely to change.

As an alternative, maybe we could expose a simple SRF that would take care of
rewriting the query and deparsing the resulting query tree(s)?

I'm attaching a POC patch for that, adding a new pg_get_query_def(text) SRF.

Usage example:

SELECT pg_get_query_def('SELECT * FROM shoe') as def;
                          def
--------------------------------------------------------
  SELECT shoename,                                     +
     sh_avail,                                         +
     slcolor,                                          +
     slminlen,                                         +
     slminlen_cm,                                      +
     slmaxlen,                                         +
     slmaxlen_cm,                                      +
     slunit                                            +
    FROM ( SELECT sh.shoename,                         +
             sh.sh_avail,                              +
             sh.slcolor,                               +
             sh.slminlen,                              +
             (sh.slminlen * un.un_fact) AS slminlen_cm,+
             sh.slmaxlen,                              +
             (sh.slmaxlen * un.un_fact) AS slmaxlen_cm,+
             sh.slunit                                 +
            FROM shoe_data sh,                         +
             unit un                                   +
           WHERE (sh.slunit = un.un_name)) shoe;       +

(1 row)