agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH 2/2] Add psql index info commands 3+ messages / 3 participants [nested] [flat]
* [PATCH 2/2] Add psql index info commands @ 2019-07-15 14:11 Nikita Glukhov <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Nikita Glukhov @ 2019-07-15 14:11 UTC (permalink / raw) --- doc/src/sgml/ref/psql-ref.sgml | 28 ++++ src/bin/psql/command.c | 10 ++ src/bin/psql/describe.c | 291 ++++++++++++++++++++++++++++++++++++- src/bin/psql/describe.h | 7 + src/bin/psql/help.c | 2 + src/bin/psql/tab-complete.c | 5 +- src/test/regress/expected/psql.out | 16 ++ src/test/regress/sql/psql.sql | 3 + 8 files changed, 359 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index e690c4d..c4ff542 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1451,6 +1451,34 @@ testdb=> </listitem> </varlistentry> + <varlistentry> + <term><literal>\dip [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> + + <listitem> + <para> + Shows index properties listed in + <xref linkend="functions-info-index-props"/>. + If <replaceable class="parameter">pattern</replaceable> is + specified, only access methods whose names match the pattern are shown. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <literal>\dicp [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>] + </literal> + </term> + + <listitem> + <para> + Shows index column properties listed in + <xref linkend="functions-info-index-column-props"/>. + If <replaceable class="parameter">pattern</replaceable> is + specified, only access methods whose names match the pattern are shown. + </para> + </listitem> + </varlistentry> <varlistentry> <term><literal>\des[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index e6cb260..36e2efe 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -827,6 +827,16 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) case 'v': case 'm': case 'i': + if (strncmp(cmd, "dip", 3) == 0) + { + success = describeIndexProperties(pattern, show_system); + break; + } + else if (strncmp(cmd, "dicp", 4) == 0) + { + success = describeIndexColumnProperties(pattern, show_system); + break; + } case 's': case 'E': success = listTables(&cmd[1], pattern, show_verbose, show_system); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 9cae8c8..86c3bd3 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -19,6 +19,7 @@ #include "catalog/pg_cast_d.h" #include "catalog/pg_class_d.h" #include "catalog/pg_default_acl_d.h" +#include "catalog/pg_index.h" #include "common/logging.h" #include "fe_utils/mbprint.h" @@ -47,7 +48,11 @@ static bool describeOneTSConfig(const char *oid, const char *nspname, const char *pnspname, const char *prsname); static void printACLColumn(PQExpBuffer buf, const char *colname); static bool listOneExtensionContents(const char *extname, const char *oid); - +static bool describeOneIndexColumnProperties(const char *oid, + const char *nspname, + const char *idxname, + const char *amname, + const char *tabname); /*---------------- * Handlers for various slash commands displaying some sort of list @@ -6361,3 +6366,287 @@ describeAccessMethodOperatorClasses(const char *access_method_pattern, PQclear(res); return true; } + +/* + * \dip + * Describes index properties. + * + * Takes an optional regexp to select particular index. + */ +bool +describeIndexProperties(const char *pattern, bool showSystem) +{ + PQExpBufferData buf; + PGresult *res; + printQueryOpt myopt = pset.popt; + + static const bool translate_columns[] = {false, false, false, false, false, false, false}; + + initPQExpBuffer(&buf); + + printfPQExpBuffer(&buf, + "SELECT" + " n.nspname AS \"%s\",\n" + " c.relname AS \"%s\",\n" + " am.amname AS \"%s\",\n", + gettext_noop("Schema"), + gettext_noop("Name"), + gettext_noop("Access method")); + appendPQExpBuffer(&buf, + pset.sversion >= 90600 ? + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'clusterable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%3$s\",\n" + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'index_scan')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%4$s\",\n" + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'bitmap_scan')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n" + " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'backward_scan')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%6$s\"\n" + : + " CASE WHEN am.amclusterable THEN '%1$s' ELSE '%2$s' END AS \"%3$s\",\n" + " CASE WHEN am.amgettuple <> 0 THEN '%1$s' ELSE '%2$s' END AS \"%4$s\",\n" + " CASE WHEN am.amgetbitmap <> 0 THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n" + " CASE WHEN am.amcanbackward THEN '%1$s' ELSE '%2$s' END AS \"%6$s\"\n", + gettext_noop("yes"), + gettext_noop("no"), + gettext_noop("Clusterable"), + gettext_noop("Index scan"), + gettext_noop("Bitmap scan"), + gettext_noop("Backward scan")); + appendPQExpBufferStr(&buf, + "FROM pg_catalog.pg_class c\n" + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" + " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n" + "WHERE c.relkind='i'\n" + " AND n.nspname !~ 'pg_toast'\n"); + + if (!showSystem && !pattern) + appendPQExpBufferStr(&buf, + " AND n.nspname <> 'pg_catalog'\n" + " AND n.nspname <> 'information_schema'\n"); + + processSQLNamePattern(pset.db, &buf, pattern, true, false, + "n.nspname", "c.relname", NULL, NULL); + + appendPQExpBufferStr(&buf, "ORDER BY 1;"); + res = PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + + myopt.nullPrint = NULL; + myopt.title = _("Index properties"); + myopt.translate_header = true; + myopt.translate_columns = translate_columns; + myopt.n_translate_columns = lengthof(translate_columns); + + printQuery(res, &myopt, pset.queryFout, false, pset.logfile); + + PQclear(res); + return true; +} + +/* + * \dicp + * Describes index index column properties. + * + * Takes an optional regexp to select particular index. + */ +bool +describeIndexColumnProperties(const char *index_pattern, bool showSystem) +{ + PQExpBufferData buf; + PGresult *res; + int i; + + initPQExpBuffer(&buf); + + printfPQExpBuffer(&buf, + "SELECT DISTINCT c.oid,\n" + " n.nspname,\n" + " c.relname,\n" + " am.amname,\n" + " c2.relname\n" + "FROM pg_catalog.pg_class c\n" + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = relnamespace\n" + " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n" + " LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n" + " LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid\n"); + + appendPQExpBufferStr(&buf, "WHERE c.relkind='i'\n"); + + if (!showSystem && !index_pattern) + appendPQExpBufferStr(&buf, "AND n.nspname <> 'pg_catalog'\n" + "AND n.nspname <> 'information_schema'\n"); + + processSQLNamePattern(pset.db, &buf, index_pattern, true, false, + "n.nspname", "c.relname", NULL, + "pg_catalog.pg_table_is_visible(c.oid)"); + + appendPQExpBufferStr(&buf, "ORDER BY 2, 3;"); + + res = PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + + if (PQntuples(res) == 0) + { + if (!pset.quiet) + { + if (index_pattern) + pg_log_error("Did not find any index named \"%s\".", + index_pattern); + else + pg_log_error("Did not find any relations."); + } + PQclear(res); + return false; + } + + for (i = 0; i < PQntuples(res); i++) + { + const char *oid = PQgetvalue(res, i, 0); + const char *nspname = PQgetvalue(res, i, 1); + const char *idxname = PQgetvalue(res, i, 2); + const char *amname = PQgetvalue(res, i, 3); + const char *tabname = PQgetvalue(res, i, 4); + + if (!describeOneIndexColumnProperties(oid, nspname, idxname, amname, + tabname)) + { + PQclear(res); + return false; + } + if (cancel_pressed) + { + PQclear(res); + return false; + } + } + + PQclear(res); + return true; +} + +static bool +describeOneIndexColumnProperties(const char *oid, + const char *nspname, + const char *idxname, + const char *amname, + const char *tabname) +{ + PQExpBufferData buf; + PGresult *res; + printQueryOpt myopt = pset.popt; + char *footers[3] = {NULL, NULL}; + static const bool translate_columns[] = {false, false, false, false, false, + false, false, false, false, false}; + + initPQExpBuffer(&buf); + + printfPQExpBuffer(&buf, + "SELECT\n" + " a.attname AS \"%s\",\n" + " pg_catalog.pg_get_indexdef(i.indexrelid, a.attnum, true) AS \"%s\",\n" + " CASE WHEN pg_catalog.pg_opclass_is_visible(o.oid) THEN '' ELSE n.nspname || '.' END || o.opcname AS \"%s\",\n", + gettext_noop("Column name"), + gettext_noop("Expr"), + gettext_noop("Opclass")); + + if (pset.sversion >= 90600) + appendPQExpBuffer(&buf, + " CASE\n" + " WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') = true \n" + " THEN CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'asc')\n" + " THEN '%1$s' ELSE '%2$s' END \n" + " ELSE NULL" + " END AS \"%3$s\"," + " CASE\n" + " WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') = true \n" + " THEN CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'nulls_first')\n" + " THEN '%1$s' ELSE '%2$s' END \n" + " ELSE NULL" + " END AS \"%4$s\"," + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'distance_orderable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%6$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'returnable')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%7$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_array')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%8$s\",\n" + " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_nulls')\n" + " THEN '%1$s' ELSE '%2$s' END AS \"%9$s\"\n", + gettext_noop("yes"), + gettext_noop("no"), + gettext_noop("ASC"), + gettext_noop("Nulls first"), + gettext_noop("Orderable"), + gettext_noop("Distance orderable"), + gettext_noop("Returnable"), + gettext_noop("Search array"), + gettext_noop("Search nulls")); + else + appendPQExpBuffer(&buf, + " CASE WHEN am.amcanorder THEN CASE\n" + " WHEN (i.indoption[a.attnum - 1] & %1$d) = 0\n" /* INDOPTION_DESC */ + " THEN '%2$s' ELSE '%3$s' END\n" + " ELSE NULL END AS \"%4$s\",\n" + " CASE WHEN am.amcanorder THEN CASE\n" + " WHEN (i.indoption[a.attnum - 1] & %5$d) <> 0\n" /* INDOPTION_NULLS_FIRST */ + " THEN '%2$s' ELSE '%3$s' END\n" + " ELSE NULL END AS \"%6$s\",\n" + " CASE WHEN am.amcanorder THEN '%2$s' ELSE '%3$s' END AS \"%7$s\",\n" + " CASE WHEN am.amcanorderbyop THEN '%2$s' ELSE '%3$s' END AS \"%8$s\",\n" + " CASE WHEN am.amsearcharray THEN '%2$s' ELSE '%3$s' END AS \"%9$s\",\n" + " CASE WHEN am.amsearchnulls THEN '%2$s' ELSE '%3$s' END AS \"%10$s\"\n", + INDOPTION_DESC, + gettext_noop("yes"), + gettext_noop("no"), + gettext_noop("ASC"), + INDOPTION_NULLS_FIRST, + gettext_noop("Nulls first"), + gettext_noop("Orderable"), + gettext_noop("Distance orderable"), + gettext_noop("Search array"), + gettext_noop("Search nulls")); + + appendPQExpBuffer(&buf, + "FROM pg_catalog.pg_class c\n" + " LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n" + " LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid\n" + " LEFT JOIN pg_catalog.pg_opclass o ON o.oid = (i.indclass::pg_catalog.oid[])[a.attnum - 1]\n" + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.opcnamespace\n"); + if (pset.sversion < 90600) + appendPQExpBuffer(&buf, + " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n"); + appendPQExpBuffer(&buf, + "WHERE c.oid = %s\n" + "ORDER BY a.attnum", + oid); + + res = PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + if (PQntuples(res) == 0) + { + PQclear(res); + return true; + } + + myopt.nullPrint = NULL; + myopt.title = psprintf(_("Index %s.%s"), nspname, idxname); + footers[0] = psprintf(_("Table: %s"), tabname); + footers[1] = psprintf(_("Access method: %s"), amname); + myopt.footers = footers; + myopt.topt.default_footer = false; + myopt.translate_header = true; + myopt.translate_columns = translate_columns; + myopt.n_translate_columns = lengthof(translate_columns); + + printQuery(res, &myopt, pset.queryFout, false, pset.logfile); + PQclear(res); + return true; +} diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 6c87401..f9c3ea6 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -131,4 +131,11 @@ extern bool describeAccessMethodOperatorClasses(const char *access_method_patter const char *opclass_pattern, bool verbose); +/* \dip */ +extern bool describeIndexProperties(const char *pattern, bool showSystem); + +/* \dicp */ +extern bool describeIndexColumnProperties(const char *indexPattern, + bool showSystem); + #endif /* DESCRIBE_H */ diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index dc591c3..dbb873e 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -249,6 +249,8 @@ slashUsage(unsigned short int pager) fprintf(output, _(" \\dFt[+] [PATTERN] list text search templates\n")); fprintf(output, _(" \\dg[S+] [PATTERN] list roles\n")); fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n")); + fprintf(output, _(" \\dip[S] [PATTERN] list indexes with properties\n")); + fprintf(output, _(" \\dicp[S][PATTERN] show index column properties\n")); fprintf(output, _(" \\dl list large objects, same as \\lo_list\n")); fprintf(output, _(" \\dL[S+] [PATTERN] list procedural languages\n")); fprintf(output, _(" \\dm[S+] [PATTERN] list materialized views\n")); diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 5d14a93..0e3a1eff 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1427,8 +1427,9 @@ psql_completion(const char *text, int start, int end) "\\d", "\\da", "\\dA", "\\dAp", "\\dAo", "\\dAp", "\\dAc", "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD", "\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df", - "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL", - "\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt", + "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dicp", "\\dip", + "\\dl", "\\dL", "\\dm", "\\dn", "\\do", "\\dO", + "\\dp", "\\dP", "\\dPi", "\\dPt", "\\drds", "\\dRs", "\\dRp", "\\ds", "\\dS", "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy", "\\e", "\\echo", "\\ef", "\\elif", "\\else", "\\encoding", diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index 4d17be3..2580bae 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -4803,3 +4803,19 @@ List operators of family related to access method brin | oid | | oid_minmax_ops | yes (1 row) +-- check printing info about indexes +\dip pg_am_name_index + Index properties + Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | Backward scan +------------+------------------+---------------+-------------+------------+-------------+--------------- + pg_catalog | pg_am_name_index | btree | yes | yes | yes | yes +(1 row) + +\dicp pg_am_name_index + Index pg_catalog.pg_am_name_index + Column name | Expr | Opclass | ASC | Nulls first | Orderable | Distance orderable | Returnable | Search array | Search nulls +-------------+--------+----------+-----+-------------+-----------+--------------------+------------+--------------+-------------- + amname | amname | name_ops | yes | no | yes | no | yes | yes | yes +Table: pg_am +Access method: btree + diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql index 77941c4..95e7dbe 100644 --- a/src/test/regress/sql/psql.sql +++ b/src/test/regress/sql/psql.sql @@ -1139,3 +1139,6 @@ drop role regress_partitioning_role; \dAp brin uuid_minmax_ops \dAp * pg_catalog.uuid_ops \dAc brin pg*.oid* +-- check printing info about indexes +\dip pg_am_name_index +\dicp pg_am_name_index -- 2.7.4 --------------251D77BCC012992979481C12-- ^ permalink raw reply [nested|flat] 3+ messages in thread
* [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ @ 2021-12-18 20:58 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw) show the size only with \dA++ and \dn++ (for which the single-plus commands have historically not done any slow operations). Also change to show the size only with \db++ and \l++ (for which it's useful to show the ACL without also doing any slow operations). \dt+ and \dP+ are not changed, since showing the table sizes seems to be their primary purpose (??) The idea for plusplus commands were previously discussed here. https://www.postgresql.org/message-id/20190506163359.GA29291%40alvherre.pgsql --- src/bin/psql/command.c | 20 +++++++++++------- src/bin/psql/describe.c | 46 +++++++++++++++++++++++++++++------------ src/bin/psql/describe.h | 8 +++---- 3 files changed, 50 insertions(+), 24 deletions(-) diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 1300869d797..667beb1b16e 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -369,6 +369,7 @@ exec_command(const char *cmd, else if (strcmp(cmd, "if") == 0) status = exec_command_if(scan_state, cstack, query_buf); else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 || + strcmp(cmd, "l++") == 0 || strcmp(cmd, "list++") == 0 || strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0) status = exec_command_list(scan_state, active_branch, cmd); else if (strncmp(cmd, "lo_", 3) == 0) @@ -754,6 +755,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; + int verbose = 0; bool show_verbose, show_system; @@ -761,7 +763,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; + + show_verbose = (bool) (verbose != 0); show_system = strchr(cmd, 'S') ? true : false; switch (cmd[1]) @@ -786,7 +791,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) { case '\0': case '+': - success = describeAccessMethods(pattern, show_verbose); + success = describeAccessMethods(pattern, verbose); break; case 'c': success = listOperatorClasses(pattern, pattern2, show_verbose); @@ -812,7 +817,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = describeAggregates(pattern, show_verbose, show_system); break; case 'b': - success = describeTablespaces(pattern, show_verbose); + success = describeTablespaces(pattern, verbose); break; case 'c': if (strncmp(cmd, "dconfig", 7) == 0) @@ -866,7 +871,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) success = listLanguages(pattern, show_verbose, show_system); break; case 'n': - success = listSchemas(pattern, show_verbose, show_system); + success = listSchemas(pattern, verbose, show_system); break; case 'o': success = exec_command_dfo(scan_state, cmd, pattern, @@ -1945,14 +1950,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd) if (active_branch) { char *pattern; - bool show_verbose; + int verbose = 0; pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); - show_verbose = strchr(cmd, '+') ? true : false; + for (const char *t = cmd; *t != '\0'; ++t) + verbose += *t == '+' ? 1 : 0; - success = listAllDbs(pattern, show_verbose); + success = listAllDbs(pattern, verbose); free(pattern); } diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 45f6a86b872..3be7a5b8dc8 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -139,12 +139,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem) * Takes an optional regexp to select particular access methods */ bool -describeAccessMethods(const char *pattern, bool verbose) +describeAccessMethods(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; printQueryOpt myopt = pset.popt; - static const bool translate_columns[] = {false, true, false, false}; + static const bool translate_columns[] = {false, true, false, false, false}; if (pset.sversion < 90600) { @@ -176,6 +176,11 @@ describeAccessMethods(const char *pattern, bool verbose) " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"", gettext_noop("Handler"), gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 170000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, @@ -214,7 +219,7 @@ describeAccessMethods(const char *pattern, bool verbose) * Takes an optional regexp to select particular tablespaces */ bool -describeTablespaces(const char *pattern, bool verbose) +describeTablespaces(const char *pattern, int verbose) { PQExpBufferData buf; PGresult *res; @@ -234,12 +239,18 @@ describeTablespaces(const char *pattern, bool verbose) { appendPQExpBufferStr(&buf, ",\n "); printACLColumn(&buf, "spcacl"); + + appendPQExpBuffer(&buf, + ",\n spcoptions AS \"%s\"", + gettext_noop("Options")); + + if (verbose > 1) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"", + gettext_noop("Size")); + appendPQExpBuffer(&buf, - ",\n spcoptions AS \"%s\"" - ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"" ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"", - gettext_noop("Options"), - gettext_noop("Size"), gettext_noop("Description")); } @@ -914,7 +925,7 @@ error_return: * for \l, \list, and -l switch */ bool -listAllDbs(const char *pattern, bool verbose) +listAllDbs(const char *pattern, int verbose) { PGresult *res; PQExpBufferData buf; @@ -961,20 +972,24 @@ listAllDbs(const char *pattern, bool verbose) gettext_noop("ICU Rules")); appendPQExpBufferStr(&buf, " "); printACLColumn(&buf, "d.datacl"); - if (verbose) + if (verbose > 1) appendPQExpBuffer(&buf, ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n" " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n" " ELSE 'No Access'\n" - " END as \"%s\"" + " END as \"%s\"", + gettext_noop("Size")); + + if (verbose > 0) + appendPQExpBuffer(&buf, ",\n t.spcname as \"%s\"" ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"", - gettext_noop("Size"), gettext_noop("Tablespace"), gettext_noop("Description")); + appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_database d\n"); - if (verbose) + if (verbose > 0) appendPQExpBufferStr(&buf, " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n"); @@ -5031,7 +5046,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem) * Describes schemas (namespaces) */ bool -listSchemas(const char *pattern, bool verbose, bool showSystem) +listSchemas(const char *pattern, int verbose, bool showSystem) { PQExpBufferData buf; PGresult *res; @@ -5053,6 +5068,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem) appendPQExpBuffer(&buf, ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"", gettext_noop("Description")); + + if (verbose > 1 && pset.sversion >= 170000) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"", + gettext_noop("Size")); } appendPQExpBufferStr(&buf, diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 24c0884a347..f11579a7bbc 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -13,10 +13,10 @@ extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem); /* \dA */ -extern bool describeAccessMethods(const char *pattern, bool verbose); +extern bool describeAccessMethods(const char *pattern, int verbose); /* \db */ -extern bool describeTablespaces(const char *pattern, bool verbose); +extern bool describeTablespaces(const char *pattern, int verbose); /* \df, \dfa, \dfn, \dft, \dfw, etc. */ extern bool describeFunctions(const char *functypes, const char *func_pattern, @@ -65,7 +65,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose); extern bool listTSTemplates(const char *pattern, bool verbose); /* \l */ -extern bool listAllDbs(const char *pattern, bool verbose); +extern bool listAllDbs(const char *pattern, int verbose); /* \dt, \di, \ds, \dS, etc. */ extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem); @@ -90,7 +90,7 @@ extern bool listCasts(const char *pattern, bool verbose); extern bool listCollations(const char *pattern, bool verbose, bool showSystem); /* \dn */ -extern bool listSchemas(const char *pattern, bool verbose, bool showSystem); +extern bool listSchemas(const char *pattern, int verbose, bool showSystem); /* \dew */ extern bool listForeignDataWrappers(const char *pattern, bool verbose); -- 2.34.1 --X119r+cMzLJwahiZ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0003-f-convert-the-other-verbose-to-int-too.patch" ^ permalink raw reply [nested|flat] 3+ messages in thread
* [PATCH v3 7/7] Handle pg_get_triggerdef default args in system_functions.sql @ 2025-12-09 19:51 Mark Wong <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Mark Wong @ 2025-12-09 19:51 UTC (permalink / raw) Modernize pg_get_triggerdef to use CREATE OR REPLACE FUNCTION to handle the optional pretty argument. --- src/backend/catalog/system_functions.sql | 7 +++++++ src/backend/utils/adt/ruleutils.c | 14 -------------- src/include/catalog/pg_proc.dat | 5 +---- src/include/catalog/pg_retired.dat | 1 + 4 files changed, 9 insertions(+), 18 deletions(-) diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql index a6f7cdf3a36..08b9b68d0cd 100644 --- a/src/backend/catalog/system_functions.sql +++ b/src/backend/catalog/system_functions.sql @@ -699,6 +699,13 @@ LANGUAGE INTERNAL PARALLEL SAFE AS 'pg_get_expr'; +CREATE OR REPLACE FUNCTION + pg_get_triggerdef(trigger oid, pretty bool DEFAULT false) +RETURNS TEXT +LANGUAGE INTERNAL +PARALLEL SAFE +AS 'pg_get_triggerdef'; + -- -- The default permissions for functions mean that anyone can execute them. -- A number of functions shouldn't be executable by just anyone, but rather diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index d87b361a093..598c916ddb6 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -807,20 +807,6 @@ pg_get_viewdef_worker(Oid viewoid, int prettyFlags, int wrapColumn) */ Datum pg_get_triggerdef(PG_FUNCTION_ARGS) -{ - Oid trigid = PG_GETARG_OID(0); - char *res; - - res = pg_get_triggerdef_worker(trigid, false); - - if (res == NULL) - PG_RETURN_NULL(); - - PG_RETURN_TEXT_P(string_to_text(res)); -} - -Datum -pg_get_triggerdef_ext(PG_FUNCTION_ARGS) { Oid trigid = PG_GETARG_OID(0); bool pretty = PG_GETARG_BOOL(1); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 4304ab220ba..bf00983b3ff 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3983,9 +3983,6 @@ proname => 'pg_get_partition_constraintdef', provolatile => 's', prorettype => 'text', proargtypes => 'oid', prosrc => 'pg_get_partition_constraintdef' }, -{ oid => '1662', descr => 'trigger description', - proname => 'pg_get_triggerdef', provolatile => 's', prorettype => 'text', - proargtypes => 'oid', prosrc => 'pg_get_triggerdef' }, { oid => '1665', descr => 'name of sequence for a serial column', proname => 'pg_get_serial_sequence', provolatile => 's', prorettype => 'text', proargtypes => 'text text', prosrc => 'pg_get_serial_sequence' }, @@ -8551,7 +8548,7 @@ prosrc => 'pg_timezone_names' }, { oid => '2730', descr => 'trigger description with pretty-print option', proname => 'pg_get_triggerdef', provolatile => 's', prorettype => 'text', - proargtypes => 'oid bool', prosrc => 'pg_get_triggerdef_ext' }, + proargtypes => 'oid bool', prosrc => 'pg_get_triggerdef' }, # asynchronous notifications { oid => '3035', diff --git a/src/include/catalog/pg_retired.dat b/src/include/catalog/pg_retired.dat index 8b5f58850f6..46764648040 100644 --- a/src/include/catalog/pg_retired.dat +++ b/src/include/catalog/pg_retired.dat @@ -22,6 +22,7 @@ { oid => '1640', proname => 'pg_get_viewdef' }, { oid => '1641', proname => 'pg_get_viewdef' }, { oid => '1643', proname => 'pg_get_indexdef' }, +{ oid => '1662', proname => 'pg_get_triggerdef' }, { oid => '1716', proname => 'pg_get_expr' } ] -- 2.43.0 --zd8Z8rlPOcTi77n/-- ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-12-09 19:51 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-07-15 14:11 [PATCH 2/2] Add psql index info commands Nikita Glukhov <[email protected]> 2021-12-18 20:58 [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]> 2025-12-09 19:51 [PATCH v3 7/7] Handle pg_get_triggerdef default args in system_functions.sql Mark Wong <[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