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

Tom Lane <tgl@sss.pgh.pa.us> writes:

> =?utf-8?Q?Dagfinn_Ilmari_Manns=C3=A5ker?= <ilmari@ilmari.org> writes:
>> I just noticed I left out the = in the match check, here's an updated
>> patch that fixes that.
>
> Hmm .... is that actually going to be useful in that form?
> Most time zone names contain slashes and will therefore require
> single-quoting.  I think you might need pushups comparable to
> COMPLETE_WITH_ENUM_VALUE.

With readline (which is what I tested on) the completion works with or
without a single quote, but the user has to supply the quote themselves
for non-identifier-syntax timezone names.

I wasn't aware of the difference in behaviour with libedit, but now that
I've tested I agree that quoting things even when not strictly needed is
better.

This does however have the unfortunate side effect that on readline it
will suggest DEFAULT even after a single quote, which is not valid.

> Also, personally, I'd rather not smash the names to lower case.
> I think that's a significant decrement of readability.

That was mainly for convenience of not having to capitalise the place
names when typing (since they are accepted case insensitively).  A
compromise would be to lower-case it in the WHERE, but not in the
SELECT, as in the attached v3 patch.

I've tested this version on Debian stable with both readline 8.1-1 and
libedit 3.1-20191231-2.

- ilmari

From 07ed215e983fe4fda10cf92ddd12991f76e1410d Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Wed, 16 Mar 2022 12:52:21 +0000
Subject: [PATCH v3] =?UTF-8?q?Add=20tab=20completion=20for=20SET=20TimeZon?=
 =?UTF-8?q?e=20TO=20=E2=80=A6?=
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Because the value (usually) needs single-quoting, and libedit includes
the leading quote in the text passed to the tab completion, but
readline doesn't, handle it simlarly to what we do for enum values.

This does however mean that under readline it'll include DEFAULT even
after an opening single quote, which is not valid.

Match then names case-insensitively for convenience, but return them
in the original case for legibility.
---
 src/bin/psql/tab-complete.c | 27 +++++++++++++++++++++++++++
 1 file changed, 27 insertions(+)

diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 17172827a9..bb2e6f47a7 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -402,6 +402,19 @@ do { \
     matches = rl_completion_matches(text, complete_from_schema_query); \
 } while (0)
 
+#define COMPLETE_WITH_TIMEZONE_NAME()            \
+do { \
+    static const char *const list[] = { "DEFAULT", NULL }; \
+    if (text[0] == '\'' || \
+        start == 0 || rl_line_buffer[start - 1] != '\'') \
+        completion_charp = Query_for_list_of_timezone_names_quoted; \
+    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 +1118,18 @@ 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 \
+" SELECT name FROM ("\
+"   SELECT pg_catalog.quote_literal(name) AS name "\
+"     FROM pg_catalog.pg_timezone_names() "\
+"  ) ss "\
+"  WHERE 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
@@ -4171,6 +4196,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 */
-- 
2.30.2