Re: PATCH: psql tab completion for SELECT - Mailing list pgsql-hackers

Edmund Horner <ejrh00@gmail.com> writes:
> On 26 January 2018 at 13:44, Vik Fearing <vik.fearing@2ndquadrant.com> wrote:
>> On 01/26/2018 01:28 AM, Edmund Horner wrote:
>>> The patch mentioned attempts to put savepoints around the tab
>>> completion query where appropriate.

>> I am -1 on this idea.

> May I ask why?  It doesn't stop psql working against older versions,
> as it checks that the server supports savepoints.

I looked into this patch and was disappointed to discover that it had
only a very ad-hoc solution to the problem of version-dependent tab
completion queries.  We need something better --- in particular, the
recent prokind changes mean that there needs to be a way to make
SchemaQuery queries version-dependent.

So ... here is a modest proposal.  It invents a VersionedQuery concept
and also extends the SchemaQuery infrastructure to allow those to be
versioned.  I have not taken this nearly as far as it could be taken,
since it's mostly just proposing mechanism.  To illustrate the
VersionedQuery infrastructure, I fixed it so it wouldn't send
publication/subscription queries to pre-v10 servers, and to illustrate
the versioned SchemaQuery infrastructure, I fixed the prokind problems.

If people like this approach, I propose to commit this more or less
as-is.  The select-tab-completion patch would then need to be rewritten
to use this infrastructure, but I think that should be straightforward.
As a separate line of work, the infrastructure could be applied to fix
the pre-existing places where tab completion fails against old servers.
But that's probably work for v12 or beyond, unless somebody's really
motivated to do it right now.

            regards, tom lane

diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 47909ed..9d0d45b 100644
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
*************** extern char *filename_completion_functio
*** 71,85 ****
--- 71,112 ----
  PQExpBuffer tab_completion_query_buf = NULL;

  /*
+  * In some situations, the query to find out what names are available to
+  * complete with must vary depending on server version.  We handle this by
+  * storing a list of queries, each tagged with the minimum server version
+  * it will work for.  Each list must be stored in descending server version
+  * order, so that the first satisfactory query is the one to use.
+  *
+  * When the query string is otherwise constant, an array of VersionedQuery
+  * suffices.  Terminate the array with 0/NULL.  (If the search reaches that
+  * entry, we give up and do no completion.)
+  */
+ typedef struct VersionedQuery
+ {
+     int            min_server_version;
+     const char *query;
+ } VersionedQuery;
+
+ /*
   * This struct is used to define "schema queries", which are custom-built
   * to obtain possibly-schema-qualified names of database objects.  There is
   * enough similarity in the structure that we don't want to repeat it each
   * time.  So we put the components of each query into this struct and
   * assemble them with the common boilerplate in _complete_from_query().
+  *
+  * As with VersionedQuery, we can use an array of these if the query details
+  * must vary across versions.
   */
  typedef struct SchemaQuery
  {
      /*
+      * If not zero, minimum server version this struct applies to.  If not
+      * zero, there should be a following struct with a smaller minimum server
+      * version; use catname == NULL in the last entry if it should do nothing.
+      */
+     int            min_server_version;
+
+     /*
       * Name of catalog or catalogs to be queried, with alias, eg.
       * "pg_catalog.pg_class c".  Note that "pg_namespace n" will be added.
       */
*************** static const char *completion_charp;    /*
*** 133,138 ****
--- 160,166 ----
  static const char *const *completion_charpp;    /* to pass a list of strings */
  static const char *completion_info_charp;    /* to pass a second string */
  static const char *completion_info_charp2;    /* to pass a third string */
+ static const VersionedQuery *completion_vquery; /* to pass a VersionedQuery */
  static const SchemaQuery *completion_squery;    /* to pass a SchemaQuery */
  static bool completion_case_sensitive;    /* completion is case sensitive */

*************** static bool completion_case_sensitive;    /
*** 140,146 ****
--- 168,176 ----
   * A few macros to ease typing. You can use these to complete the given
   * string with
   * 1) The results from a query you pass it. (Perhaps one of those below?)
+  *      We support both simple and versioned queries.
   * 2) The results from a schema query you pass it.
+  *      We support both simple and versioned schema queries.
   * 3) The items from a null-pointer-terminated list (with or without
   *      case-sensitive comparison; see also COMPLETE_WITH_LISTn, below).
   * 4) A string constant.
*************** do { \
*** 153,158 ****
--- 183,194 ----
      matches = completion_matches(text, complete_from_query); \
  } while (0)

