agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH v3] Add pg_get_query_def() to deparse and print a rewritten SQL statement. 2+ messages / 2 participants [nested] [flat]
* [PATCH v3] Add pg_get_query_def() to deparse and print a rewritten SQL statement. @ 2021-06-27 03:39 Julien Rouhaud <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Julien Rouhaud @ 2021-06-27 03:39 UTC (permalink / raw) --- 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-- ^ permalink raw reply [nested|flat] 2+ messages in thread
* [PATCH 1/3] pg_dump tests: don't put dumps in stdout @ 2025-11-25 12:42 Álvaro Herrera <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Álvaro Herrera @ 2025-11-25 12:42 UTC (permalink / raw) --- src/bin/pg_dump/t/002_pg_dump.pl | 10 ++++++++-- 1 file changed, 8 insertions(+), 2 deletions(-) diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index 445a541abf6..a05212719d9 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -5019,7 +5019,12 @@ $node->command_fails_like( # Test dumping pg_catalog (for research -- cannot be reloaded) $node->command_ok( - [ 'pg_dump', '--port' => $port, '--schema' => 'pg_catalog' ], + [ + 'pg_dump', + '--port' => $port, + '--schema' => 'pg_catalog', + '--file' => 'pgdump_pgcatalog.dmp' + ], 'pg_dump: option -n pg_catalog'); ######################################### @@ -5029,7 +5034,8 @@ $node->command_ok( [ 'pg_dumpall', '--port' => $port, - '--exclude-database' => '"myhost.mydb"' + '--exclude-database' => '"myhost.mydb"', + '--file' => 'pgdumpall.dmp' ], 'pg_dumpall: option --exclude-database handles database names with embedded dots' ); -- 2.47.3 --4zvagbkg7hhbwadg Content-Type: text/x-diff; charset=utf-8 Content-Disposition: attachment; filename="0002-log_statement-off.patch" ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-11-25 12:42 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-06-27 03:39 [PATCH v3] Add pg_get_query_def() to deparse and print a rewritten SQL statement. Julien Rouhaud <[email protected]> 2025-11-25 12:42 [PATCH 1/3] pg_dump tests: don't put dumps in stdout Álvaro Herrera <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox