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)
>From 5e726861fff28a276bb2e31972b278b833968ff4 Mon Sep 17 00:00:00 2001 From: Julien Rouhaud <julien.rouh...@free.fr> Date: Sun, 27 Jun 2021 11:39:47 +0800 Subject: [PATCH v1] Add pg_get_query_def() to deparse and print a rewritten SQL statement. --- src/backend/rewrite/rewriteHandler.c | 2 + src/backend/utils/adt/ruleutils.c | 70 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 3 ++ src/test/regress/expected/rules.out | 26 +++++++++++ src/test/regress/sql/rules.sql | 3 ++ 5 files changed, 104 insertions(+) diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 88a9e95e33..f01b4531bf 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -1815,6 +1815,8 @@ ApplyRetrieveRule(Query *parsetree, rte->rtekind = RTE_SUBQUERY; rte->subquery = rule_action; rte->security_barrier = RelationIsSecurityView(relation); + if (!rte->alias) + rte->alias = makeAlias(get_rel_name(rte->relid), NULL); /* Clear fields that should not be set in a subquery RTE */ rte->relid = InvalidOid; rte->relkind = 0; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 3719755a0d..cd39f4ce75 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -49,6 +49,8 @@ #include "nodes/nodeFuncs.h" #include "nodes/pathnodes.h" #include "optimizer/optimizer.h" +#include "parser/analyze.h" +#include "parser/parse_node.h" #include "parser/parse_agg.h" #include "parser/parse_func.h" #include "parser/parse_node.h" @@ -58,6 +60,7 @@ #include "rewrite/rewriteHandler.h" #include "rewrite/rewriteManip.h" #include "rewrite/rewriteSupport.h" +#include "tcop/tcopprot.h" #include "utils/array.h" #include "utils/builtins.h" #include "utils/fmgroids.h" @@ -489,6 +492,73 @@ static void get_reloptions(StringInfo buf, Datum reloptions); #define only_marker(rte) ((rte)->inh ? "" : "ONLY ") +/* return the query as postgres will rewrite */ +Datum +pg_get_query_def(PG_FUNCTION_ARGS) +{ + char *sql = TextDatumGetCString(PG_GETARG_TEXT_PP(0)); + List *parsetree_list; + List *querytree_list; + RawStmt *parsetree; + Query *query; + bool snapshot_set = false; + StringInfoData buf; + StringInfoData res; + ListCell *lc; + + if (strcmp(sql, "") == 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("empty statement now allowed"))); + + parsetree_list = pg_parse_query(sql); + + /* only support one statement at a time */ + if (list_length(parsetree_list) != 1) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("a single statement should be provided"))); + + initStringInfo(&res); + + parsetree = linitial_node(RawStmt, parsetree_list); + + /* + * Set up a snapshot if parse analysis/planning will need one. + */ + if (analyze_requires_snapshot(parsetree)) + { + PushActiveSnapshot(GetTransactionSnapshot()); + snapshot_set = true; + } + + querytree_list = pg_analyze_and_rewrite(parsetree, sql, + NULL, 0, NULL); + + /* Done with the snapshot used for parsing/planning */ + if (snapshot_set) + PopActiveSnapshot(); + + foreach(lc, querytree_list) + { + query = (Query *) lfirst(lc); + initStringInfo(&buf); + + if (query->utilityStmt) + appendStringInfo(&res, "%s;\n", sql); + else + { + get_query_def(query, &buf, NIL, NULL, + PRETTYFLAG_INDENT, + WRAP_COLUMN_DEFAULT, 0); + + appendStringInfo(&res, "%s;\n", buf.data); + } + } + pfree(buf.data); + + PG_RETURN_TEXT_P(string_to_text(res.data)); +} /* ---------- * pg_get_ruledef - Do it all and return a text diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index fde251fa4f..b15b40aa7a 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3650,6 +3650,9 @@ proargtypes => 'oid oid', prosrc => 'oidge' }, # System-view support functions +{ oid => '9246', descr => 'show a query as rewritten', + proname => 'pg_get_query_def', provolatile => 'v', prorettype => 'text', + proargtypes => 'text', prosrc => 'pg_get_query_def' }, { oid => '1573', descr => 'source text of a rule', proname => 'pg_get_ruledef', provolatile => 's', prorettype => 'text', proargtypes => 'oid', prosrc => 'pg_get_ruledef' }, diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index e5ab11275d..dd88b890f4 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -3075,6 +3075,32 @@ select pg_get_viewdef('shoe'::regclass,0) as prettier; WHERE sh.slunit = un.un_name; (1 row) +-- test pg_get_query_def() +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) + -- -- check multi-row VALUES in rules -- diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index 6ec37c4381..aeac713680 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -998,6 +998,9 @@ select pg_get_viewdef('shoe'::regclass) as unpretty; select pg_get_viewdef('shoe'::regclass,true) as pretty; select pg_get_viewdef('shoe'::regclass,0) as prettier; +-- test pg_get_query_def() +SELECT pg_get_query_def('SELECT * FROM shoe') as def; + -- -- check multi-row VALUES in rules -- -- 2.31.1