+ #define COMPLETE_WITH_VERSIONED_QUERY(query) \
+ do { \
+     completion_vquery = query; \
+     matches = completion_matches(text, complete_from_versioned_query); \
+ } while (0)
+
  #define COMPLETE_WITH_SCHEMA_QUERY(query, addon) \
  do { \
      completion_squery = &(query); \
*************** do { \
*** 160,165 ****
--- 196,208 ----
      matches = completion_matches(text, complete_from_schema_query); \
  } while (0)

+ #define COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(query, addon) \
+ do { \
+     completion_squery = query; \
+     completion_vquery = addon; \
+     matches = completion_matches(text, complete_from_versioned_schema_query); \
+ } while (0)
+
  #define COMPLETE_WITH_LIST_CS(list) \
  do { \
      completion_charpp = list; \
*************** do { \
*** 345,366 ****
   * Assembly instructions for schema queries
   */

! static const SchemaQuery Query_for_list_of_aggregates = {
!     /* catname */
!     "pg_catalog.pg_proc p",
!     /* selcondition */
!     "p.prokind = 'a'",
!     /* viscondition */
!     "pg_catalog.pg_function_is_visible(p.oid)",
!     /* namespace */
!     "p.pronamespace",
!     /* result */
!     "pg_catalog.quote_ident(p.proname)",
!     /* qualresult */
!     NULL
  };

  static const SchemaQuery Query_for_list_of_datatypes = {
      /* catname */
      "pg_catalog.pg_type t",
      /* selcondition --- ignore table rowtypes and array types */
--- 388,431 ----
   * Assembly instructions for schema queries
   */

! static const SchemaQuery Query_for_list_of_aggregates[] = {
!     {
!         /* min_server_version */
!         110000,
!         /* catname */
!         "pg_catalog.pg_proc p",
!         /* selcondition */
!         "p.prokind = 'a'",
!         /* viscondition */
!         "pg_catalog.pg_function_is_visible(p.oid)",
!         /* namespace */
!         "p.pronamespace",
!         /* result */
!         "pg_catalog.quote_ident(p.proname)",
!         /* qualresult */
!         NULL
!     },
!     {
!         /* min_server_version */
!         0,
!         /* 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)",
!         /* qualresult */
!         NULL
!     }
  };

  static const SchemaQuery Query_for_list_of_datatypes = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_type t",
      /* selcondition --- ignore table rowtypes and array types */
*************** static const SchemaQuery Query_for_list_
*** 379,384 ****
--- 444,451 ----
  };

  static const SchemaQuery Query_for_list_of_domains = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_type t",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 393,414 ****
      NULL
  };

! static const SchemaQuery Query_for_list_of_functions = {
!     /* catname */
!     "pg_catalog.pg_proc p",
!     /* selcondition */
!     "p.prokind IN ('f', 'w')",
!     /* viscondition */
!     "pg_catalog.pg_function_is_visible(p.oid)",
!     /* namespace */
!     "p.pronamespace",
!     /* result */
!     "pg_catalog.quote_ident(p.proname)",
!     /* qualresult */
!     NULL
  };

  static const SchemaQuery Query_for_list_of_indexes = {
      /* catname */
      "pg_catalog.pg_class c",
      /* selcondition */
--- 460,503 ----
      NULL
  };

! static const SchemaQuery Query_for_list_of_functions[] = {
!     {
!         /* min_server_version */
!         110000,
!         /* catname */
!         "pg_catalog.pg_proc p",
!         /* selcondition */
!         "p.prokind IN ('f', 'w')",
!         /* viscondition */
!         "pg_catalog.pg_function_is_visible(p.oid)",
!         /* namespace */
!         "p.pronamespace",
!         /* result */
!         "pg_catalog.quote_ident(p.proname)",
!         /* qualresult */
!         NULL
!     },
!     {
!         /* min_server_version */
!         0,
!         /* catname */
!         "pg_catalog.pg_proc p",
!         /* selcondition */
!         NULL,
!         /* viscondition */
!         "pg_catalog.pg_function_is_visible(p.oid)",
!         /* namespace */
!         "p.pronamespace",
!         /* result */
!         "pg_catalog.quote_ident(p.proname)",
!         /* qualresult */
!         NULL
!     }
  };

  static const SchemaQuery Query_for_list_of_indexes = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_class c",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 424,445 ****
      NULL
  };

! static const SchemaQuery Query_for_list_of_procedures = {
!     /* catname */
!     "pg_catalog.pg_proc p",
!     /* selcondition */
!     "p.prokind = 'p'",
!     /* viscondition */
!     "pg_catalog.pg_function_is_visible(p.oid)",
!     /* namespace */
!     "p.pronamespace",
!     /* result */
!     "pg_catalog.quote_ident(p.proname)",
!     /* qualresult */
!     NULL
  };

  static const SchemaQuery Query_for_list_of_routines = {
      /* catname */
      "pg_catalog.pg_proc p",
      /* selcondition */
--- 513,541 ----
      NULL
  };

! static const SchemaQuery Query_for_list_of_procedures[] = {
!     {
!         /* min_server_version */
!         110000,
!         /* catname */
!         "pg_catalog.pg_proc p",
!         /* selcondition */
!         "p.prokind = 'p'",
!         /* viscondition */
!         "pg_catalog.pg_function_is_visible(p.oid)",
!         /* namespace */
!         "p.pronamespace",
!         /* result */
!         "pg_catalog.quote_ident(p.proname)",
!         /* qualresult */
!         NULL
!     },
!     {0, NULL}
  };

  static const SchemaQuery Query_for_list_of_routines = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_proc p",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 455,460 ****
--- 551,558 ----
  };

  static const SchemaQuery Query_for_list_of_sequences = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_class c",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 470,475 ****
