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

Dagfinn Ilmari Mannsåker <ilmari@ilmari.org> writes:

> Tom Lane <tgl@sss.pgh.pa.us> writes:
>
>> 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 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.

- ilmari

From ad4f37359852fa08ec377d86f0bad5f23dff3a0b 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 v4] =?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 the names case-insensitively for convenience, but return them in
the original case for legibility.
---
 src/bin/psql/tab-complete.c | 25 +++++++++++++++++++++++++
 1 file changed, 25 insertions(+)

diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 17172827a9..c985781b27 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,16 @@ 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 pg_catalog.quote_literal(name) AS name "\
+"  FROM pg_catalog.pg_timezone_names() "\
+" 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 +4194,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