Bruce Momjian wrote:
> I have applied a simplified version of your patch, attached, that adds
> just a "AND <>" line to the query; I was a little concerned that IN
> might affect performance, and the macros seemed kind of complicated.
>
Thanks.
> Also, since my patch this morning any pattern will also trigger
> information_schema lookups, not just 'S'.
>
Hmm, this is a problem -- "\dX *.*" now shows all objects, and there is
no way to list only user objects. This is especially a problem if user
objects are scattered in different schemas.
I'd suggest that the U option to be reintroduced for the purpouse to describe
only user objects. One possible solution is to modifiy exec_command so that
show_system is set to true if S or pattern present, and false if U is
specified.
Proposed patch attached.
regards,
Martin
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
***************
*** 334,340 **** exec_command(const char *cmd,
OT_NORMAL, NULL, true);
show_verbose = strchr(cmd, '+') ? true : false;
! show_system = strchr(cmd, 'S') ? true : false;
switch (cmd[1])
{
--- 334,348 ----
OT_NORMAL, NULL, true);
show_verbose = strchr(cmd, '+') ? true : false;
!
! /*
! * Show system objects if S or pattern present. Use U to
! * describe only user objects.
! */
! if ((strchr(cmd, 'S') || pattern) && !strchr(cmd, 'U'))
! show_system = true;
! else
! show_system = false;
switch (cmd[1])
{
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
***************
*** 94,100 **** describeAggregates(const char *pattern, bool verbose, bool showSystem)
"WHERE p.proisagg\n",
gettext_noop("Description"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
--- 94,100 ----
"WHERE p.proisagg\n",
gettext_noop("Description"));
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
***************
*** 282,288 **** describeFunctions(const char *pattern, bool verbose, bool showSystem)
" AND p.proargtypes[0] IS DISTINCT FROM 'pg_catalog.cstring'::pg_catalog.regtype\n"
" AND NOT p.proisagg\n");
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
--- 282,288 ----
" AND p.proargtypes[0] IS DISTINCT FROM 'pg_catalog.cstring'::pg_catalog.regtype\n"
" AND NOT p.proisagg\n");
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
***************
*** 374,380 **** describeTypes(const char *pattern, bool verbose, bool showSystem)
else
appendPQExpBuffer(&buf, " AND t.typname !~ '^_'\n");
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
--- 374,380 ----
else
appendPQExpBuffer(&buf, " AND t.typname !~ '^_'\n");
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
***************
*** 430,440 **** describeOperators(const char *pattern, bool showSystem)
gettext_noop("Result type"),
gettext_noop("Description"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, true,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
--- 430,440 ----
gettext_noop("Result type"),
gettext_noop("Description"));
! if (!showSystem)
appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem, true,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
***************
*** 635,641 **** objectDescription(const char *pattern, bool showSystem)
" WHERE p.proisagg\n",
gettext_noop("aggregate"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
--- 635,641 ----
" WHERE p.proisagg\n",
gettext_noop("aggregate"));
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
***************
*** 659,665 **** objectDescription(const char *pattern, bool showSystem)
" AND NOT p.proisagg\n",
gettext_noop("function"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
--- 659,665 ----
" AND NOT p.proisagg\n",
gettext_noop("function"));
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
***************
*** 678,688 **** objectDescription(const char *pattern, bool showSystem)
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
gettext_noop("operator"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
--- 678,688 ----
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
gettext_noop("operator"));
! if (!showSystem)
appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
***************
*** 697,707 **** objectDescription(const char *pattern, bool showSystem)
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
gettext_noop("data type"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
"n.nspname", "pg_catalog.format_type(t.oid, NULL)",
NULL,
"pg_catalog.pg_type_is_visible(t.oid)");
--- 697,707 ----
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
gettext_noop("data type"));
! if (!showSystem)
appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "pg_catalog.format_type(t.oid, NULL)",
NULL,
"pg_catalog.pg_type_is_visible(t.oid)");
***************
*** 723,729 **** objectDescription(const char *pattern, bool showSystem)
gettext_noop("index"),
gettext_noop("sequence"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
--- 723,729 ----
gettext_noop("index"),
gettext_noop("sequence"));
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
***************
*** 744,750 **** objectDescription(const char *pattern, bool showSystem)
" WHERE r.rulename != '_RETURN'\n",
gettext_noop("rule"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
--- 744,750 ----
" WHERE r.rulename != '_RETURN'\n",
gettext_noop("rule"));
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
***************
*** 765,776 **** objectDescription(const char *pattern, bool showSystem)
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
gettext_noop("trigger"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
/* XXX not sure what to do about visibility rule here? */
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
"n.nspname", "t.tgname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
--- 765,776 ----
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
gettext_noop("trigger"));
! if (!showSystem)
appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
/* XXX not sure what to do about visibility rule here? */
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "t.tgname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
***************
*** 821,831 **** describeTableDetails(const char *pattern, bool verbose, bool showSystem)
"FROM pg_catalog.pg_class c\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
"n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
--- 821,831 ----
"FROM pg_catalog.pg_class c\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
! if (!showSystem)
appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
***************
*** 2027,2033 **** listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBuffer(&buf, "''"); /* dummy */
appendPQExpBuffer(&buf, ")\n");
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n"
" AND n.nspname !~ '^pg_toast'\n");
--- 2027,2033 ----
appendPQExpBuffer(&buf, "''"); /* dummy */
appendPQExpBuffer(&buf, ")\n");
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n"
" AND n.nspname !~ '^pg_toast'\n");
***************
*** 2100,2106 **** listDomains(const char *pattern, bool showSystem)
gettext_noop("Modifier"),
gettext_noop("Check"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
--- 2100,2106 ----
gettext_noop("Modifier"),
gettext_noop("Check"));
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
***************
*** 2156,2162 **** listConversions(const char *pattern, bool showSystem)
gettext_noop("yes"), gettext_noop("no"),
gettext_noop("Default?"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
--- 2156,2162 ----
gettext_noop("yes"), gettext_noop("no"),
gettext_noop("Default?"));
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