--- 568,575 ----
  };

  static const SchemaQuery Query_for_list_of_foreign_tables = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_class c",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 485,490 ****
--- 585,592 ----
  };

  static const SchemaQuery Query_for_list_of_tables = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_class c",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 501,506 ****
--- 603,610 ----
  };

  static const SchemaQuery Query_for_list_of_partitioned_tables = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_class c",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 516,521 ****
--- 620,627 ----
  };

  static const SchemaQuery Query_for_list_of_constraints_with_schema = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_constraint c",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 532,537 ****
--- 638,645 ----

  /* Relations supporting INSERT, UPDATE or DELETE */
  static const SchemaQuery Query_for_list_of_updatables = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_class c",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 550,555 ****
--- 658,665 ----
  };

  static const SchemaQuery Query_for_list_of_relations = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_class c",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 565,570 ****
--- 675,682 ----
  };

  static const SchemaQuery Query_for_list_of_tsvmf = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_class c",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 585,590 ****
--- 697,704 ----
  };

  static const SchemaQuery Query_for_list_of_tmf = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_class c",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 602,607 ****
--- 716,723 ----
  };

  static const SchemaQuery Query_for_list_of_tpm = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_class c",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 619,624 ****
--- 735,742 ----
  };

  static const SchemaQuery Query_for_list_of_tm = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_class c",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 635,640 ****
--- 753,760 ----
  };

  static const SchemaQuery Query_for_list_of_views = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_class c",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 650,655 ****
--- 770,777 ----
  };

  static const SchemaQuery Query_for_list_of_matviews = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_class c",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 665,670 ****
