From: Julien Rouhaud <rjuju123@gmail.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Gilles Darold <gilles@darold.net>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Deparsing rewritten query
Date: Tue, 1 Feb 2022 11:37:07 +0800
Message-ID: <20220201033707.facusnf6evjmnoo3@nol> (raw)
In-Reply-To: <CAFj8pRBb2bDUqgzv_r1LVUNo6CEQsEA_9NQDTrrk12a5S=EWJA@mail.gmail.com>
References: <20210627041138.zklczwmu3ms4ufnk@nol>
<3176554.1624804492@sss.pgh.pa.us>
<20210627150343.zslwnw7g564f2zz4@nol>
<8085.1624806845@sss.pgh.pa.us>
<8f0b88a8-e9b8-0fd8-4dd1-ad14c4fdca0e@darold.net>
<20210628164147.vyglw4rbyyypncqf@nol>
<44a01de0-7c46-1ea9-41b0-c85a795c3c6c@darold.net>
<CAFj8pRCzdbPJkUOP6e-mci=Vo91FqBOLvYsiJWAaVkEXGyJb5Q@mail.gmail.com>
<20220131180938.id2jjihudd4xmwjh@jrouhaud>
<CAFj8pRBb2bDUqgzv_r1LVUNo6CEQsEA_9NQDTrrk12a5S=EWJA@mail.gmail.com>
Hi,
On Mon, Jan 31, 2022 at 10:05:44PM +0100, Pavel Stehule wrote:
>
> I don't feel good about forcing an alias. relname doesn't ensure
> uniqueness. You can have two views with the same name from different
> schemas. Moreover this field is necessary only when a deparsed query is
> printed, not always.
Yes I agree.
> Isn't possible to compute the correct subquery alias in print time when it
> is missing?
Actually I think that the current code already does everything to generate
unique refnames, it's just that they don't get printed for a query after view
expansions. I modified the patch to simply make sure that an alias is
displayed when it's a subquery and the output using a custom pg_get_query_def
is like that:
# select pg_get_query_def('select * from nsp1.v1');
pg_get_query_def
-------------------------------
SELECT nb +
FROM ( SELECT 1 AS nb) v1;+
(1 row)
# select pg_get_query_def('select * from nsp1.v1, nsp2.v1');
pg_get_query_def
-------------------------------
SELECT v1.nb, +
v1_1.nb +
FROM ( SELECT 1 AS nb) v1,+
( SELECT 1 AS nb) v1_1; +
(1 row)
Attachments:v3-0001-Add-pg_get_query_def-to-deparse-and-print-a-rewri.patch (text/plain, 6.2K) download
From 093cc350c09b3e0a458822da7f541ca602af4ef6 Mon Sep 17 00:00:00 2001
From: Julien Rouhaud <julien.rouhaud@free.fr>
Date: Sun, 27 Jun 2021 11:39:47 +0800
Subject: [PATCH v3] Add pg_get_query_def() to deparse and print a rewritten
SQL statement.
---
src/backend/utils/adt/ruleutils.c | 75 +++++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 ++
src/test/regress/expected/rules.out | 26 ++++++++++
src/test/regress/sql/rules.sql | 3 ++
4 files changed, 107 insertions(+)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.cindex 039b1d2b95..1186438757 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"
@@ -493,6 +496,68 @@ 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;++ 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
@@ -10989,6 +11054,16 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
if (strcmp(refname, rte->ctename) != 0)
printalias = true;
}
+ else if (rte->rtekind == RTE_SUBQUERY)+ {+ /*+ * For a subquery RTE, always print alias. A user-specified query+ * should only be valid if an alias is provided, but our view+ * expansion doesn't generate aliases, so a rewritten query might+ * not be valid SQL.+ */+ printalias = true;+ }
if (printalias)
appendStringInfo(buf, " %s", quote_identifier(refname));
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.datindex 0859dc81ca..a2e5de7967 100644--- a/src/include/catalog/pg_proc.dat+++ b/src/include/catalog/pg_proc.dat@@ -3704,6 +3704,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.outindex d652f7b5fb..8a17936a05 100644--- a/src/test/regress/expected/rules.out+++ b/src/test/regress/expected/rules.out@@ -3120,6 +3120,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.sqlindex b732833e63..a10377fcc5 100644--- a/src/test/regress/sql/rules.sql+++ b/src/test/regress/sql/rules.sql@@ -1012,6 +1012,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.35.0
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: rjuju123@gmail.com, pavel.stehule@gmail.com, gilles@darold.net, pgsql-hackers@lists.postgresql.org
Subject: Re: Deparsing rewritten query
In-Reply-To: <20220201033707.facusnf6evjmnoo3@nol>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox