Re: Support tab completion for upper character inputs in psql - Mailing list pgsql-hackers

From Tom Lane
Subject Re: Support tab completion for upper character inputs in psql
Date
Msg-id 2304294.1643152287@sss.pgh.pa.us
Whole thread Raw
In response to RE: Support tab completion for upper character inputs in psql  ("tanghy.fnst@fujitsu.com" <tanghy.fnst@fujitsu.com>)
Responses Re: Support tab completion for upper character inputs in psql
List pgsql-hackers
I spent some time contemplating my navel about the concerns I raised
upthread about double-quoted identifiers.  I concluded that the reason
things don't work well in that area is that we're trying to get all the
work done by applying quote_ident() on the backend side and then
ignoring quoting considerations in tab-complete itself.  That sort of
works, but not terribly well.  The currently proposed patch is sticking
a toe into the water of dealing with quoting/downcasing in tab-complete,
but we need to go a lot further.  I propose that we ought to drop the
use of quote_ident() in the tab completion queries altogether, instead
having the backend return names as-is, and doing all the dequoting and
requoting work in tab-complete.

Attached is a very-much-WIP patch along these lines.  I make no
pretense that it's complete; no doubt some of the individual
queries are broken or don't return quite the results we want.
But it seems to act the way I think it should for relation names.

One thing I'm particularly unsure what to do with is the queries
for type names, which want to match against the output of
format_type, which'll already have applied quote_ident.  We can
probably hack something up there, but I ran out of time to mess
with that for today.

Anyway, I wanted to post this just to see what people think of
going in this direction.

            regards, tom lane

PS: I omitted the proposed regression test changes here.
Many of them are not at all portable --- different versions
of readline/libedit will produce different control character
sequences for backspacing, for example.  I got a lot of
failures when I tried to use those tests with this patch;
I've not run down which ones are test portability problems,
which are due to intentional behavior changes in this patch,
and which are due to breakage I've not fixed yet.

diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 502b5c5751..2dadf7d945 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -47,6 +47,7 @@
 #include "catalog/pg_class_d.h"
 #include "common.h"
 #include "libpq-fe.h"
+#include "mb/pg_wchar.h"
 #include "pqexpbuffer.h"
 #include "settings.h"
 #include "stringutils.h"
@@ -148,8 +149,8 @@ typedef struct SchemaQuery
     const char *namespace;

     /*
-     * Result --- the appropriately-quoted name to return, in the case of an
-     * unqualified name.  For example, "pg_catalog.quote_ident(c.relname)".
+     * Result --- the (unquoted) name to return, in the case of an unqualified
+     * name.  For example, "c.relname".
      */
     const char *result;

@@ -315,7 +316,7 @@ do { \
         completion_info_charp = _completion_type; \
         completion_info_charp2 = _completion_schema; \
     } \
-    matches = rl_completion_matches(text, complete_from_query); \
+    matches = rl_completion_matches(text, complete_from_query_verbatim); \
 } while (0)

 #define COMPLETE_WITH_FUNCTION_ARG(function) \
@@ -357,14 +358,14 @@ static const SchemaQuery Query_for_list_of_aggregates[] = {
         .selcondition = "p.prokind = 'a'",
         .viscondition = "pg_catalog.pg_function_is_visible(p.oid)",
         .namespace = "p.pronamespace",
-        .result = "pg_catalog.quote_ident(p.proname)",
+        .result = "p.proname",
     },
     {
         .catname = "pg_catalog.pg_proc p",
         .selcondition = "p.proisagg",
         .viscondition = "pg_catalog.pg_function_is_visible(p.oid)",
         .namespace = "p.pronamespace",
-        .result = "pg_catalog.quote_ident(p.proname)",
+        .result = "p.proname",
     }
 };