--- 787,794 ----
  };

  static const SchemaQuery Query_for_list_of_statistics = {
+     /* min_server_version */
+     0,
      /* catname */
      "pg_catalog.pg_statistic_ext s",
      /* selcondition */
*************** static const SchemaQuery Query_for_list_
*** 925,942 ****
  "   FROM pg_catalog.pg_am "\
  "  WHERE substring(pg_catalog.quote_ident(amname),1,%d)='%s'"

- #define Query_for_list_of_publications \
- " SELECT pg_catalog.quote_ident(pubname) "\
- "   FROM pg_catalog.pg_publication "\
- "  WHERE substring(pg_catalog.quote_ident(pubname),1,%d)='%s'"
-
- #define Query_for_list_of_subscriptions \
- " SELECT pg_catalog.quote_ident(s.subname) "\
- "   FROM pg_catalog.pg_subscription s, pg_catalog.pg_database d "\
- "  WHERE substring(pg_catalog.quote_ident(s.subname),1,%d)='%s' "\
- "    AND d.datname = pg_catalog.current_database() "\
- "    AND s.subdbid = d.oid"
-
  /* the silly-looking length condition is just to eat up the current word */
  #define Query_for_list_of_arguments \
  "SELECT pg_catalog.oidvectortypes(proargtypes)||')' "\
--- 1049,1054 ----
*************** static const SchemaQuery Query_for_list_
*** 1031,1036 ****
--- 1143,1174 ----
  "       and c2.relispartition = 'true'"

  /*
+  * These object types were introduced later than our support cutoff of
+  * server version 7.4.  We use the VersionedQuery infrastructure so that
+  * we don't send certain-to-fail queries to older servers.
+  */
+
+ static const VersionedQuery Query_for_list_of_publications[] = {
+     {100000,
+         " SELECT pg_catalog.quote_ident(pubname) "
+         "   FROM pg_catalog.pg_publication "
+         "  WHERE substring(pg_catalog.quote_ident(pubname),1,%d)='%s'"
+     },
+     {0, NULL}
+ };
+
+ static const VersionedQuery Query_for_list_of_subscriptions[] = {
+     {100000,
+         " SELECT pg_catalog.quote_ident(s.subname) "
+         "   FROM pg_catalog.pg_subscription s, pg_catalog.pg_database d "
+         "  WHERE substring(pg_catalog.quote_ident(s.subname),1,%d)='%s' "
+         "    AND d.datname = pg_catalog.current_database() "
+         "    AND s.subdbid = d.oid"
+     },
+     {0, NULL}
+ };
+
+ /*
   * This is a list of all "things" in Pgsql, which can show up after CREATE or
   * DROP; and there is also a query to get a list of them.
   */
*************** typedef struct
*** 1039,1044 ****
--- 1177,1183 ----
  {
      const char *name;
      const char *query;            /* simple query, or NULL */
+     const VersionedQuery *vquery;    /* versioned query, or NULL */
      const SchemaQuery *squery;    /* schema query, or NULL */
      const bits32 flags;            /* visibility flags, see below */
  } pgsql_thing_t;
*************** typedef struct
*** 1049,1057 ****
  #define THING_NO_SHOW        (THING_NO_CREATE | THING_NO_DROP | THING_NO_ALTER)

  static const pgsql_thing_t words_after_create[] = {
!     {"ACCESS METHOD", NULL, NULL, THING_NO_ALTER},
!     {"AGGREGATE", NULL, &Query_for_list_of_aggregates},
!     {"CAST", NULL, NULL},        /* Casts have complex structures for names, so
                                   * skip it */
      {"COLLATION", "SELECT pg_catalog.quote_ident(collname) FROM pg_catalog.pg_collation WHERE collencoding IN (-1,
pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding()))AND
substring(pg_catalog.quote_ident(collname),1,%d)='%s'"},

--- 1188,1196 ----
  #define THING_NO_SHOW        (THING_NO_CREATE | THING_NO_DROP | THING_NO_ALTER)

  static const pgsql_thing_t words_after_create[] = {
!     {"ACCESS METHOD", NULL, NULL, NULL, THING_NO_ALTER},
!     {"AGGREGATE", NULL, NULL, Query_for_list_of_aggregates},
!     {"CAST", NULL, NULL, NULL}, /* Casts have complex structures for names, so
                                   * skip it */
      {"COLLATION", "SELECT pg_catalog.quote_ident(collname) FROM pg_catalog.pg_collation WHERE collencoding IN (-1,
pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding()))AND
substring(pg_catalog.quote_ident(collname),1,%d)='%s'"},

*************** static const pgsql_thing_t words_after_c
*** 1059,1114 ****
       * CREATE CONSTRAINT TRIGGER is not supported here because it is designed
       * to be used only by pg_dump.
       */
!     {"CONFIGURATION", Query_for_list_of_ts_configurations, 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'"},
      {"DATABASE", Query_for_list_of_databases},
!     {"DEFAULT PRIVILEGES", NULL, NULL, THING_NO_CREATE | THING_NO_DROP},
!     {"DICTIONARY", Query_for_list_of_ts_dictionaries, NULL, THING_NO_SHOW},
!     {"DOMAIN", NULL, &Query_for_list_of_domains},
!     {"EVENT TRIGGER", NULL, NULL},
      {"EXTENSION", Query_for_list_of_extensions},
!     {"FOREIGN DATA WRAPPER", NULL, NULL},
!     {"FOREIGN TABLE", NULL, NULL},
!     {"FUNCTION", NULL, &Query_for_list_of_functions},
      {"GROUP", Query_for_list_of_roles},
!     {"INDEX", NULL, &Query_for_list_of_indexes},
      {"LANGUAGE", Query_for_list_of_languages},
!     {"LARGE OBJECT", NULL, NULL, THING_NO_CREATE | THING_NO_DROP},
!     {"MATERIALIZED VIEW", NULL, &Query_for_list_of_matviews},
!     {"OPERATOR", NULL, NULL},    /* Querying for this is probably not such a
!                                  * good idea. */
!     {"OWNED", NULL, NULL, THING_NO_CREATE | THING_NO_ALTER},    /* for DROP OWNED BY ... */
!     {"PARSER", Query_for_list_of_ts_parsers, NULL, THING_NO_SHOW},
!     {"POLICY", NULL, NULL},
!     {"PROCEDURE", NULL, &Query_for_list_of_procedures},
!     {"PUBLICATION", Query_for_list_of_publications},
      {"ROLE", Query_for_list_of_roles},
!     {"ROUTINE", 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'"},
      {"SCHEMA", Query_for_list_of_schemas},
!     {"SEQUENCE", NULL, &Query_for_list_of_sequences},
      {"SERVER", Query_for_list_of_servers},
!     {"STATISTICS", NULL, &Query_for_list_of_statistics},
!     {"SUBSCRIPTION", Query_for_list_of_subscriptions},
!     {"SYSTEM", NULL, NULL, THING_NO_CREATE | THING_NO_DROP},
!     {"TABLE", NULL, &Query_for_list_of_tables},
      {"TABLESPACE", Query_for_list_of_tablespaces},
!     {"TEMP", NULL, NULL, THING_NO_DROP | THING_NO_ALTER},    /* for CREATE TEMP TABLE
!                                                              * ... */
!     {"TEMPLATE", Query_for_list_of_ts_templates, NULL, THING_NO_SHOW},
!     {"TEMPORARY", NULL, NULL, THING_NO_DROP | THING_NO_ALTER},    /* for CREATE TEMPORARY
!                                                                  * TABLE ... */
!     {"TEXT SEARCH", NULL, NULL},
!     {"TRANSFORM", NULL, NULL},
      {"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"}, 
!     {"TYPE", NULL, &Query_for_list_of_datatypes},
!     {"UNIQUE", NULL, NULL, THING_NO_DROP | THING_NO_ALTER}, /* for CREATE UNIQUE
!                                                              * INDEX ... */
!     {"UNLOGGED", NULL, NULL, THING_NO_DROP | THING_NO_ALTER},    /* for CREATE UNLOGGED
!                                                                  * TABLE ... */
      {"USER", Query_for_list_of_roles " UNION SELECT 'MAPPING FOR'"},
!     {"USER MAPPING FOR", NULL, NULL},
!     {"VIEW", NULL, &Query_for_list_of_views},
      {NULL}                        /* end of list */
  };

--- 1198,1253 ----
       * CREATE CONSTRAINT TRIGGER is not supported here because it is designed
       * 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'"},
      {"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},
!     {"DOMAIN", NULL, NULL, &Query_for_list_of_domains},
!     {"EVENT TRIGGER", NULL, NULL, NULL},
      {"EXTENSION", Query_for_list_of_extensions},
!     {"FOREIGN DATA WRAPPER", NULL, NULL, NULL},
!     {"FOREIGN TABLE", NULL, NULL, NULL},
!     {"FUNCTION", NULL, NULL, Query_for_list_of_functions},
      {"GROUP", Query_for_list_of_roles},
!     {"INDEX", NULL, NULL, &Query_for_list_of_indexes},
      {"LANGUAGE", Query_for_list_of_languages},
!     {"LARGE OBJECT", NULL, NULL, NULL, THING_NO_CREATE | THING_NO_DROP},
!     {"MATERIALIZED VIEW", NULL, NULL, &Query_for_list_of_matviews},
!     {"OPERATOR", NULL, NULL, NULL}, /* Querying for this is probably not such
!                                      * a good idea. */
!     {"OWNED", NULL, NULL, NULL, THING_NO_CREATE | THING_NO_ALTER},    /* for DROP OWNED BY ... */
!     {"PARSER", Query_for_list_of_ts_parsers, NULL, NULL, THING_NO_SHOW},
!     {"POLICY", NULL, NULL, NULL},
!     {"PROCEDURE", NULL, NULL, Query_for_list_of_procedures},
!     {"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'"},
      {"SCHEMA", Query_for_list_of_schemas},
!     {"SEQUENCE", NULL, NULL, &Query_for_list_of_sequences},
      {"SERVER", Query_for_list_of_servers},
!     {"STATISTICS", NULL, NULL, &Query_for_list_of_statistics},
!     {"SUBSCRIPTION", NULL, Query_for_list_of_subscriptions},
!     {"SYSTEM", NULL, NULL, NULL, THING_NO_CREATE | THING_NO_DROP},
!     {"TABLE", NULL, NULL, &Query_for_list_of_tables},
      {"TABLESPACE", Query_for_list_of_tablespaces},
!     {"TEMP", NULL, NULL, NULL, THING_NO_DROP | THING_NO_ALTER}, /* for CREATE TEMP TABLE
!                                                                  * ... */
!     {"TEMPLATE", Query_for_list_of_ts_templates, NULL, NULL, THING_NO_SHOW},
!     {"TEMPORARY", NULL, NULL, NULL, THING_NO_DROP | THING_NO_ALTER},    /* for CREATE TEMPORARY
!                                                                          * TABLE ... */
!     {"TEXT SEARCH", NULL, NULL, NULL},
!     {"TRANSFORM", NULL, NULL, NULL},
      {"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"}, 
!     {"TYPE", NULL, NULL, &Query_for_list_of_datatypes},
!     {"UNIQUE", NULL, NULL, NULL, THING_NO_DROP | THING_NO_ALTER},    /* for CREATE UNIQUE
!                                                                      * INDEX ... */
!     {"UNLOGGED", NULL, NULL, NULL, THING_NO_DROP | THING_NO_ALTER}, /* for CREATE UNLOGGED
!                                                                      * TABLE ... */
      {"USER", Query_for_list_of_roles " UNION SELECT 'MAPPING FOR'"},
!     {"USER MAPPING FOR", NULL, NULL, NULL},
!     {"VIEW", NULL, NULL, &Query_for_list_of_views},
      {NULL}                        /* end of list */
  };

*************** static char *create_command_generator(co
*** 1119,1126 ****
  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_schema_query(const char *text, int state);
! static char *_complete_from_query(int is_schema_query,
                       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);
--- 1258,1268 ----
  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_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,
                       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);
*************** psql_completion(const char *text, int st
*** 2208,2214 ****
          COMPLETE_WITH_LIST4("WORK", "TRANSACTION", "TO SAVEPOINT", "PREPARED");
  /* CALL */
      else if (Matches1("CALL"))
!         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_procedures, NULL);
      else if (Matches2("CALL", MatchAny))
          COMPLETE_WITH_CONST("(");
  /* CLUSTER */
--- 2350,2356 ----
          COMPLETE_WITH_LIST4("WORK", "TRANSACTION", "TO SAVEPOINT", "PREPARED");
  /* CALL */
      else if (Matches1("CALL"))
!         COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_procedures, NULL);
      else if (Matches2("CALL", MatchAny))
          COMPLETE_WITH_CONST("(");
  /* CLUSTER */
*************** psql_completion(const char *text, int st
*** 2654,2660 ****
      else if (HeadMatches2("CREATE", "TRIGGER") && TailMatches1("EXECUTE"))
          COMPLETE_WITH_CONST("PROCEDURE");
      else if (HeadMatches2("CREATE", "TRIGGER") && TailMatches2("EXECUTE", "PROCEDURE"))
!         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions, NULL);

  /* CREATE ROLE,USER,GROUP <name> */
      else if (Matches3("CREATE", "ROLE|GROUP|USER", MatchAny) &&
--- 2796,2802 ----
      else if (HeadMatches2("CREATE", "TRIGGER") && TailMatches1("EXECUTE"))
          COMPLETE_WITH_CONST("PROCEDURE");
      else if (HeadMatches2("CREATE", "TRIGGER") && TailMatches2("EXECUTE", "PROCEDURE"))
!         COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);

  /* CREATE ROLE,USER,GROUP <name> */
      else if (Matches3("CREATE", "ROLE|GROUP|USER", MatchAny) &&
*************** psql_completion(const char *text, int st
*** 3008,3018 ****
          else if (TailMatches1("DOMAIN"))
              COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_domains, NULL);
          else if (TailMatches1("FUNCTION"))
!             COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
          else if (TailMatches1("LANGUAGE"))
              COMPLETE_WITH_QUERY(Query_for_list_of_languages);
          else if (TailMatches1("PROCEDURE"))
!             COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_procedures, NULL);
          else if (TailMatches1("ROUTINE"))
              COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_routines, NULL);
          else if (TailMatches1("SCHEMA"))
