psql: improve tab-complete's handling of variant SQL names.

This patch improves tab completion's ability to deal with
valid variant spellings of SQL identifiers.  Notably:

* Unquoted upper-case identifiers are now downcased as the backend
would do, allowing them to be completed correctly.

* Tab completion can now match identifiers that are quoted even
though they don't need to be; for example "f<TAB> now completes
to "foo" if that's the only available name.  Previously, only
names that require quotes would be offered.

* Schema-qualified identifiers are now supported where SQL syntax
allows it; many lesser-used completion rules neglected this.

* Completion operations that refer back to some previously-typed
name (for example, to complete names of columns belonging to a
previously-mentioned table) now allow variant spellings of the
previous name too.

In addition, performance of tab completion queries has been
improved for databases containing many objects, although
you'd only be likely to notice with a heavily-loaded server.

Authors of future tab-completion patches should note that this
commit changes many details about how tab completion queries
must be written:

* Tab completion queries now deal in raw object names; do not
use quote_ident().

* The name-matching restriction in a query must now be written
as "outputcol LIKE '%s'", not "substring(outputcol,1,%d)='%s'".

* The SchemaQuery mechanism has been extended so that it can
handle queries that refer back to a previous name.  Most completion
queries that do that should be converted to SchemaQuery form.
Only consider using a literal query if the previous name can
never be schema-qualified.  Don't use a literal query if the
name-to-be-completed can validly be schema-qualified, either.

* Use set_completion_reference() to specify which word is the previous
name to consider, for either a SchemaQuery or a literal query.

* If you want to offer some keywords in addition to a query result
(for example, offer COLUMN in addition to column names after
"ALTER TABLE t RENAME"), do not use the old hack of tacking the
keywords on with UNION.  Instead use the new QUERY_PLUS macros
to write such keywords separately from the query proper.  The
"addon" macro arguments that used to be used for this purpose
are gone.

* If your query returns something that's not a SQL identifier
(such as an attribute number or enum label), use the new
QUERY_VERBATIM macros to prevent the result from incorrectly
getting double-quoted.  You may still need to use quote_literal
in such a query, too.

Tom Lane and Haiying Tang

Discussion: https://postgr.es/m/a63cbd45e3884cf9b3961c2a6a95dcb7@G08CNEXMBPEKD05.g08.fujitsu.local

1 files changed, 51 insertions, 15 deletions

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 1ab200a4adc..caabb06c537 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -318,9 +318,9 @@ EOF
       <term><option>--no-readline</option></term>
       <listitem>
       <para>
-       Do not use <application>Readline</application> for line editing and do
-       not use the command history.
-       This can be useful to turn off tab expansion when cutting and pasting.
+       Do not use <application>Readline</application> for line editing and
+       do not use the command history (see
+       <xref linkend="app-psql-readline"/> below).
       </para>
       </listitem>
     </varlistentry>
@@ -4562,21 +4562,47 @@ testdb=&gt; \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
 
    </refsect3>
 
-   <refsect3>
+   <refsect3 id="app-psql-readline">
     <title>Command-Line Editing</title>
 
+   <indexterm>
+    <primary>Readline</primary>
+    <secondary>in psql</secondary>
+   </indexterm>
+   <indexterm>
+    <primary>libedit</primary>
+    <secondary>in psql</secondary>
+   </indexterm>
+
+    <para>
+    <application>psql</application> uses
+    the <application>Readline</application>
+    or <application>libedit</application> library, if available, for
+    convenient line editing and retrieval.  The command history is
+    automatically saved when <application>psql</application> exits and is
+    reloaded when <application>psql</application> starts up.  Type
+    up-arrow or control-P to retrieve previous lines.
+    </para>
+
+    <para>
+    You can also use tab completion to fill in partially-typed keywords
+    and SQL object names in many (by no means all) contexts.  For example,
+    at the start of a command, typing <literal>ins</literal> and pressing
+    TAB will fill in <literal>insert into </literal>.  Then, typing a few
+    characters of a table or schema name and pressing TAB will fill in the
+    unfinished name, or offer a menu of possible completions when there's
+    more than one.  (Depending on the library in use, you may need to
+    press TAB more than once to get a menu.)
+    </para>
+
     <para>
-    <application>psql</application> supports the <application>Readline</application>
-    library for convenient line editing and retrieval. The command
-    history is automatically saved when <application>psql</application>
-    exits and is reloaded when
-    <application>psql</application> starts up. Tab-completion is also
-    supported, although the completion logic makes no claim to be an
-    <acronym>SQL</acronym> parser.  The queries generated by tab-completion
-    can also interfere with other SQL commands, e.g., <literal>SET
-    TRANSACTION ISOLATION LEVEL</literal>.
-    If for some reason you do not like the tab completion, you
-    can turn it off by putting this in a file named
+    Tab completion for SQL object names requires sending queries to the
+    server to find possible matches.  In some contexts this can interfere
+    with other operations.  For example, after <command>BEGIN</command>
+    it will be too late to issue <command>SET TRANSACTION ISOLATION
+    LEVEL</command> if a tab-completion query is issued in between.
+    If you do not want tab completion at all, you
+    can turn it off permanently by putting this in a file named
     <filename>.inputrc</filename> in your home directory:
 <programlisting>
 $if psql
@@ -4587,6 +4613,16 @@ $endif
     <application>Readline</application> feature. Read its documentation
     for further details.)
     </para>
+
+    <para>
+     The <option>-n</option> (<option>--no-readline</option>) command line
+     option can also be useful to disable use
+     of <application>Readline</application> for a single run
+     of <application>psql</application>.  This prevents tab completion,
+     use or recording of command line history, and editing of multi-line
+     commands.  It is particularly useful when you need to copy-and-paste
+     text that contains TAB characters.
+    </para>
    </refsect3>
   </refsect2>
  </refsect1>