From: | Julien Rouhaud <rjuju123(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Deparsing rewritten query |
Date: | 2021-06-27 04:11:38 |
Message-ID: | 20210627041138.zklczwmu3ms4ufnk@nol |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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)
Attachment | Content-Type | Size |
---|---|---|
v1-0001-Add-pg_get_query_def-to-deparse-and-print-a-rewri.patch | text/x-diff | 6.4 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2021-06-27 04:31:34 | Re: Deparsing rewritten query |
Previous Message | Julien Rouhaud | 2021-06-27 03:35:39 | Re: [HACKERS] Preserving param location |