Re: Deparsing rewritten query

ne 27. 6. 2021 v 6:11 odesílatel Julien Rouhaud <rjuju123(at)gmail(dot)com> napsal:

> 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)
>

+1

Pavel