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 <[email protected]>
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
