Re: createuser/dropuser username - Mailing list pgsql-docs

Tom Lane wrote:
> Susanne Ebrecht <susanne@2ndQuadrant.com> writes:
> > PostgreSQL even is so intelligent - that when you say:
> > $ createdb "tEst" - it will ignore the quotes - you will get database
> > named tEst and not
> > named "tEst".
>
> Not true.  The reason you don't get quotes there is that the shell
> stripped them off.  Try '"tEst"' ...
>
> The current behavior is something that we settled on years ago, after
> thinking about the interaction between shell quoting rules and SQL
> quoting rules.  If we don't treat SQL names appearing on command lines
> as spelled literally, then you have to quote them if you don't want them
> downcased --- and doing that in a way that the shell won't mangle is
> unpleasant, as in my example above.  So we ruled that all utilities that
> take a SQL name from the command line should auto-quote the name.
>
> I'm pretty sure this is documented somewhere, but perhaps not in the
> place where Grzegorz looked for it ...

Uh, I did some digging after this email report and found it does need
some cleanup, which is done in the attached patch.

It removes quoting for table references in clusterdb and index/table
references in reindexdb, and adds a general documentation overview about
when case is preserved and suggests quoting, and adds documentation
where special quoting happens.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/ref/createdb.sgml b/doc/src/sgml/ref/createdb.sgml
new file mode 100644
index 3e50173..60a54cd
*** a/doc/src/sgml/ref/createdb.sgml
--- b/doc/src/sgml/ref/createdb.sgml
*************** PostgreSQL documentation
*** 90,96 ****
        <term><option>--tablespace=<replaceable class="parameter">tablespace</replaceable></></term>
        <listitem>
         <para>
!         Specifies the default tablespace for the database.
         </para>
        </listitem>
       </varlistentry>
--- 90,96 ----
        <term><option>--tablespace=<replaceable class="parameter">tablespace</replaceable></></term>
        <listitem>
         <para>
!         Specifies the default tablespace for the database (double-quoted internally).
         </para>
        </listitem>
       </varlistentry>
*************** PostgreSQL documentation
*** 153,159 ****
        <term><option>--owner=<replaceable class="parameter">owner</replaceable></></term>
        <listitem>
         <para>
!         Specifies the database user who will own the new database.
         </para>
        </listitem>
       </varlistentry>
--- 153,160 ----
        <term><option>--owner=<replaceable class="parameter">owner</replaceable></></term>
        <listitem>
         <para>
!         Specifies the database user who will own the new database
!         (double-quoted internally).
         </para>
        </listitem>
       </varlistentry>
*************** PostgreSQL documentation
*** 163,169 ****
        <term><option>--template=<replaceable class="parameter">template</replaceable></></term>
        <listitem>
         <para>
!         Specifies the template database from which to build this database.
         </para>
        </listitem>
       </varlistentry>
--- 164,171 ----
        <term><option>--template=<replaceable class="parameter">template</replaceable></></term>
        <listitem>
         <para>
!         Specifies the template database from which to build this
!         database (double-quoted internally).
         </para>
        </listitem>
       </varlistentry>
diff --git a/doc/src/sgml/ref/createlang.sgml b/doc/src/sgml/ref/createlang.sgml
new file mode 100644
index f01f298..7f9459a
*** a/doc/src/sgml/ref/createlang.sgml
--- b/doc/src/sgml/ref/createlang.sgml
*************** PostgreSQL documentation
*** 70,76 ****
        <term><replaceable class="parameter">langname</replaceable></term>
        <listitem>
         <para>
!         Specifies the name of the procedural language to be installed.
         </para>
        </listitem>
       </varlistentry>
--- 70,77 ----
        <term><replaceable class="parameter">langname</replaceable></term>
        <listitem>
         <para>
!         Specifies the name of the procedural language to be
!         installed.  (automatically lower-cased)
         </para>
        </listitem>
       </varlistentry>
diff --git a/doc/src/sgml/ref/droplang.sgml b/doc/src/sgml/ref/droplang.sgml
new file mode 100644
index 04c3a60..fa7e913
*** a/doc/src/sgml/ref/droplang.sgml
--- b/doc/src/sgml/ref/droplang.sgml
*************** PostgreSQL documentation
*** 73,78 ****
--- 73,79 ----
        <listitem>
         <para>
          Specifies the name of the procedural language to be removed.
