Date: Tue, 1 Feb 2022 11:37:07 +0800
From: Julien Rouhaud <rjuju123@gmail.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Gilles Darold <gilles@darold.net>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Deparsing rewritten query
Message-ID: <20220201033707.facusnf6evjmnoo3@nol>
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>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="vgzwunjhkiix76jj"
Content-Disposition: inline
In-Reply-To: 
 <CAFj8pRBb2bDUqgzv_r1LVUNo6CEQsEA_9NQDTrrk12a5S=EWJA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/20220201033707.facusnf6evjmnoo3%40nol>
Precedence: bulk


--vgzwunjhkiix76jj
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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)

--vgzwunjhkiix76jj
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename="v3-0001-Add-pg_get_query_def-to-deparse-and-print-a-rewri.patch"

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.c
index 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.dat
index 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.out
index 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.sql
index 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


--vgzwunjhkiix76jj--