--- 3150,3160 ----
          else if (TailMatches1("DOMAIN"))
              COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_domains, NULL);
          else if (TailMatches1("FUNCTION"))
!             COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
          else if (TailMatches1("LANGUAGE"))
              COMPLETE_WITH_QUERY(Query_for_list_of_languages);
          else if (TailMatches1("PROCEDURE"))
!             COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_procedures, NULL);
          else if (TailMatches1("ROUTINE"))
              COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_routines, NULL);
          else if (TailMatches1("SCHEMA"))
*************** psql_completion(const char *text, int st
*** 3483,3489 ****
              COMPLETE_WITH_QUERY(Query_for_list_of_roles);
      }
      else if (TailMatchesCS1("\\da*"))
!         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL);
      else if (TailMatchesCS1("\\dA*"))
          COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
      else if (TailMatchesCS1("\\db*"))
--- 3625,3631 ----
              COMPLETE_WITH_QUERY(Query_for_list_of_roles);
      }
      else if (TailMatchesCS1("\\da*"))
!         COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL);
      else if (TailMatchesCS1("\\dA*"))
          COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
      else if (TailMatchesCS1("\\db*"))
*************** psql_completion(const char *text, int st
*** 3497,3503 ****
      else if (TailMatchesCS1("\\dew*"))
          COMPLETE_WITH_QUERY(Query_for_list_of_fdws);
      else if (TailMatchesCS1("\\df*"))
!         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions, NULL);

      else if (TailMatchesCS1("\\dFd*"))
          COMPLETE_WITH_QUERY(Query_for_list_of_ts_dictionaries);