+         (automatically lower-cased)
         </para>
        </listitem>
       </varlistentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
new file mode 100644
index 9ae8000..5fd6410
*** a/doc/src/sgml/reference.sgml
--- b/doc/src/sgml/reference.sgml
***************
*** 198,203 ****
--- 198,211 ----
      applications is that they can be run on any host, independent of
      where the database server resides.
     </para>
+
+    <para>
+     When specified on the command line, user and databases names have
+     their case preserved — the presence of spaces or special
+     characters might require quoting.  Table names and other identifiers
+     do not have their case preserved, except where documented, and
+     might require quoting.
+    </para>
    </partintro>

     &clusterdb;
diff --git a/src/bin/scripts/clusterdb.c b/src/bin/scripts/clusterdb.c
new file mode 100644
index f4c317a..3742091
*** a/src/bin/scripts/clusterdb.c
--- b/src/bin/scripts/clusterdb.c
*************** cluster_one_database(const char *dbname,
*** 177,183 ****
      if (verbose)
          appendPQExpBuffer(&sql, " VERBOSE");
      if (table)
!         appendPQExpBuffer(&sql, " %s", fmtId(table));
      appendPQExpBuffer(&sql, ";\n");

      conn = connectDatabase(dbname, host, port, username, prompt_password, progname);
--- 177,183 ----
      if (verbose)
          appendPQExpBuffer(&sql, " VERBOSE");
      if (table)
!         appendPQExpBuffer(&sql, " %s", table);
      appendPQExpBuffer(&sql, ";\n");

      conn = connectDatabase(dbname, host, port, username, prompt_password, progname);
diff --git a/src/bin/scripts/createlang.c b/src/bin/scripts/createlang.c
new file mode 100644
index c2153db..2f667e8
*** a/src/bin/scripts/createlang.c
--- b/src/bin/scripts/createlang.c
*************** main(int argc, char *argv[])
*** 164,169 ****
--- 164,170 ----
          exit(1);
      }

+     /* lower case language name */
      for (p = langname; *p; p++)
          if (*p >= 'A' && *p <= 'Z')
              *p += ('a' - 'A');
diff --git a/src/bin/scripts/droplang.c b/src/bin/scripts/droplang.c
new file mode 100644
index 7fadee0..f136a76
*** a/src/bin/scripts/droplang.c
--- b/src/bin/scripts/droplang.c
*************** main(int argc, char *argv[])
*** 165,170 ****
--- 165,171 ----
          exit(1);
      }

+     /* lower case language name */
      for (p = langname; *p; p++)
          if (*p >= 'A' && *p <= 'Z')
              *p += ('a' - 'A');
diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c
new file mode 100644
index 53fff01..caeed75
*** a/src/bin/scripts/reindexdb.c
--- b/src/bin/scripts/reindexdb.c
*************** reindex_one_database(const char *name, c
*** 223,231 ****

      appendPQExpBuffer(&sql, "REINDEX");
      if (strcmp(type, "TABLE") == 0)
!         appendPQExpBuffer(&sql, " TABLE %s", fmtId(name));
      else if (strcmp(type, "INDEX") == 0)
!         appendPQExpBuffer(&sql, " INDEX %s", fmtId(name));
      else if (strcmp(type, "DATABASE") == 0)
          appendPQExpBuffer(&sql, " DATABASE %s", fmtId(name));
      appendPQExpBuffer(&sql, ";\n");
--- 223,231 ----

      appendPQExpBuffer(&sql, "REINDEX");
      if (strcmp(type, "TABLE") == 0)
!         appendPQExpBuffer(&sql, " TABLE %s", name);
      else if (strcmp(type, "INDEX") == 0)
!         appendPQExpBuffer(&sql, " INDEX %s", name);
      else if (strcmp(type, "DATABASE") == 0)
          appendPQExpBuffer(&sql, " DATABASE %s", fmtId(name));
      appendPQExpBuffer(&sql, ";\n");