@@ -378,7 +379,7 @@ static const SchemaQuery Query_for_list_of_datatypes = {
     .viscondition = "pg_catalog.pg_type_is_visible(t.oid)",
     .namespace = "t.typnamespace",
     .result = "pg_catalog.format_type(t.oid, NULL)",
-    .qualresult = "pg_catalog.quote_ident(t.typname)",
+    .qualresult = "t.typname",
 };

 static const SchemaQuery Query_for_list_of_composite_datatypes = {
@@ -390,7 +391,7 @@ static const SchemaQuery Query_for_list_of_composite_datatypes = {
     .viscondition = "pg_catalog.pg_type_is_visible(t.oid)",
     .namespace = "t.typnamespace",
     .result = "pg_catalog.format_type(t.oid, NULL)",
-    .qualresult = "pg_catalog.quote_ident(t.typname)",
+    .qualresult = "t.typname",
 };

 static const SchemaQuery Query_for_list_of_domains = {
@@ -398,7 +399,7 @@ static const SchemaQuery Query_for_list_of_domains = {
     .selcondition = "t.typtype = 'd'",
     .viscondition = "pg_catalog.pg_type_is_visible(t.oid)",
     .namespace = "t.typnamespace",
-    .result = "pg_catalog.quote_ident(t.typname)",
+    .result = "t.typname",
 };

 /* Note: this intentionally accepts aggregates as well as plain functions */
@@ -409,13 +410,13 @@ static const SchemaQuery Query_for_list_of_functions[] = {
         .selcondition = "p.prokind != 'p'",
         .viscondition = "pg_catalog.pg_function_is_visible(p.oid)",
         .namespace = "p.pronamespace",
-        .result = "pg_catalog.quote_ident(p.proname)",
+        .result = "p.proname",
     },
     {
         .catname = "pg_catalog.pg_proc p",
         .viscondition = "pg_catalog.pg_function_is_visible(p.oid)",
         .namespace = "p.pronamespace",
-        .result = "pg_catalog.quote_ident(p.proname)",
+        .result = "p.proname",
     }
 };

@@ -426,7 +427,7 @@ static const SchemaQuery Query_for_list_of_procedures[] = {
         .selcondition = "p.prokind = 'p'",
         .viscondition = "pg_catalog.pg_function_is_visible(p.oid)",
         .namespace = "p.pronamespace",
-        .result = "pg_catalog.quote_ident(p.proname)",
+        .result = "p.proname",
     },
     {
         /* not supported in older versions */
@@ -438,7 +439,7 @@ static const SchemaQuery Query_for_list_of_routines = {
     .catname = "pg_catalog.pg_proc p",
     .viscondition = "pg_catalog.pg_function_is_visible(p.oid)",
     .namespace = "p.pronamespace",
-    .result = "pg_catalog.quote_ident(p.proname)",
+    .result = "p.proname",
 };

 static const SchemaQuery Query_for_list_of_sequences = {
@@ -446,7 +447,7 @@ static const SchemaQuery Query_for_list_of_sequences = {
     .selcondition = "c.relkind IN (" CppAsString2(RELKIND_SEQUENCE) ")",
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };

 static const SchemaQuery Query_for_list_of_foreign_tables = {
@@ -454,7 +455,7 @@ static const SchemaQuery Query_for_list_of_foreign_tables = {
     .selcondition = "c.relkind IN (" CppAsString2(RELKIND_FOREIGN_TABLE) ")",
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };

 static const SchemaQuery Query_for_list_of_tables = {
@@ -464,7 +465,7 @@ static const SchemaQuery Query_for_list_of_tables = {
     CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };

 static const SchemaQuery Query_for_list_of_partitioned_tables = {
@@ -472,7 +473,7 @@ static const SchemaQuery Query_for_list_of_partitioned_tables = {
     .selcondition = "c.relkind IN (" CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };

 static const SchemaQuery Query_for_list_of_views = {
@@ -480,7 +481,7 @@ static const SchemaQuery Query_for_list_of_views = {
     .selcondition = "c.relkind IN (" CppAsString2(RELKIND_VIEW) ")",
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };

 static const SchemaQuery Query_for_list_of_matviews = {
@@ -488,7 +489,7 @@ static const SchemaQuery Query_for_list_of_matviews = {
     .selcondition = "c.relkind IN (" CppAsString2(RELKIND_MATVIEW) ")",
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };

 static const SchemaQuery Query_for_list_of_indexes = {
@@ -498,7 +499,7 @@ static const SchemaQuery Query_for_list_of_indexes = {
     CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };

 static const SchemaQuery Query_for_list_of_partitioned_indexes = {
@@ -506,7 +507,7 @@ static const SchemaQuery Query_for_list_of_partitioned_indexes = {
     .selcondition = "c.relkind = " CppAsString2(RELKIND_PARTITIONED_INDEX),
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };


@@ -515,7 +516,7 @@ static const SchemaQuery Query_for_list_of_relations = {
     .catname = "pg_catalog.pg_class c",
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };

 /* partitioned relations */
@@ -525,14 +526,14 @@ static const SchemaQuery Query_for_list_of_partitioned_relations = {
     ", " CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };

 static const SchemaQuery Query_for_list_of_operator_families = {
     .catname = "pg_catalog.pg_opfamily c",
     .viscondition = "pg_catalog.pg_opfamily_is_visible(c.oid)",
     .namespace = "c.opfnamespace",
-    .result = "pg_catalog.quote_ident(c.opfname)",
+    .result = "c.opfname",
 };

 /* Relations supporting INSERT, UPDATE or DELETE */
@@ -545,7 +546,7 @@ static const SchemaQuery Query_for_list_of_updatables = {
     CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };

 /* Relations supporting SELECT */
@@ -560,7 +561,7 @@ static const SchemaQuery Query_for_list_of_selectables = {
     CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };

 /* Relations supporting TRUNCATE */
@@ -572,7 +573,7 @@ static const SchemaQuery Query_for_list_of_truncatables = {
     CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };

 /* Relations supporting GRANT are currently same as those supporting SELECT */
@@ -588,7 +589,7 @@ static const SchemaQuery Query_for_list_of_analyzables = {
     CppAsString2(RELKIND_FOREIGN_TABLE) ")",
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };

 /* Relations supporting index creation */
@@ -600,7 +601,7 @@ static const SchemaQuery Query_for_list_of_indexables = {
     CppAsString2(RELKIND_MATVIEW) ")",
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };

 /*
@@ -617,7 +618,7 @@ static const SchemaQuery Query_for_list_of_clusterables = {
     CppAsString2(RELKIND_MATVIEW) ")",
     .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
     .namespace = "c.relnamespace",
-    .result = "pg_catalog.quote_ident(c.relname)",
+    .result = "c.relname",
 };

 static const SchemaQuery Query_for_list_of_constraints_with_schema = {
@@ -625,14 +626,14 @@ static const SchemaQuery Query_for_list_of_constraints_with_schema = {
     .selcondition = "c.conrelid <> 0",
     .viscondition = "true",        /* there is no pg_constraint_is_visible */
     .namespace = "c.connamespace",
-    .result = "pg_catalog.quote_ident(c.conname)",
+    .result = "c.conname",
 };

 static const SchemaQuery Query_for_list_of_statistics = {
     .catname = "pg_catalog.pg_statistic_ext s",
     .viscondition = "pg_catalog.pg_statistics_obj_is_visible(s.oid)",
     .namespace = "s.stxnamespace",
-    .result = "pg_catalog.quote_ident(s.stxname)",
+    .result = "s.stxname",
 };

 static const SchemaQuery Query_for_list_of_collations = {
@@ -640,7 +641,7 @@ static const SchemaQuery Query_for_list_of_collations = {
     .selcondition = "c.collencoding IN (-1, pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding()))",
     .viscondition = "pg_catalog.pg_collation_is_visible(c.oid)",
     .namespace = "c.collnamespace",
-    .result = "pg_catalog.quote_ident(c.collname)",
+    .result = "c.collname",
 };


@@ -659,13 +660,13 @@ static const SchemaQuery Query_for_list_of_collations = {
  */

 #define Query_for_list_of_attributes \
-"SELECT pg_catalog.quote_ident(attname) "\
+"SELECT attname "\
 "  FROM pg_catalog.pg_attribute a, pg_catalog.pg_class c "\
 " WHERE c.oid = a.attrelid "\
 "   AND a.attnum > 0 "\
 "   AND NOT a.attisdropped "\
-"   AND substring(pg_catalog.quote_ident(attname),1,%d)='%s' "\
-"   AND (pg_catalog.quote_ident(relname)='%s' "\
+"   AND substring(attname,1,%d)='%s' "\
+"   AND (relname='%s' "\
 "        OR '\"' || relname || '\"'='%s') "\
 "   AND pg_catalog.pg_table_is_visible(c.oid)"

@@ -676,21 +677,21 @@ static const SchemaQuery Query_for_list_of_collations = {
 "   AND a.attnum > 0 "\
 "   AND NOT a.attisdropped "\
 "   AND substring(attnum::pg_catalog.text,1,%d)='%s' "\
-"   AND (pg_catalog.quote_ident(relname)='%s' "\
+"   AND (relname='%s' "\
 "        OR '\"' || relname || '\"'='%s') "\
 "   AND pg_catalog.pg_table_is_visible(c.oid)"

 #define Query_for_list_of_attributes_with_schema \
-"SELECT pg_catalog.quote_ident(attname) "\
+"SELECT attname "\
 "  FROM pg_catalog.pg_attribute a, pg_catalog.pg_class c, pg_catalog.pg_namespace n "\
 " WHERE c.oid = a.attrelid "\
 "   AND n.oid = c.relnamespace "\
 "   AND a.attnum > 0 "\
 "   AND NOT a.attisdropped "\
-"   AND substring(pg_catalog.quote_ident(attname),1,%d)='%s' "\
-"   AND (pg_catalog.quote_ident(relname)='%s' "\
+"   AND substring(attname,1,%d)='%s' "\
+"   AND (relname='%s' "\
 "        OR '\"' || relname || '\"' ='%s') "\
-"   AND (pg_catalog.quote_ident(nspname)='%s' "\
+"   AND (nspname='%s' "\
 "        OR '\"' || nspname || '\"' ='%s') "

 #define Query_for_list_of_enum_values_quoted \
@@ -698,7 +699,7 @@ static const SchemaQuery Query_for_list_of_collations = {
 "  FROM pg_catalog.pg_enum e, pg_catalog.pg_type t "\
 " WHERE t.oid = e.enumtypid "\
 "   AND substring(pg_catalog.quote_literal(enumlabel),1,%d)='%s' "\
-"   AND (pg_catalog.quote_ident(typname)='%s' "\
+"   AND (typname='%s' "\
 "        OR '\"' || typname || '\"'='%s') "\
 "   AND pg_catalog.pg_type_is_visible(t.oid)"

@@ -707,7 +708,7 @@ static const SchemaQuery Query_for_list_of_collations = {
 "  FROM pg_catalog.pg_enum e, pg_catalog.pg_type t "\
 " WHERE t.oid = e.enumtypid "\
 "   AND substring(enumlabel,1,%d)='%s' "\
-"   AND (pg_catalog.quote_ident(typname)='%s' "\
+"   AND (typname='%s' "\
 "        OR '\"' || typname || '\"'='%s') "\
 "   AND pg_catalog.pg_type_is_visible(t.oid)"

@@ -717,9 +718,9 @@ static const SchemaQuery Query_for_list_of_collations = {
 " WHERE t.oid = e.enumtypid "\
 "   AND n.oid = t.typnamespace "\
 "   AND substring(pg_catalog.quote_literal(enumlabel),1,%d)='%s' "\
-"   AND (pg_catalog.quote_ident(typname)='%s' "\
+"   AND (typname='%s' "\
 "        OR '\"' || typname || '\"'='%s') "\
-"   AND (pg_catalog.quote_ident(nspname)='%s' "\
+"   AND (nspname='%s' "\
 "        OR '\"' || nspname || '\"' ='%s') "

 #define Query_for_list_of_enum_values_with_schema_unquoted \
@@ -728,24 +729,24 @@ static const SchemaQuery Query_for_list_of_collations = {
 " WHERE t.oid = e.enumtypid "\
 "   AND n.oid = t.typnamespace "\
 "   AND substring(enumlabel,1,%d)='%s' "\
-"   AND (pg_catalog.quote_ident(typname)='%s' "\
+"   AND (typname='%s' "\
 "        OR '\"' || typname || '\"'='%s') "\
-"   AND (pg_catalog.quote_ident(nspname)='%s' "\
+"   AND (nspname='%s' "\
 "        OR '\"' || nspname || '\"' ='%s') "

 #define Query_for_list_of_template_databases \
-"SELECT pg_catalog.quote_ident(d.datname) "\
+"SELECT d.datname "\
 "  FROM pg_catalog.pg_database d "\
-" WHERE substring(pg_catalog.quote_ident(d.datname),1,%d)='%s' "\
+" WHERE substring(d.datname,1,%d)='%s' "\
 "   AND (d.datistemplate OR pg_catalog.pg_has_role(d.datdba, 'USAGE'))"

 #define Query_for_list_of_databases \
-"SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database "\
-" WHERE substring(pg_catalog.quote_ident(datname),1,%d)='%s'"
+"SELECT datname FROM pg_catalog.pg_database "\
+" WHERE substring(datname,1,%d)='%s'"

 #define Query_for_list_of_tablespaces \
-"SELECT pg_catalog.quote_ident(spcname) FROM pg_catalog.pg_tablespace "\
-" WHERE substring(pg_catalog.quote_ident(spcname),1,%d)='%s'"
+"SELECT spcname FROM pg_catalog.pg_tablespace "\
+" WHERE substring(spcname,1,%d)='%s'"

 #define Query_for_list_of_encodings \
 " SELECT DISTINCT pg_catalog.pg_encoding_to_char(conforencoding) "\
@@ -753,14 +754,14 @@ static const SchemaQuery Query_for_list_of_collations = {
 "  WHERE substring(pg_catalog.pg_encoding_to_char(conforencoding),1,%d)=UPPER('%s')"

 #define Query_for_list_of_languages \
-"SELECT pg_catalog.quote_ident(lanname) "\
+"SELECT lanname "\
 "  FROM pg_catalog.pg_language "\
 " WHERE lanname != 'internal' "\
-"   AND substring(pg_catalog.quote_ident(lanname),1,%d)='%s'"
+"   AND substring(lanname,1,%d)='%s'"

 #define Query_for_list_of_schemas \
-"SELECT pg_catalog.quote_ident(nspname) FROM pg_catalog.pg_namespace "\
-" WHERE substring(pg_catalog.quote_ident(nspname),1,%d)='%s'"
+"SELECT nspname FROM pg_catalog.pg_namespace "\
+" WHERE substring(nspname,1,%d)='%s'"

 #define Query_for_list_of_alter_system_set_vars \
 "SELECT name FROM "\
@@ -789,14 +790,14 @@ static const SchemaQuery Query_for_list_of_collations = {
 " WHERE substring(name,1,%d)='%s'"

 #define Query_for_list_of_roles \
-" SELECT pg_catalog.quote_ident(rolname) "\
+" SELECT rolname "\
 "   FROM pg_catalog.pg_roles "\
-"  WHERE substring(pg_catalog.quote_ident(rolname),1,%d)='%s'"
+"  WHERE substring(rolname,1,%d)='%s'"

 #define Query_for_list_of_grant_roles \
-" SELECT pg_catalog.quote_ident(rolname) "\
+" SELECT rolname "\
 "   FROM pg_catalog.pg_roles "\
-"  WHERE substring(pg_catalog.quote_ident(rolname),1,%d)='%s'"\
+"  WHERE substring(rolname,1,%d)='%s'"\
 " UNION ALL SELECT 'PUBLIC'"\
 " UNION ALL SELECT 'CURRENT_ROLE'"\
 " UNION ALL SELECT 'CURRENT_USER'"\
@@ -804,11 +805,11 @@ static const SchemaQuery Query_for_list_of_collations = {

 /* the silly-looking length condition is just to eat up the current word */
 #define Query_for_index_of_table \
-"SELECT pg_catalog.quote_ident(c2.relname) "\
+"SELECT c2.relname "\
 "  FROM pg_catalog.pg_class c1, pg_catalog.pg_class c2, pg_catalog.pg_index i"\
 " WHERE c1.oid=i.indrelid and i.indexrelid=c2.oid"\
 "       and (%d = pg_catalog.length('%s'))"\
-"       and pg_catalog.quote_ident(c1.relname)='%s'"\
+"       and c1.relname='%s'"\
 "       and pg_catalog.pg_table_is_visible(c2.oid)"

 #define Query_for_unique_index_of_table \
@@ -817,124 +818,124 @@ Query_for_index_of_table \

 /* the silly-looking length condition is just to eat up the current word */
 #define Query_for_constraint_of_table \
-"SELECT pg_catalog.quote_ident(conname) "\
+"SELECT conname "\
 "  FROM pg_catalog.pg_class c1, pg_catalog.pg_constraint con "\
 " WHERE c1.oid=conrelid and (%d = pg_catalog.length('%s'))"\
-"       and pg_catalog.quote_ident(c1.relname)='%s'"\
+"       and c1.relname='%s'"\
 "       and pg_catalog.pg_table_is_visible(c1.oid)"

 /* the silly-looking length condition is just to eat up the current word */
 #define Query_for_constraint_of_table_not_validated \
-"SELECT pg_catalog.quote_ident(conname) "\
+"SELECT conname "\
 "  FROM pg_catalog.pg_class c1, pg_catalog.pg_constraint con "\
 " WHERE c1.oid=conrelid and (%d = pg_catalog.length('%s'))"\
-"       and pg_catalog.quote_ident(c1.relname)='%s'"\
+"       and c1.relname='%s'"\
 "       and pg_catalog.pg_table_is_visible(c1.oid)" \
 "       and not con.convalidated"

 #define Query_for_all_table_constraints \
-"SELECT pg_catalog.quote_ident(conname) "\
+"SELECT conname "\
 "  FROM pg_catalog.pg_constraint c "\
 " WHERE c.conrelid <> 0 "

 /* the silly-looking length condition is just to eat up the current word */
 #define Query_for_constraint_of_type \
-"SELECT pg_catalog.quote_ident(conname) "\
+"SELECT conname "\
 "  FROM pg_catalog.pg_type t, pg_catalog.pg_constraint con "\
 " WHERE t.oid=contypid and (%d = pg_catalog.length('%s'))"\
-"       and pg_catalog.quote_ident(t.typname)='%s'"\
+"       and t.typname='%s'"\
 "       and pg_catalog.pg_type_is_visible(t.oid)"

 /* the silly-looking length condition is just to eat up the current word */
 #define Query_for_list_of_tables_for_constraint \
-"SELECT pg_catalog.quote_ident(relname) "\
+"SELECT relname "\
 "  FROM pg_catalog.pg_class"\
 " WHERE (%d = pg_catalog.length('%s'))"\
 "   AND oid IN "\
 "       (SELECT conrelid FROM pg_catalog.pg_constraint "\
-"         WHERE pg_catalog.quote_ident(conname)='%s')"
+"         WHERE conname='%s')"

 /* the silly-looking length condition is just to eat up the current word */
 #define Query_for_rule_of_table \
-"SELECT pg_catalog.quote_ident(rulename) "\
+"SELECT rulename "\
 "  FROM pg_catalog.pg_class c1, pg_catalog.pg_rewrite "\
 " WHERE c1.oid=ev_class and (%d = pg_catalog.length('%s'))"\
-"       and pg_catalog.quote_ident(c1.relname)='%s'"\
+"       and c1.relname='%s'"\
 "       and pg_catalog.pg_table_is_visible(c1.oid)"

 /* the silly-looking length condition is just to eat up the current word */
 #define Query_for_list_of_tables_for_rule \
-"SELECT pg_catalog.quote_ident(relname) "\
+"SELECT relname "\
 "  FROM pg_catalog.pg_class"\
 " WHERE (%d = pg_catalog.length('%s'))"\
 "   AND oid IN "\
 "       (SELECT ev_class FROM pg_catalog.pg_rewrite "\
-"         WHERE pg_catalog.quote_ident(rulename)='%s')"
+"         WHERE rulename='%s')"

 /* the silly-looking length condition is just to eat up the current word */
 #define Query_for_trigger_of_table \
-"SELECT pg_catalog.quote_ident(tgname) "\
+"SELECT tgname "\
 "  FROM pg_catalog.pg_class c1, pg_catalog.pg_trigger "\
 " WHERE c1.oid=tgrelid and (%d = pg_catalog.length('%s'))"\
-"       and pg_catalog.quote_ident(c1.relname)='%s'"\
+"       and c1.relname='%s'"\
 "       and pg_catalog.pg_table_is_visible(c1.oid)"\
 "       and not tgisinternal"

 /* the silly-looking length condition is just to eat up the current word */
 #define Query_for_list_of_tables_for_trigger \
-"SELECT pg_catalog.quote_ident(relname) "\
+"SELECT relname "\
 "  FROM pg_catalog.pg_class"\
 " WHERE (%d = pg_catalog.length('%s'))"\
 "   AND oid IN "\
 "       (SELECT tgrelid FROM pg_catalog.pg_trigger "\
-"         WHERE pg_catalog.quote_ident(tgname)='%s')"
+"         WHERE tgname='%s')"

 #define Query_for_list_of_ts_configurations \
-"SELECT pg_catalog.quote_ident(cfgname) FROM pg_catalog.pg_ts_config "\
-" WHERE substring(pg_catalog.quote_ident(cfgname),1,%d)='%s'"
+"SELECT cfgname FROM pg_catalog.pg_ts_config "\
+" WHERE substring(cfgname,1,%d)='%s'"

 #define Query_for_list_of_ts_dictionaries \
-"SELECT pg_catalog.quote_ident(dictname) FROM pg_catalog.pg_ts_dict "\
-" WHERE substring(pg_catalog.quote_ident(dictname),1,%d)='%s'"
+"SELECT dictname FROM pg_catalog.pg_ts_dict "\
+" WHERE substring(dictname,1,%d)='%s'"

 #define Query_for_list_of_ts_parsers \
-"SELECT pg_catalog.quote_ident(prsname) FROM pg_catalog.pg_ts_parser "\
-" WHERE substring(pg_catalog.quote_ident(prsname),1,%d)='%s'"
+"SELECT prsname FROM pg_catalog.pg_ts_parser "\
+" WHERE substring(prsname,1,%d)='%s'"

 #define Query_for_list_of_ts_templates \
-"SELECT pg_catalog.quote_ident(tmplname) FROM pg_catalog.pg_ts_template "\
-" WHERE substring(pg_catalog.quote_ident(tmplname),1,%d)='%s'"
+"SELECT tmplname FROM pg_catalog.pg_ts_template "\
+" WHERE substring(tmplname,1,%d)='%s'"

 #define Query_for_list_of_fdws \
-" SELECT pg_catalog.quote_ident(fdwname) "\
+" SELECT fdwname "\
 "   FROM pg_catalog.pg_foreign_data_wrapper "\
-"  WHERE substring(pg_catalog.quote_ident(fdwname),1,%d)='%s'"
+"  WHERE substring(fdwname,1,%d)='%s'"

 #define Query_for_list_of_servers \
-" SELECT pg_catalog.quote_ident(srvname) "\
+" SELECT srvname "\
 "   FROM pg_catalog.pg_foreign_server "\
-"  WHERE substring(pg_catalog.quote_ident(srvname),1,%d)='%s'"
+"  WHERE substring(srvname,1,%d)='%s'"

 #define Query_for_list_of_user_mappings \
-" SELECT pg_catalog.quote_ident(usename) "\
+" SELECT usename "\
 "   FROM pg_catalog.pg_user_mappings "\
-"  WHERE substring(pg_catalog.quote_ident(usename),1,%d)='%s'"
+"  WHERE substring(usename,1,%d)='%s'"

 #define Query_for_list_of_access_methods \
-" SELECT pg_catalog.quote_ident(amname) "\
+" SELECT amname "\
 "   FROM pg_catalog.pg_am "\
-"  WHERE substring(pg_catalog.quote_ident(amname),1,%d)='%s'"
+"  WHERE substring(amname,1,%d)='%s'"

 #define Query_for_list_of_index_access_methods \
-" SELECT pg_catalog.quote_ident(amname) "\
+" SELECT amname "\
 "   FROM pg_catalog.pg_am "\
-"  WHERE substring(pg_catalog.quote_ident(amname),1,%d)='%s' AND "\
+"  WHERE substring(amname,1,%d)='%s' AND "\
 "   amtype=" CppAsString2(AMTYPE_INDEX)

 #define Query_for_list_of_table_access_methods \
-" SELECT pg_catalog.quote_ident(amname) "\
+" SELECT amname "\
 "   FROM pg_catalog.pg_am "\
-"  WHERE substring(pg_catalog.quote_ident(amname),1,%d)='%s' AND "\
+"  WHERE substring(amname,1,%d)='%s' AND "\
 "   amtype=" CppAsString2(AMTYPE_TABLE)

 /* the silly-looking length condition is just to eat up the current word */
@@ -942,7 +943,7 @@ Query_for_index_of_table \
 "SELECT pg_catalog.oidvectortypes(proargtypes)||')' "\
 "  FROM pg_catalog.pg_proc "\
 " WHERE (%d = pg_catalog.length('%s'))"\
-"   AND (pg_catalog.quote_ident(proname)='%s'"\
+"   AND (proname='%s'"\
 "        OR '\"' || proname || '\"'='%s') "\
 "   AND (pg_catalog.pg_function_is_visible(pg_proc.oid))"

@@ -952,68 +953,68 @@ Query_for_index_of_table \
 "  FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n "\
 " WHERE (%d = pg_catalog.length('%s'))"\
 "   AND n.oid = p.pronamespace "\
-"   AND (pg_catalog.quote_ident(proname)='%s' "\
+"   AND (proname='%s' "\
 "        OR '\"' || proname || '\"' ='%s') "\
-"   AND (pg_catalog.quote_ident(nspname)='%s' "\
+"   AND (nspname='%s' "\
 "        OR '\"' || nspname || '\"' ='%s') "

 #define Query_for_list_of_extensions \
-" SELECT pg_catalog.quote_ident(extname) "\
+" SELECT extname "\
 "   FROM pg_catalog.pg_extension "\
-"  WHERE substring(pg_catalog.quote_ident(extname),1,%d)='%s'"
+"  WHERE substring(extname,1,%d)='%s'"

 #define Query_for_list_of_available_extensions \
-" SELECT pg_catalog.quote_ident(name) "\
+" SELECT name "\
 "   FROM pg_catalog.pg_available_extensions "\
-"  WHERE substring(pg_catalog.quote_ident(name),1,%d)='%s' AND installed_version IS NULL"
+"  WHERE substring(name,1,%d)='%s' AND installed_version IS NULL"

 /* the silly-looking length condition is just to eat up the current word */
 #define Query_for_list_of_available_extension_versions \
-" SELECT pg_catalog.quote_ident(version) "\
+" SELECT version "\
 "   FROM pg_catalog.pg_available_extension_versions "\
 "  WHERE (%d = pg_catalog.length('%s'))"\
-"    AND pg_catalog.quote_ident(name)='%s'"
+"    AND name='%s'"

 /* the silly-looking length condition is just to eat up the current word */
 #define Query_for_list_of_available_extension_versions_with_TO \
-" SELECT 'TO ' || pg_catalog.quote_ident(version) "\
+" SELECT 'TO ' || version "\
 "   FROM pg_catalog.pg_available_extension_versions "\
 "  WHERE (%d = pg_catalog.length('%s'))"\
-"    AND pg_catalog.quote_ident(name)='%s'"
+"    AND name='%s'"

 #define Query_for_list_of_prepared_statements \
-" SELECT pg_catalog.quote_ident(name) "\
+" SELECT name "\
 "   FROM pg_catalog.pg_prepared_statements "\
-"  WHERE substring(pg_catalog.quote_ident(name),1,%d)='%s'"
+"  WHERE substring(name,1,%d)='%s'"

 #define Query_for_list_of_event_triggers \
-" SELECT pg_catalog.quote_ident(evtname) "\
+" SELECT evtname "\
 "   FROM pg_catalog.pg_event_trigger "\
-"  WHERE substring(pg_catalog.quote_ident(evtname),1,%d)='%s'"
+"  WHERE substring(evtname,1,%d)='%s'"

 #define Query_for_list_of_tablesample_methods \
-" SELECT pg_catalog.quote_ident(proname) "\
+" SELECT proname "\
 "   FROM pg_catalog.pg_proc "\
 "  WHERE prorettype = 'pg_catalog.tsm_handler'::pg_catalog.regtype AND "\
 "        proargtypes[0] = 'pg_catalog.internal'::pg_catalog.regtype AND "\
-"        substring(pg_catalog.quote_ident(proname),1,%d)='%s'"
+"        substring(proname,1,%d)='%s'"

 #define Query_for_list_of_policies \
-" SELECT pg_catalog.quote_ident(polname) "\
+" SELECT polname "\
 "   FROM pg_catalog.pg_policy "\
-"  WHERE substring(pg_catalog.quote_ident(polname),1,%d)='%s'"
+"  WHERE substring(polname,1,%d)='%s'"

 #define Query_for_list_of_tables_for_policy \
-"SELECT pg_catalog.quote_ident(relname) "\
+"SELECT relname "\
 "  FROM pg_catalog.pg_class"\
 " WHERE (%d = pg_catalog.length('%s'))"\
 "   AND oid IN "\
 "       (SELECT polrelid FROM pg_catalog.pg_policy "\
-"         WHERE pg_catalog.quote_ident(polname)='%s')"
+"         WHERE polname='%s')"

 #define Query_for_enum \
 " SELECT name FROM ( "\
-"   SELECT pg_catalog.quote_ident(pg_catalog.unnest(enumvals)) AS name "\
+"   SELECT pg_catalog.unnest(enumvals) AS name "\
 "     FROM pg_catalog.pg_settings "\
 "    WHERE pg_catalog.lower(name)=pg_catalog.lower('%s') "\
 "    UNION ALL " \
@@ -1022,18 +1023,18 @@ Query_for_index_of_table \

 /* the silly-looking length condition is just to eat up the current word */
 #define Query_for_partition_of_table \
-"SELECT pg_catalog.quote_ident(c2.relname) "\
+"SELECT c2.relname "\
 "  FROM pg_catalog.pg_class c1, pg_catalog.pg_class c2, pg_catalog.pg_inherits i"\
 " WHERE c1.oid=i.inhparent and i.inhrelid=c2.oid"\
 "       and (%d = pg_catalog.length('%s'))"\
-"       and pg_catalog.quote_ident(c1.relname)='%s'"\
+"       and c1.relname='%s'"\
 "       and pg_catalog.pg_table_is_visible(c2.oid)"\
 "       and c2.relispartition = 'true'"

 #define Query_for_list_of_cursors \
-" SELECT pg_catalog.quote_ident(name) "\
+" SELECT name "\
 "   FROM pg_catalog.pg_cursors "\
-"  WHERE substring(pg_catalog.quote_ident(name),1,%d)='%s'"
+"  WHERE substring(name,1,%d)='%s'"

 /*
  * These object types were introduced later than our support cutoff of
@@ -1043,18 +1044,18 @@ Query_for_index_of_table \

 static const VersionedQuery Query_for_list_of_publications[] = {
     {100000,
-        " SELECT pg_catalog.quote_ident(pubname) "
+        " SELECT pubname "
         "   FROM pg_catalog.pg_publication "
-        "  WHERE substring(pg_catalog.quote_ident(pubname),1,%d)='%s'"
+        "  WHERE substring(pubname,1,%d)='%s'"
     },
     {0, NULL}
 };

 static const VersionedQuery Query_for_list_of_subscriptions[] = {
     {100000,
-        " SELECT pg_catalog.quote_ident(s.subname) "
+        " SELECT s.subname "
         "   FROM pg_catalog.pg_subscription s, pg_catalog.pg_database d "
-        "  WHERE substring(pg_catalog.quote_ident(s.subname),1,%d)='%s' "
+        "  WHERE substring(s.subname,1,%d)='%s' "
         "    AND d.datname = pg_catalog.current_database() "
         "    AND s.subdbid = d.oid"
     },
@@ -1092,7 +1093,7 @@ static const pgsql_thing_t words_after_create[] = {
      * to be used only by pg_dump.
      */
     {"CONFIGURATION", Query_for_list_of_ts_configurations, NULL, NULL, THING_NO_SHOW},
-    {"CONVERSION", "SELECT pg_catalog.quote_ident(conname) FROM pg_catalog.pg_conversion WHERE
substring(pg_catalog.quote_ident(conname),1,%d)='%s'"},
+    {"CONVERSION", "SELECT conname FROM pg_catalog.pg_conversion WHERE substring(conname,1,%d)='%s'"},
     {"DATABASE", Query_for_list_of_databases},
     {"DEFAULT PRIVILEGES", NULL, NULL, NULL, THING_NO_CREATE | THING_NO_DROP},
     {"DICTIONARY", Query_for_list_of_ts_dictionaries, NULL, NULL, THING_NO_SHOW},
@@ -1117,7 +1118,7 @@ static const pgsql_thing_t words_after_create[] = {
     {"PUBLICATION", NULL, Query_for_list_of_publications},
     {"ROLE", Query_for_list_of_roles},
     {"ROUTINE", NULL, NULL, &Query_for_list_of_routines, THING_NO_CREATE},
-    {"RULE", "SELECT pg_catalog.quote_ident(rulename) FROM pg_catalog.pg_rules WHERE
substring(pg_catalog.quote_ident(rulename),1,%d)='%s'"},
+    {"RULE", "SELECT rulename FROM pg_catalog.pg_rules WHERE substring(rulename,1,%d)='%s'"},
     {"SCHEMA", Query_for_list_of_schemas},
     {"SEQUENCE", NULL, NULL, &Query_for_list_of_sequences},
     {"SERVER", Query_for_list_of_servers},
@@ -1133,7 +1134,7 @@ static const pgsql_thing_t words_after_create[] = {
                                                                          * TABLE ... */
     {"TEXT SEARCH", NULL, NULL, NULL},
     {"TRANSFORM", NULL, NULL, NULL, THING_NO_ALTER},
-    {"TRIGGER", "SELECT pg_catalog.quote_ident(tgname) FROM pg_catalog.pg_trigger WHERE
substring(pg_catalog.quote_ident(tgname),1,%d)='%s'AND NOT tgisinternal"}, 
+    {"TRIGGER", "SELECT tgname FROM pg_catalog.pg_trigger WHERE substring(tgname,1,%d)='%s' AND NOT tgisinternal"},
     {"TYPE", NULL, NULL, &Query_for_list_of_datatypes},
     {"UNIQUE", NULL, NULL, NULL, THING_NO_DROP | THING_NO_ALTER},    /* for CREATE UNIQUE
                                                                      * INDEX ... */
@@ -1195,11 +1196,13 @@ static char *create_command_generator(const char *text, int state);
 static char *drop_command_generator(const char *text, int state);
 static char *alter_command_generator(const char *text, int state);
 static char *complete_from_query(const char *text, int state);
+static char *complete_from_query_verbatim(const char *text, int state);
 static char *complete_from_versioned_query(const char *text, int state);
 static char *complete_from_schema_query(const char *text, int state);
 static char *complete_from_versioned_schema_query(const char *text, int state);
 static char *_complete_from_query(const char *simple_query,
                                   const SchemaQuery *schema_query,
+                                  bool verbatim,
                                   const char *text, int state);
 static char *complete_from_list(const char *text, int state);
 static char *complete_from_const(const char *text, int state);
@@ -1212,6 +1215,12 @@ static char *complete_from_files(const char *text, int state);

 static char *pg_strdup_keyword_case(const char *s, const char *ref);
 static char *escape_string(const char *text);
+static void parse_identifier(const char *ident,
+                             char **schemaname, char **objectname,
+                             bool *schemaquoted, bool *objectquoted);
+static char *requote_identifier(const char *schemaname, const char *objectname,
+                                bool quote_schema, bool quote_object);
+static bool identifier_needs_quotes(const char *ident);
 static PGresult *exec_query(const char *query);

 static char **get_previous_words(int point, char **buffer, int *nwords);
@@ -1651,7 +1660,7 @@ psql_completion(const char *text, int start, int end)
     /* ALTER TABLE */
     else if (Matches("ALTER", "TABLE"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables,
-                                   "UNION SELECT 'ALL IN TABLESPACE'");
+                                   "UNION SELECT 'ALL IN TABLESPACE', NULL, true");

     /* ALTER something */
     else if (Matches("ALTER"))
@@ -1812,7 +1821,7 @@ psql_completion(const char *text, int start, int end)
     /* ALTER INDEX */
     else if (Matches("ALTER", "INDEX"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes,
-                                   "UNION SELECT 'ALL IN TABLESPACE'");
+                                   "UNION SELECT 'ALL IN TABLESPACE', NULL, true");
     /* ALTER INDEX <name> */
     else if (Matches("ALTER", "INDEX", MatchAny))
         COMPLETE_WITH("ALTER COLUMN", "OWNER TO", "RENAME TO", "SET",
@@ -1879,7 +1888,7 @@ psql_completion(const char *text, int start, int end)
     /* ALTER MATERIALIZED VIEW */
     else if (Matches("ALTER", "MATERIALIZED", "VIEW"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews,
-                                   "UNION SELECT 'ALL IN TABLESPACE'");
+                                   "UNION SELECT 'ALL IN TABLESPACE', NULL, true");

     /* ALTER USER,ROLE <name> */
     else if (Matches("ALTER", "USER|ROLE", MatchAny) &&
@@ -2164,13 +2173,13 @@ psql_completion(const char *text, int start, int end)
     }
     /* ALTER TABLE xxx INHERIT */
     else if (Matches("ALTER", "TABLE", MatchAny, "INHERIT"))
-        COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, "");
+        COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
     /* ALTER TABLE xxx NO */
     else if (Matches("ALTER", "TABLE", MatchAny, "NO"))
         COMPLETE_WITH("FORCE ROW LEVEL SECURITY", "INHERIT");
     /* ALTER TABLE xxx NO INHERIT */
     else if (Matches("ALTER", "TABLE", MatchAny, "NO", "INHERIT"))
-        COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, "");
+        COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
     /* ALTER TABLE xxx DISABLE */
     else if (Matches("ALTER", "TABLE", MatchAny, "DISABLE"))
         COMPLETE_WITH("ROW LEVEL SECURITY", "RULE", "TRIGGER");
@@ -2298,7 +2307,7 @@ psql_completion(const char *text, int start, int end)
      * tables.
      */
     else if (Matches("ALTER", "TABLE", MatchAny, "ATTACH", "PARTITION"))
-        COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, "");
+        COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
     /* Limited completion support for partition bound specification */
     else if (TailMatches("ATTACH", "PARTITION", MatchAny))
         COMPLETE_WITH("FOR VALUES", "DEFAULT");
@@ -2386,7 +2395,7 @@ psql_completion(const char *text, int start, int end)
  */
     else if (Matches("ANALYZE"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_analyzables,
-                                   " UNION SELECT 'VERBOSE'");
+                                   " UNION SELECT 'VERBOSE', NULL, true");
     else if (HeadMatches("ANALYZE", "(*") &&
              !HeadMatches("ANALYZE", "(*)"))
     {
@@ -2434,7 +2443,8 @@ psql_completion(const char *text, int start, int end)
                             " UNION SELECT 'ALL'");
 /* CLUSTER */
     else if (Matches("CLUSTER"))
-        COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_clusterables, "UNION SELECT 'VERBOSE'");
+        COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_clusterables,
+                                   "UNION SELECT 'VERBOSE', NULL, true");
     else if (Matches("CLUSTER", "VERBOSE") ||
              Matches("CLUSTER", "(*)"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_clusterables, NULL);
@@ -2558,7 +2568,7 @@ psql_completion(const char *text, int start, int end)
      */
     else if (Matches("COPY|\\copy"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables,
-                                   " UNION ALL SELECT '('");
+                                   " UNION ALL SELECT '(', NULL, true");
     /* Complete COPY ( with legal query commands */
     else if (Matches("COPY|\\copy", "("))
         COMPLETE_WITH("SELECT", "TABLE", "VALUES", "INSERT INTO", "UPDATE", "DELETE FROM", "WITH");
@@ -2686,8 +2696,8 @@ psql_completion(const char *text, int start, int end)
      */
     else if (TailMatches("CREATE|UNIQUE", "INDEX"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes,
-                                   " UNION SELECT 'ON'"
-                                   " UNION SELECT 'CONCURRENTLY'");
+                                   " UNION SELECT 'ON', NULL, true"
+                                   " UNION SELECT 'CONCURRENTLY', NULL, true");

     /*
      * Complete ... INDEX|CONCURRENTLY [<name>] ON with a list of relations
@@ -2703,7 +2713,7 @@ psql_completion(const char *text, int start, int end)
      */
     else if (TailMatches("CREATE|UNIQUE", "INDEX", "CONCURRENTLY"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes,
-                                   " UNION SELECT 'ON'");
+                                   " UNION SELECT 'ON', NULL, true");
     /* Complete CREATE|UNIQUE INDEX [CONCURRENTLY] <sth> with "ON" */
     else if (TailMatches("CREATE|UNIQUE", "INDEX", MatchAny) ||
              TailMatches("CREATE|UNIQUE", "INDEX", "CONCURRENTLY", MatchAny))
@@ -2912,7 +2922,7 @@ psql_completion(const char *text, int start, int end)
         COMPLETE_WITH("RANGE (", "LIST (", "HASH (");
     /* If we have xxx PARTITION OF, provide a list of partitioned tables */
     else if (TailMatches("PARTITION", "OF"))
-        COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_partitioned_tables, "");
+        COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_partitioned_tables, NULL);
     /* Limited completion support for partition bound specification */
     else if (TailMatches("PARTITION", "OF", MatchAny))
         COMPLETE_WITH("FOR VALUES", "DEFAULT");
@@ -3359,7 +3369,7 @@ psql_completion(const char *text, int start, int end)
     /* DROP INDEX */
     else if (Matches("DROP", "INDEX"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes,
-                                   " UNION SELECT 'CONCURRENTLY'");
+                                   " UNION SELECT 'CONCURRENTLY', NULL, true");
     else if (Matches("DROP", "INDEX", "CONCURRENTLY"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL);
     else if (Matches("DROP", "INDEX", MatchAny))
@@ -3618,25 +3628,25 @@ psql_completion(const char *text, int start, int end)
             COMPLETE_WITH("TABLES", "SEQUENCES", "FUNCTIONS", "PROCEDURES", "ROUTINES", "TYPES", "SCHEMAS");
         else
             COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_grantables,
-                                       " UNION SELECT 'ALL FUNCTIONS IN SCHEMA'"
-                                       " UNION SELECT 'ALL PROCEDURES IN SCHEMA'"
-                                       " UNION SELECT 'ALL ROUTINES IN SCHEMA'"
-                                       " UNION SELECT 'ALL SEQUENCES IN SCHEMA'"
-                                       " UNION SELECT 'ALL TABLES IN SCHEMA'"
-                                       " UNION SELECT 'DATABASE'"
-                                       " UNION SELECT 'DOMAIN'"
-                                       " UNION SELECT 'FOREIGN DATA WRAPPER'"
-                                       " UNION SELECT 'FOREIGN SERVER'"
-                                       " UNION SELECT 'FUNCTION'"
-                                       " UNION SELECT 'LANGUAGE'"
-                                       " UNION SELECT 'LARGE OBJECT'"
-                                       " UNION SELECT 'PROCEDURE'"
-                                       " UNION SELECT 'ROUTINE'"
-                                       " UNION SELECT 'SCHEMA'"
-                                       " UNION SELECT 'SEQUENCE'"
-                                       " UNION SELECT 'TABLE'"
-                                       " UNION SELECT 'TABLESPACE'"
-                                       " UNION SELECT 'TYPE'");
+                                       " UNION SELECT 'ALL FUNCTIONS IN SCHEMA', NULL, true"
+                                       " UNION SELECT 'ALL PROCEDURES IN SCHEMA', NULL, true"
+                                       " UNION SELECT 'ALL ROUTINES IN SCHEMA', NULL, true"
+                                       " UNION SELECT 'ALL SEQUENCES IN SCHEMA', NULL, true"
+                                       " UNION SELECT 'ALL TABLES IN SCHEMA', NULL, true"
+                                       " UNION SELECT 'DATABASE', NULL, true"
+                                       " UNION SELECT 'DOMAIN', NULL, true"
+                                       " UNION SELECT 'FOREIGN DATA WRAPPER', NULL, true"
+                                       " UNION SELECT 'FOREIGN SERVER', NULL, true"
+                                       " UNION SELECT 'FUNCTION', NULL, true"
+                                       " UNION SELECT 'LANGUAGE', NULL, true"
+                                       " UNION SELECT 'LARGE OBJECT', NULL, true"
+                                       " UNION SELECT 'PROCEDURE', NULL, true"
+                                       " UNION SELECT 'ROUTINE', NULL, true"
+                                       " UNION SELECT 'SCHEMA', NULL, true"
+                                       " UNION SELECT 'SEQUENCE', NULL, true"
+                                       " UNION SELECT 'TABLE', NULL, true"
+                                       " UNION SELECT 'TABLESPACE', NULL, true"
+                                       " UNION SELECT 'TYPE', NULL, true");
     }
     else if (TailMatches("GRANT|REVOKE", MatchAny, "ON", "ALL"))
         COMPLETE_WITH("FUNCTIONS IN SCHEMA",
@@ -3789,11 +3799,11 @@ psql_completion(const char *text, int start, int end)
     /* Complete LOCK [TABLE] [ONLY] with a list of tables */
     else if (Matches("LOCK"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables,
-                                   " UNION SELECT 'TABLE'"
-                                   " UNION SELECT 'ONLY'");
+                                   " UNION SELECT 'TABLE', NULL, true"
+                                   " UNION SELECT 'ONLY', NULL, true");
     else if (Matches("LOCK", "TABLE"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables,
-                                   " UNION SELECT 'ONLY'");
+                                   " UNION SELECT 'ONLY', NULL, true");
     else if (Matches("LOCK", "TABLE", "ONLY") || Matches("LOCK", "ONLY"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
     /* For the following, handle the case of a single table only for now */
@@ -3831,7 +3841,7 @@ psql_completion(const char *text, int start, int end)

 /* NOTIFY --- can be inside EXPLAIN, RULE, etc */
     else if (TailMatches("NOTIFY"))
-        COMPLETE_WITH_QUERY("SELECT pg_catalog.quote_ident(channel) FROM pg_catalog.pg_listening_channels() AS channel
WHEREsubstring(pg_catalog.quote_ident(channel),1,%d)='%s'"); 
+        COMPLETE_WITH_QUERY("SELECT channel FROM pg_catalog.pg_listening_channels() AS channel WHERE
substring(channel,1,%d)='%s'");

 /* OPTIONS */
     else if (TailMatches("OPTIONS"))
@@ -3875,7 +3885,7 @@ psql_completion(const char *text, int start, int end)
         COMPLETE_WITH("VIEW");
     else if (Matches("REFRESH", "MATERIALIZED", "VIEW"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews,
-                                   " UNION SELECT 'CONCURRENTLY'");
+                                   " UNION SELECT 'CONCURRENTLY', NULL, true");
     else if (Matches("REFRESH", "MATERIALIZED", "VIEW", "CONCURRENTLY"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL);
     else if (Matches("REFRESH", "MATERIALIZED", "VIEW", MatchAny))
@@ -3898,15 +3908,15 @@ psql_completion(const char *text, int start, int end)
     else if (Matches("REINDEX", "TABLE") ||
              Matches("REINDEX", "(*)", "TABLE"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexables,
-                                   " UNION SELECT 'CONCURRENTLY'");
+                                   " UNION SELECT 'CONCURRENTLY', NULL, true");
     else if (Matches("REINDEX", "INDEX") ||
              Matches("REINDEX", "(*)", "INDEX"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes,
-                                   " UNION SELECT 'CONCURRENTLY'");
+                                   " UNION SELECT 'CONCURRENTLY', NULL, true");
     else if (Matches("REINDEX", "SCHEMA") ||
              Matches("REINDEX", "(*)", "SCHEMA"))
         COMPLETE_WITH_QUERY(Query_for_list_of_schemas
-                            " UNION SELECT 'CONCURRENTLY'");
+                            " UNION SELECT 'CONCURRENTLY', NULL, true");
     else if (Matches("REINDEX", "SYSTEM|DATABASE") ||
              Matches("REINDEX", "(*)", "SYSTEM|DATABASE"))
         COMPLETE_WITH_QUERY(Query_for_list_of_databases
@@ -3997,7 +4007,8 @@ psql_completion(const char *text, int start, int end)
         COMPLETE_WITH("ONLY", "WRITE");
     /* SET CONSTRAINTS */
     else if (Matches("SET", "CONSTRAINTS"))
-        COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_constraints_with_schema, "UNION SELECT 'ALL'");
+        COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_constraints_with_schema,
+                                   "UNION SELECT 'ALL', NULL, true");
     /* Complete SET CONSTRAINTS <foo> with DEFERRED|IMMEDIATE */
     else if (Matches("SET", "CONSTRAINTS", MatchAny))
         COMPLETE_WITH("DEFERRED", "IMMEDIATE");
@@ -4091,11 +4102,11 @@ psql_completion(const char *text, int start, int end)
 /* TRUNCATE */
     else if (Matches("TRUNCATE"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_truncatables,
-                                   " UNION SELECT 'TABLE'"
-                                   " UNION SELECT 'ONLY'");
+                                   " UNION SELECT 'TABLE', NULL, true"
+                                   " UNION SELECT 'ONLY', NULL, true");
     else if (Matches("TRUNCATE", "TABLE"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_truncatables,
-                                   " UNION SELECT 'ONLY'");
+                                   " UNION SELECT 'ONLY', NULL, true");
     else if (HeadMatches("TRUNCATE") && TailMatches("ONLY"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_truncatables, NULL);
     else if (Matches("TRUNCATE", MatchAny) ||
@@ -4107,7 +4118,7 @@ psql_completion(const char *text, int start, int end)

 /* UNLISTEN */
     else if (Matches("UNLISTEN"))
-        COMPLETE_WITH_QUERY("SELECT pg_catalog.quote_ident(channel) FROM pg_catalog.pg_listening_channels() AS channel
WHEREsubstring(pg_catalog.quote_ident(channel),1,%d)='%s' UNION SELECT '*'"); 
+        COMPLETE_WITH_QUERY("SELECT channel FROM pg_catalog.pg_listening_channels() AS channel WHERE
substring(channel,1,%d)='%s'UNION SELECT '*'"); 

 /* UPDATE --- can be inside EXPLAIN, RULE, etc */
     /* If prev. word is UPDATE suggest a list of tables */
@@ -4145,25 +4156,25 @@ psql_completion(const char *text, int start, int end)
  */
     else if (Matches("VACUUM"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_vacuumables,
-                                   " UNION SELECT 'FULL'"
-                                   " UNION SELECT 'FREEZE'"
-                                   " UNION SELECT 'ANALYZE'"
-                                   " UNION SELECT 'VERBOSE'");
+                                   " UNION SELECT 'FULL', NULL, true"
+                                   " UNION SELECT 'FREEZE', NULL, true"
+                                   " UNION SELECT 'ANALYZE', NULL, true"
+                                   " UNION SELECT 'VERBOSE', NULL, true");
     else if (Matches("VACUUM", "FULL"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_vacuumables,
-                                   " UNION SELECT 'FREEZE'"
-                                   " UNION SELECT 'ANALYZE'"
-                                   " UNION SELECT 'VERBOSE'");
+                                   " UNION SELECT 'FREEZE', NULL, true"
+                                   " UNION SELECT 'ANALYZE', NULL, true"
+                                   " UNION SELECT 'VERBOSE', NULL, true");
     else if (Matches("VACUUM", "FREEZE") ||
              Matches("VACUUM", "FULL", "FREEZE"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_vacuumables,
-                                   " UNION SELECT 'VERBOSE'"
-                                   " UNION SELECT 'ANALYZE'");
+                                   " UNION SELECT 'VERBOSE', NULL, true"
+                                   " UNION SELECT 'ANALYZE', NULL, true");
     else if (Matches("VACUUM", "VERBOSE") ||
              Matches("VACUUM", "FULL|FREEZE", "VERBOSE") ||
              Matches("VACUUM", "FULL", "FREEZE", "VERBOSE"))
         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_vacuumables,
-                                   " UNION SELECT 'ANALYZE'");
+                                   " UNION SELECT 'ANALYZE', NULL, true");
     else if (HeadMatches("VACUUM", "(*") &&
              !HeadMatches("VACUUM", "(*)"))
     {
@@ -4551,7 +4562,14 @@ static char *
 complete_from_query(const char *text, int state)
 {
     /* query is assumed to work for any server version */
-    return _complete_from_query(completion_charp, NULL, text, state);
+    return _complete_from_query(completion_charp, NULL, false, text, state);
+}
+
+static char *
+complete_from_query_verbatim(const char *text, int state)
+{
+    /* query is assumed to work for any server version */
+    return _complete_from_query(completion_charp, NULL, true, text, state);
 }

 static char *
@@ -4566,7 +4584,7 @@ complete_from_versioned_query(const char *text, int state)
     if (vquery->query == NULL)
         return NULL;

-    return _complete_from_query(vquery->query, NULL, text, state);
+    return _complete_from_query(vquery->query, NULL, false, text, state);
 }

 static char *
@@ -4574,7 +4592,7 @@ complete_from_schema_query(const char *text, int state)
 {
     /* query is assumed to work for any server version */
     return _complete_from_query(completion_charp, completion_squery,
-                                text, state);
+                                false, text, state);
 }

 static char *
@@ -4600,7 +4618,7 @@ complete_from_versioned_schema_query(const char *text, int state)
     }

     return _complete_from_query(vquery ? vquery->query : NULL,
-                                squery, text, state);
+                                squery, false, text, state);
 }


@@ -4618,28 +4636,40 @@ complete_from_versioned_schema_query(const char *text, int state)
  * completion_info_charp2.
  *
  * 2. A schema query used for completion of both schema and relation names.
- * These are more complex and must contain in the following order:
- * %d %s %d %s %d %s %s %d %s
- * where %d is the string length of the text and %s the text itself.
+ * This is represented by a SchemaQuery object; see that typedef for details.
+ *
+ * In either case, the query can return up to three columns: an object name
+ * column, a schema name column, and a boolean column which if TRUE indicates
+ * that the object-name column should be returned verbatim.  If the boolean
+ * column is false or omitted, we will combine and appropriately quote the
+ * schema and object names.
  *
  * If both simple_query and schema_query are non-NULL, then we construct
  * a schema query and append the (uninterpreted) string simple_query to it.
+ * The simple query must return all three columns in this case.
  *
  * It is assumed that strings should be escaped to become SQL literals
  * (that is, what is in the query is actually ... '%s' ...)
  *
  * See top of file for examples of both kinds of query.
  *
+ * If "verbatim" is true, then we use the given text as-is to match the
+ * query results; otherwise we parse it as a possibly-qualified identifier,
+ * and reconstruct suitable quoting afterward.  This also changes the
+ * default assumption about whether the results are verbatim.
+ *
  * "text" and "state" are supplied by readline.
  */
 static char *
 _complete_from_query(const char *simple_query,
                      const SchemaQuery *schema_query,
+                     bool verbatim,
                      const char *text, int state)
 {
-    static int    list_index,
-                byte_length;
+    static int    list_index;
     static PGresult *result = NULL;
+    static bool schemaquoted;
+    static bool objectquoted;

     /*
      * If this is the first time for this completion, we fetch a list of our
@@ -4648,31 +4678,56 @@ _complete_from_query(const char *simple_query,
     if (state == 0)
     {
         PQExpBufferData query_buffer;
-        char       *e_text;
+        char       *schemaname;
+        char       *objectname;
+        int            schema_length = 0;
+        int            object_length = 0;
+        char       *e_schemaname;
+        char       *e_objectname;
         char       *e_info_charp;
         char       *e_info_charp2;
-        const char *pstr = text;
-        int            char_length = 0;

+        /* Reset static state, ensuring no memory leaks */
         list_index = 0;
-        byte_length = strlen(text);
+        PQclear(result);
+        result = NULL;
+
+        if (verbatim)
+        {
+            objectname = pg_strdup(text);
+            schemaname = NULL;
+        }
+        else
+        {
+            /* Parse text, splitting into schema and object name if needed */
+            parse_identifier(text,
+                             &schemaname, &objectname,
+                             &schemaquoted, &objectquoted);
+        }

         /*
-         * Count length as number of characters (not bytes), for passing to
+         * Count lengths as number of characters (not bytes), for passing to
          * substring
          */
-        while (*pstr)
+        if (schemaname)
         {
-            char_length++;
-            pstr += PQmblenBounded(pstr, pset.encoding);
+            for (const char *p = schemaname;
+                 *p;
+                 p += PQmblenBounded(p, pset.encoding))
+                schema_length++;
         }
-
-        /* Free any prior result */
-        PQclear(result);
-        result = NULL;
+        for (const char *p = objectname;
+             *p;
+             p += PQmblenBounded(p, pset.encoding))
+            object_length++;

         /* Set up suitably-escaped copies of textual inputs */
-        e_text = escape_string(text);
+        if (schemaname)
+            e_schemaname = escape_string(schemaname);
+        else
+            e_schemaname = NULL;
+
+        e_objectname = escape_string(objectname);

         if (completion_info_charp)
             e_info_charp = escape_string(completion_info_charp);
@@ -4694,8 +4749,14 @@ _complete_from_query(const char *simple_query,
             if (qualresult == NULL)
                 qualresult = schema_query->result;

+            /*
+             * We issue different queries depending on whether the input is
+             * qualified or not.
+             */
+            if (schemaname == NULL)
+            {
             /* Get unqualified names matching the input-so-far */
-            appendPQExpBuffer(&query_buffer, "SELECT %s FROM %s WHERE ",
+            appendPQExpBuffer(&query_buffer, "SELECT %s, NULL::pg_catalog.text, false FROM %s WHERE ",
                               schema_query->result,
                               schema_query->catname);
             if (schema_query->selcondition)
@@ -4703,7 +4764,7 @@ _complete_from_query(const char *simple_query,
                                   schema_query->selcondition);
             appendPQExpBuffer(&query_buffer, "substring(%s,1,%d)='%s'",
                               schema_query->result,
-                              char_length, e_text);
+                              object_length, e_objectname);
             appendPQExpBuffer(&query_buffer, " AND %s",
                               schema_query->viscondition);

@@ -4715,35 +4776,32 @@ _complete_from_query(const char *simple_query,
              */
             if (strcmp(schema_query->catname,
                        "pg_catalog.pg_class c") == 0 &&
-                strncmp(text, "pg_", 3) != 0)
+                strncmp(objectname, "pg_", 3) != 0)
             {
                 appendPQExpBufferStr(&query_buffer,
                                      " AND c.relnamespace <> (SELECT oid FROM"
                                      " pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')");
             }

-            /*
-             * Add in matching schema names, but only if there is more than
-             * one potential match among schema names.
-             */
+            /* Add in schema names matching the input-so-far */
             appendPQExpBuffer(&query_buffer, "\nUNION\n"
-                              "SELECT pg_catalog.quote_ident(n.nspname) || '.' "
+                              "SELECT NULL::pg_catalog.text, n.nspname, false "
                               "FROM pg_catalog.pg_namespace n "
-                              "WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,%d)='%s'",
-                              char_length, e_text);
-            appendPQExpBuffer(&query_buffer,
-                              " AND (SELECT pg_catalog.count(*)"
-                              " FROM pg_catalog.pg_namespace"
-                              " WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,%d) ="
-                              " substring('%s',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1",
-                              char_length, e_text);
+                              "WHERE substring(n.nspname,1,%d)='%s'",
+                              object_length, e_objectname);

             /*
-             * Add in matching qualified names, but only if there is exactly
-             * one schema matching the input-so-far.
+             * Likewise, suppress system schemas unless the input-so-far
+             * begins with "pg_".
              */
-            appendPQExpBuffer(&query_buffer, "\nUNION\n"
-                              "SELECT pg_catalog.quote_ident(n.nspname) || '.' || %s "
+            if (strncmp(objectname, "pg_", 3) != 0)
+                appendPQExpBufferStr(&query_buffer,
+                                     " AND n.nspname NOT LIKE 'pg\\_%'");
+            }
+            else
+            {
+            /* Input is qualified, so produce only qualified names */
+            appendPQExpBuffer(&query_buffer, "SELECT %s, n.nspname, false "
                               "FROM %s, pg_catalog.pg_namespace n "
                               "WHERE %s = n.oid AND ",
                               qualresult,
@@ -4752,24 +4810,12 @@ _complete_from_query(const char *simple_query,
             if (schema_query->selcondition)
                 appendPQExpBuffer(&query_buffer, "%s AND ",
                                   schema_query->selcondition);
-            appendPQExpBuffer(&query_buffer, "substring(pg_catalog.quote_ident(n.nspname) || '.' || %s,1,%d)='%s'",
+            appendPQExpBuffer(&query_buffer, "substring(%s,1,%d)='%s' AND ",
                               qualresult,
-                              char_length, e_text);
-
-            /*
-             * This condition exploits the single-matching-schema rule to
-             * speed up the query
-             */
-            appendPQExpBuffer(&query_buffer,
-                              " AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,%d) ="
-                              " substring('%s',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1)",
-                              char_length, e_text);
-            appendPQExpBuffer(&query_buffer,
-                              " AND (SELECT pg_catalog.count(*)"
-                              " FROM pg_catalog.pg_namespace"
-                              " WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,%d) ="
-                              " substring('%s',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1",
-                              char_length, e_text);
+                              object_length, e_objectname);
+            appendPQExpBuffer(&query_buffer, "substring(n.nspname,1,%d)='%s'",
+                              schema_length, e_schemaname);
+            }

             /* If an addon query was provided, use it */
             if (simple_query)
@@ -4780,7 +4826,7 @@ _complete_from_query(const char *simple_query,
             Assert(simple_query);
             /* simple_query is an sprintf-style format string */
             appendPQExpBuffer(&query_buffer, simple_query,
-                              char_length, e_text,
+                              object_length, e_objectname,
                               e_info_charp, e_info_charp,
                               e_info_charp2, e_info_charp2);
         }
@@ -4792,25 +4838,49 @@ _complete_from_query(const char *simple_query,
         result = exec_query(query_buffer.data);

         termPQExpBuffer(&query_buffer);
-        free(e_text);
+        if (e_schemaname)
+            free(e_schemaname);
+        free(e_objectname);
         if (e_info_charp)
             free(e_info_charp);
         if (e_info_charp2)
             free(e_info_charp2);
     }

-    /* Find something that matches */
-    if (result && PQresultStatus(result) == PGRES_TUPLES_OK)
+    /* Return the next result, if there is one */
+    if (result && PQresultStatus(result) == PGRES_TUPLES_OK &&
+        list_index < PQntuples(result))
     {
-        const char *item;
+        const char *item = NULL;
+        const char *nsp = NULL;
+        bool        thisverbatim = verbatim;
+
+        if (!PQgetisnull(result, list_index, 0))
+            item = PQgetvalue(result, list_index, 0);
+        if (PQnfields(result) > 1 &&
+            !PQgetisnull(result, list_index, 1))
+            nsp = PQgetvalue(result, list_index, 1);
+        if (PQnfields(result) > 2)
+            thisverbatim = (PQgetvalue(result, list_index, 2)[0] == 't');
+        list_index++;
+
+        if (thisverbatim)
+            return pg_strdup(item);

-        while (list_index < PQntuples(result) &&
-               (item = PQgetvalue(result, list_index++, 0)))
-            if (pg_strncasecmp(text, item, byte_length) == 0)
-                return pg_strdup(item);
+        /*
+         * Hack: if we're returning one single schema name, don't let Readline
+         * add a space after it.  Otherwise it'll stop being part of the
+         * completion subject text, which is not what we want.
+         */
+#ifdef HAVE_RL_COMPLETION_APPEND_CHARACTER
+        if (item == NULL && nsp != NULL && PQntuples(result) == 1)
+            rl_completion_append_character = '\0';
+#endif
+
+        return requote_identifier(nsp, item, schemaquoted, objectquoted);
     }

-    /* If nothing matches, free the db structure and return null */
+    /* If nothing (else) matches, free the db structure and return null */
     PQclear(result);
     result = NULL;
     return NULL;
@@ -5144,6 +5214,199 @@ escape_string(const char *text)
 }


+/*
+ * parse_identifier - Parse a possibly-schema-qualified SQL identifier.
+ *
+ * This involves splitting off the schema name if present, de-quoting,
+ * and downcasing any unquoted text.  We are a bit laxer than the backend
+ * in that we allow just portions of a name to be quoted --- that's because
+ * psql metacommands have traditionally behaved that way.
+ *
+ * Outputs are a malloc'd schema name (NULL if none), malloc'd object name,
+ * and booleans telling whether any part of the schema and object name was
+ * double-quoted.
+ */
+static void
+parse_identifier(const char *ident,
+                 char **schemaname, char **objectname,
+                 bool *schemaquoted, bool *objectquoted)
+{
+    size_t        buflen = strlen(ident) + 1;
+    bool        enc_is_single_byte = (pg_encoding_max_length(pset.encoding) == 1);
+    char       *sname;
+    char       *oname;
+    char       *optr;
+    bool        inquotes;
+
+    /* Initialize, making a certainly-large-enough output buffer */
+    sname = NULL;
+    oname = pg_malloc(buflen);
+    *schemaquoted = *objectquoted = false;
+    /* Scan */
+    optr = oname;
+    inquotes = false;
+    while (*ident)
+    {
+        unsigned char ch = (unsigned char) *ident++;
+
+        if (ch == '"')
+        {
+            if (inquotes && *ident == '"')
+            {
+                /* two quote marks within a quoted identifier = emit quote */
+                *optr++ = '"';
+                ident++;
+            }
+            else
+            {
+                inquotes = !inquotes;
+                *objectquoted = true;
+            }
+        }
+        else if (ch == '.' && !inquotes)
+        {
+            /* Found a schema name, transfer it to sname / *schemaquoted */
+            *optr = '\0';
+            free(sname);        /* drop any catalog name */
+            sname = oname;
+            oname = pg_malloc(buflen);
+            optr = oname;
+            *schemaquoted = *objectquoted;
+            *objectquoted = false;
+        }
+        else
+        {
+            if (!inquotes)
+            {
+                /*
+                 * This downcasing transformation should match the backend's
+                 * downcase_identifier() as best we can.  We do not know the
+                 * backend's locale, though, so it's necessarily approximate.
+                 * We assume that psql is operating in the same locale and
+                 * encoding as the backend.
+                 */
+                if (ch >= 'A' && ch <= 'Z')
+                    ch += 'a' - 'A';
+                else if (enc_is_single_byte && IS_HIGHBIT_SET(ch) && isupper(ch))
+                    ch = tolower(ch);
+            }
+            *optr++ = (char) ch;
+        }
+    }
+
+    *optr = '\0';
+    *schemaname = sname;
+    *objectname = oname;
+}
+
+
+/*
+ * requote_identifier - Reconstruct a possibly-schema-qualified SQL identifier.
+ *
+ * Build a malloc'd string containing the identifier, with quoting applied
+ * as necessary.  This is more or less the inverse of parse_identifier;
+ * in particular, if an input component was quoted, we'll quote the output
+ * even when that isn't strictly required.
+ *
+ * Unlike parse_identifier, we handle the case where a schema and no
+ * object name is provided, producing just "schema.".
+ */
+static char *
+requote_identifier(const char *schemaname, const char *objectname,
+                   bool quote_schema, bool quote_object)
+{
+    char       *result;
+    size_t        buflen = 1;        /* count the trailing \0 */
+    char       *ptr;
+
+    /*
+     * We could use PQescapeIdentifier for some of this, but not all, and it
+     * adds more notational cruft than it seems worth.
+     */
+    if (schemaname)
+    {
+        buflen += strlen(schemaname) + 1;    /* +1 for the dot */
+        if (!quote_schema)
+            quote_schema = identifier_needs_quotes(schemaname);
+        if (quote_schema)
+        {
+            buflen += 2;        /* account for quote marks */
+            for (const char *p = schemaname; *p; p++)
+            {
+                if (*p == '"')
+                    buflen++;
+            }
+        }
+    }
+    if (objectname)
+    {
+        buflen += strlen(objectname);
+        if (!quote_object)
+            quote_object = identifier_needs_quotes(objectname);
+        if (quote_object)
+        {
+            buflen += 2;        /* account for quote marks */
+            for (const char *p = objectname; *p; p++)
+            {
+                if (*p == '"')
+                    buflen++;
+            }
+        }
+    }
+    result = pg_malloc(buflen);
+    ptr = result;
+    if (schemaname)
+    {
+        if (quote_schema)
+            *ptr++ = '"';
+        for (const char *p = schemaname; *p; p++)
+        {
+            *ptr++ = *p;
+            if (*p == '"')
+                *ptr++ = '"';
+        }
+        if (quote_schema)
+            *ptr++ = '"';
+        *ptr++ = '.';
+    }
+    if (objectname)
+    {
+        if (quote_object)
+            *ptr++ = '"';
+        for (const char *p = objectname; *p; p++)
+        {
+            *ptr++ = *p;
+            if (*p == '"')
+                *ptr++ = '"';
+        }
+        if (quote_object)
+            *ptr++ = '"';
+    }
+    *ptr = '\0';
+    return result;
+}
+
+
+/*
+ * Detect whether an identifier must be double-quoted.
+ *
+ * Note we'll quote anything that's not ASCII; the backend's quote_ident()
+ * does the same.
+ */
+static bool
+identifier_needs_quotes(const char *ident)
+{
+    if (!((ident[0] >= 'a' && ident[0] <= 'z') || ident[0] == '_'))
+        return true;
+    if (strspn(ident, "abcdefghijklmnopqrstuvwxyz0123456789_") != strlen(ident))
+        return true;
+
+    /* XXX is it worth checking for SQL reserved words? */
+
+    return false;
+}
+
+
 /*
  * Execute a query and report any errors. This should be the preferred way of
  * talking to the database in this file.

pgsql-hackers by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: JSONB docs patch
Next
From: Mark Dilger
Date:
Subject: Re: CREATEROLE and role ownership hierarchies