--- 3639,3645 ----
      else if (TailMatchesCS1("\\dew*"))
          COMPLETE_WITH_QUERY(Query_for_list_of_fdws);
      else if (TailMatchesCS1("\\df*"))
!         COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);

      else if (TailMatchesCS1("\\dFd*"))
          COMPLETE_WITH_QUERY(Query_for_list_of_ts_dictionaries);
*************** psql_completion(const char *text, int st
*** 3541,3547 ****
          COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_relations, NULL);

      else if (TailMatchesCS1("\\ef"))
!         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
      else if (TailMatchesCS1("\\ev"))
          COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);

--- 3683,3689 ----
          COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_relations, NULL);

      else if (TailMatchesCS1("\\ef"))
!         COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
      else if (TailMatchesCS1("\\ev"))
          COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);

*************** psql_completion(const char *text, int st
*** 3650,3656 ****
              COMPLETE_WITH_LIST_CS3("default", "verbose", "terse");
      }
      else if (TailMatchesCS1("\\sf*"))
!         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
      else if (TailMatchesCS1("\\sv*"))
          COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);
      else if (TailMatchesCS1("\\cd|\\e|\\edit|\\g|\\i|\\include|"
--- 3792,3798 ----
              COMPLETE_WITH_LIST_CS3("default", "verbose", "terse");
      }
      else if (TailMatchesCS1("\\sf*"))
!         COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
      else if (TailMatchesCS1("\\sv*"))
          COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);
      else if (TailMatchesCS1("\\cd|\\e|\\edit|\\g|\\i|\\include|"
*************** psql_completion(const char *text, int st
*** 3676,3684 ****
              {
                  if (words_after_create[i].query)
                      COMPLETE_WITH_QUERY(words_after_create[i].query);
                  else if (words_after_create[i].squery)
!                     COMPLETE_WITH_SCHEMA_QUERY(*words_after_create[i].squery,
!                                                NULL);
                  break;
              }
          }
--- 3818,3828 ----
              {
                  if (words_after_create[i].query)
                      COMPLETE_WITH_QUERY(words_after_create[i].query);
+                 else if (words_after_create[i].vquery)
+                     COMPLETE_WITH_VERSIONED_QUERY(words_after_create[i].vquery);
                  else if (words_after_create[i].squery)
!                     COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(words_after_create[i].squery,
!                                                          NULL);
                  break;
              }
          }
*************** alter_command_generator(const char *text
*** 3777,3800 ****
      return create_or_drop_command_generator(text, state, THING_NO_ALTER);
  }

! /* The following two functions are wrappers for _complete_from_query */

  static char *
  complete_from_query(const char *text, int state)
  {
!     return _complete_from_query(0, text, state);
  }

  static char *
  complete_from_schema_query(const char *text, int state)
  {
!     return _complete_from_query(1, text, state);
  }


  /*
!  * This creates a list of matching things, according to a query pointed to
!  * by completion_charp.
   * The query can be one of two kinds:
   *
   * 1. A simple query which must contain a %d and a %s, which will be replaced
--- 3921,3993 ----
      return create_or_drop_command_generator(text, state, THING_NO_ALTER);
  }

! /*
!  * These functions generate lists using server queries.
!  * They are all wrappers for _complete_from_query.
!  */

  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);
