Re: Tab completion for SET TimeZone - Mailing list pgsql-hackers

=?utf-8?Q?Dagfinn_Ilmari_Manns=C3=A5ker?= <ilmari@ilmari.org> writes:
> I just realised there's no point in the subselect when I'm not applying
> the same function in the WHERE and the SELECT, so here's an updated
> version that simplifies that.  It also fixes a typo in the commit
> message.

This doesn't work right for me under libedit -- it will correctly
complete "am<TAB>" to "'America/", but then it fails to complete
anything past that.  The reason seems to be that once we have a
leading single quote, libedit will include that in the text passed
to future completion attempts, while readline won't.  I ended up
needing three query variants, as attached (bikeshedding welcome).

I think the reason the COMPLETE_WITH_ENUM_VALUE macro doesn't look
similar is that it hasn't made an attempt to work with input that
the user didn't quote --- that is, if you type
    alter type planets rename value ur<TAB>
it just fails to match anything, instead of providing "'uranus'".
Should we upgrade that likewise?  Not sure it's worth the trouble
though; I think COMPLETE_WITH_ENUM_VALUE is there more as a finger
exercise than because people use it regularly.

I added a regression test case too.

            regards, tom lane

diff --git a/src/bin/psql/t/010_tab_completion.pl b/src/bin/psql/t/010_tab_completion.pl
index a54910680e..c6db1b1591 100644
--- a/src/bin/psql/t/010_tab_completion.pl
+++ b/src/bin/psql/t/010_tab_completion.pl
@@ -338,6 +338,19 @@ check_completion(

 clear_line();

+# check timezone name completion
+check_completion(
+    "SET timezone TO am\t",
+    qr|'America/|,
+    "offer partial timezone name");
+
+check_completion(
+    "new_\t",
+    qr|New_York|,
+    "complete partial timezone name");
+
+clear_line();
+
 # check completion of a keyword offered in addition to object names;
 # such a keyword should obey COMP_KEYWORD_CASE
 foreach (
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 380cbc0b1f..510ea1d926 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -402,6 +402,21 @@ do { \
     matches = rl_completion_matches(text, complete_from_schema_query); \
 } while (0)

+/* timezone completion is mostly like enum label completion */
+#define COMPLETE_WITH_TIMEZONE_NAME() \
+do { \
+    static const char *const list[] = { "DEFAULT", NULL }; \
+    if (text[0] == '\'') \
+        completion_charp = Query_for_list_of_timezone_names_quoted_in; \
+    else if (start == 0 || rl_line_buffer[start - 1] != '\'') \
+        completion_charp = Query_for_list_of_timezone_names_quoted_out; \
+    else \
+        completion_charp = Query_for_list_of_timezone_names_unquoted; \
+    completion_charpp = list;                              \
+    completion_verbatim = true; \
+    matches = rl_completion_matches(text, complete_from_query); \
+} while (0)
+
 #define COMPLETE_WITH_FUNCTION_ARG(function) \
 do { \
     set_completion_reference(function); \
@@ -1105,6 +1120,21 @@ static const SchemaQuery Query_for_trigger_of_table = {
 "   FROM pg_catalog.pg_cursors "\
 "  WHERE name LIKE '%s'"

+#define Query_for_list_of_timezone_names_unquoted \
+" SELECT name "\
+"   FROM pg_catalog.pg_timezone_names() "\
+"  WHERE pg_catalog.lower(name) LIKE pg_catalog.lower('%s')"
+
+#define Query_for_list_of_timezone_names_quoted_out \
+"SELECT pg_catalog.quote_literal(name) AS name "\
+"  FROM pg_catalog.pg_timezone_names() "\
+" WHERE pg_catalog.lower(name) LIKE pg_catalog.lower('%s')"
+
+#define Query_for_list_of_timezone_names_quoted_in \
+"SELECT pg_catalog.quote_literal(name) AS name "\
+"  FROM pg_catalog.pg_timezone_names() "\
+" WHERE pg_catalog.quote_literal(pg_catalog.lower(name)) LIKE pg_catalog.lower('%s')"
+
 /*
  * These object types were introduced later than our support cutoff of
  * server version 9.2.  We use the VersionedQuery infrastructure so that
@@ -4172,6 +4202,8 @@ psql_completion(const char *text, int start, int end)
                                      " AND nspname NOT LIKE E'pg\\\\_temp%%'",
                                      "DEFAULT");
         }
+        else if (TailMatches("TimeZone", "TO|="))
+            COMPLETE_WITH_TIMEZONE_NAME();
         else
         {
             /* generic, type based, GUC support */