Re: [GENERAL] Temporary tables and miscellaneous schemas - Mailing list pgsql-patches

From Bruce Momjian
Subject Re: [GENERAL] Temporary tables and miscellaneous schemas
Date
Msg-id 200312210426.hBL4Q4V15913@candle.pha.pa.us
Whole thread Raw
Responses Re: [GENERAL] Temporary tables and miscellaneous schemas  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-patches
pgman wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > How about if we add a UNION that does:
> > >     UNION
> > >     SELECT 'non-local temp schemas skipped', NULL
> >
> > I think showing that would only be appropriate if we actually *did* skip
> > some.  Finding that out would complicate the query unduly IMHO.
> >
> > > I see a few goals here:
> > >     Prevent \dn from showing lots of lines for large installs
> > >     Show the local temp schema so people can query it
> >
> > If those are agreed to be the goals then we end up with your original
> > solution (or a working implementation of same anyway).
> >
> > I'd like to see some input from other people about what they want...
>
> I have added this to the TODO list:
>
>     * Have psql \dn show only visible schemas using current_schemas()
>
> I know there was talk of showing all schemas only in admin mode, but I
> don't think we want to implement different behavior until we have a more
> practical reason to have such a mode distiction.  Of course, \dn will
> have to be documented that is supresses non-visible schemas, and admins
> can always do a select from pg_namespace.

This patch uses current_schemas('true') to display only the schemas in
the current search path and implicit schemas.

It uses an array with =ANY(), which we already do in psql's describe.c
for groups.  I also had to use :: for casting because that's the only
way to cast function parameters, I think.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.101
diff -c -c -r1.101 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml    1 Dec 2003 22:21:54 -0000    1.101
--- doc/src/sgml/ref/psql-ref.sgml    21 Dec 2003 04:04:35 -0000
***************
*** 954,960 ****

          <listitem>
          <para>
!         Lists all available schemas (namespaces). If <replaceable
          class="parameter">pattern</replaceable> (a regular expression)
          is specified, only schemas whose names match the pattern are listed.
          </para>
--- 954,960 ----

          <listitem>
          <para>
!         Lists all visible schemas (namespaces). If <replaceable
          class="parameter">pattern</replaceable> (a regular expression)
          is specified, only schemas whose names match the pattern are listed.
          </para>
Index: src/bin/psql/describe.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.90
diff -c -c -r1.90 describe.c
*** src/bin/psql/describe.c    1 Dec 2003 22:21:54 -0000    1.90
--- src/bin/psql/describe.c    21 Dec 2003 04:04:37 -0000
***************
*** 1629,1639 ****
                        "SELECT n.nspname AS \"%s\",\n"
                        "       u.usename AS \"%s\"\n"
          "FROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
!                       "       ON n.nspowner=u.usesysid\n",
                        _("Name"),
                        _("Owner"));

!     processNamePattern(&buf, pattern, false, false,
                         NULL, "n.nspname", NULL,
                         NULL);

--- 1629,1641 ----
                        "SELECT n.nspname AS \"%s\",\n"
                        "       u.usename AS \"%s\"\n"
          "FROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
!                       "       ON n.nspowner=u.usesysid,\n"
!         "      (SELECT current_schemas('t'::boolean)) AS curr_schemas(name)\n"
!         "WHERE n.nspname = ANY(curr_schemas.name)\n",
                        _("Name"),
                        _("Owner"));

!     processNamePattern(&buf, pattern, true, false,
                         NULL, "n.nspname", NULL,
                         NULL);


pgsql-patches by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: fork/exec patch
Next
From: Zach Irmen
Date:
Subject: psql \i handling ~ in specified file name