! }
!
! static char *
! complete_from_versioned_query(const char *text, int state)
! {
!     const VersionedQuery *vquery = completion_vquery;
!
!     /* Find appropriate array element */
!     while (pset.sversion < vquery->min_server_version)
!         vquery++;
!     /* Fail completion if server is too old */
!     if (vquery->query == NULL)
!         return NULL;
!
!     return _complete_from_query(vquery->query, NULL, text, state);
  }

  static char *
  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);
! }
!
! static char *
! complete_from_versioned_schema_query(const char *text, int state)
! {
!     const SchemaQuery *squery = completion_squery;
!     const VersionedQuery *vquery = completion_vquery;
!
!     /* Find appropriate array element */
!     while (pset.sversion < squery->min_server_version)
!         squery++;
!     /* Fail completion if server is too old */
!     if (squery->catname == NULL)
!         return NULL;
!
!     /* Likewise for the add-on text, if any */
!     if (vquery)
!     {
!         while (pset.sversion < vquery->min_server_version)
!             vquery++;
!         if (vquery->query == NULL)
!             return NULL;
!     }
!
!     return _complete_from_query(vquery ? vquery->query : NULL,
!                                 squery, text, state);
  }


  /*
!  * This creates a list of matching things, according to a query described by
!  * the initial arguments.  The caller has already done any work needed to
!  * select the appropriate query for the server's version.
!  *
   * The query can be one of two kinds:
   *
   * 1. A simple query which must contain a %d and a %s, which will be replaced
*************** complete_from_schema_query(const char *t
*** 3808,3820 ****
   * %d %s %d %s %d %s %s %d %s
   * where %d is the string length of the text and %s the text itself.
   *
   * 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.
   */
  static char *
