Maybe the best we are going to do is to have any pattern supplied to \d*
assume 'S' (include system objects). I actually have a patch that does
that, attached. (It is from January so might need adjustment.)
---------------------------------------------------------------------------
Robert Haas wrote:
> On Sun, Mar 29, 2009 at 1:32 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> > Now I *have* a user function named sin(), it's not getting called
> > (which might surprise me if I didn't know there was a conflicting
> > system function) and \df doesn't show me either one.
> >
> > I actually was expecting the above example to show me the user function,
> > which I was then going to rant about being a lie. ?But the actual
> > behavior is even worse than that.
>
> Well, that is clearly a bug.
>
> > There is not anything that is not broken about HEAD's behavior,
> > and the sooner we admit that the sooner we can get to a fix.
> > Slicing the categorization more finely or in different ways is
> > not going to improve matters: the concept that there is a categorization
> > that will make it hide requested objects is wrong to begin with.
>
> Well, by that argument, 8.3 is broken, too, because it hides
> pg_catalog tables, views, sequences, and indices. It's fair to say
> that the system shouldn't hide "requested" objects, but sometimes
> people want request only the objects that they created, and not the
> ones that are part of the system. In 8.3, if you want to list all of
> the functions you've defined (as opposed to the ones that came with
> the system), you have a couple of not-so-fun options:
>
> 1. pg_dump -s | grep 'CREATE.*FUNCTION'
> 2. looking up the **40-line** query that \df issues, modifying it to
> exclude system functions, and running it by hand
>
> This has been a huge irritation to me for many years, and (whatever
> else you can say about the patch that started all this) it makes this
> particular thing a whole lot easier. I'd like to find a way to still
> have that be easy while fixing some of the other issues.
>
> Even in 8.3, we have this oddness:
>
> \dt pg_index
> No matching relations found.
> select sum(1) from pg_index;
> sum
> -----
> 332
> (1 row)
>
> One idea I had is to issue some kind of a warning if a \d command
> matches system objects that are excluded from the output, like this:
>
> note: %d system objects also found, use %s to display
>
> ...Robert
--
Bruce Momjian <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/bin/psql/describe.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.195
diff -c -c -r1.195 describe.c
*** src/bin/psql/describe.c 6 Jan 2009 23:01:57 -0000 1.195
--- src/bin/psql/describe.c 15 Jan 2009 16:50:45 -0000
***************
*** 94,100 ****
"WHERE p.proisagg\n",
gettext_noop("Description"));
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 94,100 ----
"WHERE p.proisagg\n",
gettext_noop("Description"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 281,287 ****
" 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");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 281,287 ----
" 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");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 372,378 ****
else
appendPQExpBuffer(&buf, " AND t.typname !~ '^_'\n");
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
/* Match name pattern against either internal or external name */
--- 372,378 ----
else
appendPQExpBuffer(&buf, " AND t.typname !~ '^_'\n");
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
/* Match name pattern against either internal or external name */
***************
*** 427,436 ****
gettext_noop("Result type"),
gettext_noop("Description"));
! if (!showSystem)
appendPQExpBuffer(&buf, " WHERE n.nspname <> 'pg_catalog'\n");
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem, true,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
--- 427,436 ----
gettext_noop("Result type"),
gettext_noop("Description"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " WHERE n.nspname <> 'pg_catalog'\n");
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, true,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
***************
*** 620,626 ****
" WHERE p.proisagg\n",
gettext_noop("aggregate"));
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 620,626 ----
" WHERE p.proisagg\n",
gettext_noop("aggregate"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 643,649 ****
" AND NOT p.proisagg\n",
gettext_noop("function"));
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 643,649 ----
" AND NOT p.proisagg\n",
gettext_noop("function"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 661,670 ****
" 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");
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
--- 661,670 ----
" 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");
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
***************
*** 679,688 ****
" 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");
! 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)");
--- 679,688 ----
" 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");
! 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)");
***************
*** 703,709 ****
gettext_noop("view"),
gettext_noop("index"),
gettext_noop("sequence"));
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 703,709 ----
gettext_noop("view"),
gettext_noop("index"),
gettext_noop("sequence"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 723,729 ****
" WHERE r.rulename != '_RETURN'\n",
gettext_noop("rule"));
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
/* XXX not sure what to do about visibility rule here? */
--- 723,729 ----
" WHERE r.rulename != '_RETURN'\n",
gettext_noop("rule"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
/* XXX not sure what to do about visibility rule here? */
***************
*** 742,752 ****
" JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
" 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");
/* 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)");
--- 742,752 ----
" JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
" 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");
/* 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)");
***************
*** 1961,1967 ****
appendPQExpBuffer(&buf, "'i',");
if (showSeq)
appendPQExpBuffer(&buf, "'S',");
! if (showSystem && showTables)
appendPQExpBuffer(&buf, "'s',");
appendPQExpBuffer(&buf, "''"); /* dummy */
appendPQExpBuffer(&buf, ")\n");
--- 1961,1967 ----
appendPQExpBuffer(&buf, "'i',");
if (showSeq)
appendPQExpBuffer(&buf, "'S',");
! if ((showSystem || pattern) && showTables)
appendPQExpBuffer(&buf, "'s',");
appendPQExpBuffer(&buf, "''"); /* dummy */
appendPQExpBuffer(&buf, ")\n");
***************
*** 1971,1983 ****
* pg_catalog). Otherwise, suppress system objects, including those in
* pg_catalog and pg_toast. (We don't want to hide temp tables though.)
*/
! if (showSystem)
! appendPQExpBuffer(&buf,
! " AND n.nspname = 'pg_catalog'\n");
! else
! appendPQExpBuffer(&buf,
! " AND n.nspname <> 'pg_catalog'\n"
! " AND n.nspname !~ '^pg_toast'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "c.relname", NULL,
--- 1971,1986 ----
* pg_catalog). Otherwise, suppress system objects, including those in
* pg_catalog and pg_toast. (We don't want to hide temp tables though.)
*/
! if (!pattern)
! {
! if (showSystem)
! appendPQExpBuffer(&buf,
! " AND n.nspname = 'pg_catalog'\n");
! else
! appendPQExpBuffer(&buf,
! " AND n.nspname <> 'pg_catalog'\n"
! " AND n.nspname !~ '^pg_toast'\n");
! }
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "c.relname", NULL,
***************
*** 2046,2052 ****
gettext_noop("Modifier"),
gettext_noop("Check"));
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 2049,2055 ----
gettext_noop("Modifier"),
gettext_noop("Check"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 2101,2107 ****
gettext_noop("yes"), gettext_noop("no"),
gettext_noop("Default?"));
! if (!showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 2104,2110 ----
gettext_noop("yes"), gettext_noop("no"),
gettext_noop("Default?"));
! if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