! _complete_from_query(int is_schema_query, const char *text, int state)
  {
      static int    list_index,
                  byte_length;
--- 4001,4020 ----
   * %d %s %d %s %d %s %s %d %s
   * where %d is the string length of the text and %s the text itself.
   *
+  * 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.
+  *
   * 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.
+  *
+  * "text" and "state" are supplied by readline.
   */
  static char *
! _complete_from_query(const char *simple_query,
!                      const SchemaQuery *schema_query,
!                      const char *text, int state)
  {
      static int    list_index,
                  byte_length;
*************** _complete_from_query(int is_schema_query
*** 3865,3890 ****

          initPQExpBuffer(&query_buffer);

!         if (is_schema_query)
          {
!             /* completion_squery gives us the pieces to assemble */
!             const char *qualresult = completion_squery->qualresult;

              if (qualresult == NULL)
!                 qualresult = completion_squery->result;

              /* Get unqualified names matching the input-so-far */
              appendPQExpBuffer(&query_buffer, "SELECT %s FROM %s WHERE ",
!                               completion_squery->result,
!                               completion_squery->catname);
!             if (completion_squery->selcondition)
                  appendPQExpBuffer(&query_buffer, "%s AND ",
!                                   completion_squery->selcondition);
              appendPQExpBuffer(&query_buffer, "substring(%s,1,%d)='%s'",
!                               completion_squery->result,
                                char_length, e_text);
              appendPQExpBuffer(&query_buffer, " AND %s",
!                               completion_squery->viscondition);

              /*
               * When fetching relation names, suppress system catalogs unless
--- 4065,4090 ----

          initPQExpBuffer(&query_buffer);

!         if (schema_query)
          {
!             /* schema_query gives us the pieces to assemble */
!             const char *qualresult = schema_query->qualresult;

              if (qualresult == NULL)
!                 qualresult = schema_query->result;

              /* Get unqualified names matching the input-so-far */
              appendPQExpBuffer(&query_buffer, "SELECT %s FROM %s WHERE ",
!                               schema_query->result,
!                               schema_query->catname);
!             if (schema_query->selcondition)
                  appendPQExpBuffer(&query_buffer, "%s AND ",
!                                   schema_query->selcondition);
              appendPQExpBuffer(&query_buffer, "substring(%s,1,%d)='%s'",
!                               schema_query->result,
                                char_length, e_text);
              appendPQExpBuffer(&query_buffer, " AND %s",
!                               schema_query->viscondition);

              /*
               * When fetching relation names, suppress system catalogs unless
*************** _complete_from_query(int is_schema_query
*** 3892,3898 ****
               * between not offering system catalogs for completion at all, and
               * having them swamp the result when the input is just "p".
               */
!             if (strcmp(completion_squery->catname,
                         "pg_catalog.pg_class c") == 0 &&
                  strncmp(text, "pg_", 3) !=0)
              {
--- 4092,4098 ----
               * between not offering system catalogs for completion at all, and
               * having them swamp the result when the input is just "p".
               */
!             if (strcmp(schema_query->catname,
                         "pg_catalog.pg_class c") == 0 &&
                  strncmp(text, "pg_", 3) !=0)
              {
*************** _complete_from_query(int is_schema_query
*** 3926,3936 ****
                                "FROM %s, pg_catalog.pg_namespace n "
                                "WHERE %s = n.oid AND ",
                                qualresult,
!                               completion_squery->catname,
!                               completion_squery->namespace);
!             if (completion_squery->selcondition)
                  appendPQExpBuffer(&query_buffer, "%s AND ",
!                                   completion_squery->selcondition);
              appendPQExpBuffer(&query_buffer, "substring(pg_catalog.quote_ident(n.nspname) || '.' || %s,1,%d)='%s'",
                                qualresult,
                                char_length, e_text);
--- 4126,4136 ----
                                "FROM %s, pg_catalog.pg_namespace n "
                                "WHERE %s = n.oid AND ",
                                qualresult,
!                               schema_query->catname,
!                               schema_query->namespace);
!             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'",
                                qualresult,
                                char_length, e_text);
*************** _complete_from_query(int is_schema_query
*** 3951,3963 ****
                                char_length, e_text);

              /* If an addon query was provided, use it */
!             if (completion_charp)
!                 appendPQExpBuffer(&query_buffer, "\n%s", completion_charp);
          }
          else
          {
!             /* completion_charp is an sprintf-style format string */
!             appendPQExpBuffer(&query_buffer, completion_charp,
                                char_length, e_text,
                                e_info_charp, e_info_charp,
                                e_info_charp2, e_info_charp2);
--- 4151,4164 ----
                                char_length, e_text);

              /* If an addon query was provided, use it */
!             if (simple_query)
!                 appendPQExpBuffer(&query_buffer, "\n%s", simple_query);
          }
          else
          {
!             Assert(simple_query);
!             /* simple_query is an sprintf-style format string */
!             appendPQExpBuffer(&query_buffer, simple_query,
                                char_length, e_text,
                                e_info_charp, e_info_charp,
                                e_info_charp2, e_info_charp2);