Re: Improve tab completion for various SET/RESET forms - Mailing list pgsql-hackers

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

> I just noticed that in addition to ALTER ROLE ... RESET being buggy, the
> ALTER DATABASE ... RESET query doesn't schema-qualify the unnest() call.
> Here's an updated patch series that fixes that too.  The first two are
> bug fixes to features new in 18 and should IMO be committed before
> that's released.  The rest can wait for 19.

Now that Tomas has committed the two bugfixes, here's the rest of the
patches rebased over that.

- ilmari

From e9880ce7a786306ba075c795030bb81a240d0452 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Mon, 9 Jun 2025 20:39:15 +0100
Subject: [PATCH v3 1/3] Add tab completion for ALTER TABLE ... ALTER COLUMN
 ... RESET

Unlike SET, it only takes parenthesised attribute options.
---
 src/bin/psql/tab-complete.in.c | 10 +++++++---
 1 file changed, 7 insertions(+), 3 deletions(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 1f2ca946fc5..176ae1284be 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2913,9 +2913,13 @@ match_previous_words(int pattern_id,
                       "STATISTICS", "STORAGE",
         /* a subset of ALTER SEQUENCE options */
                       "INCREMENT", "MINVALUE", "MAXVALUE", "START", "NO", "CACHE", "CYCLE");
-    /* ALTER TABLE ALTER [COLUMN] <foo> SET ( */
-    else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET", "(") ||
-             Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET", "("))
+    /* ALTER TABLE ALTER [COLUMN] <foo> RESET */
+    else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "RESET") ||
+             Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "RESET"))
+        COMPLETE_WITH("(");
+     /* ALTER TABLE ALTER [COLUMN] <foo> SET|RESET ( */
+    else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET|RESET", "(") ||
+             Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET|RESET", "("))
         COMPLETE_WITH("n_distinct", "n_distinct_inherited");
     /* ALTER TABLE ALTER [COLUMN] <foo> SET COMPRESSION */
     else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET", "COMPRESSION") ||
-- 
2.50.1

From e3e7fe0378af2d04f4c92e3fdc17fccaf2580d56 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Mon, 9 Jun 2025 20:41:29 +0100
Subject: [PATCH v3 2/3] Add tab completion for ALTER FOREIGN TABLE ... SET

The schema is the only thing that can be set for foreign tables.
---
 src/bin/psql/tab-complete.in.c | 4 ++++
 1 file changed, 4 insertions(+)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 176ae1284be..d31f5780727 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2435,6 +2435,10 @@ match_previous_words(int pattern_id,
         COMPLETE_WITH("ADD", "ALTER", "DISABLE TRIGGER", "DROP", "ENABLE",
                       "INHERIT", "NO INHERIT", "OPTIONS", "OWNER TO",
                       "RENAME", "SET", "VALIDATE CONSTRAINT");
+    else if (Matches("ALTER", "FOREIGN", "TABLE", MatchAny, "SET"))
+        COMPLETE_WITH("SCHEMA");
+    else if (Matches("ALTER", "FOREIGN", "TABLE", MatchAny, "SET", "SCHEMA"))
+        COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
 
     /* ALTER INDEX */
     else if (Matches("ALTER", "INDEX"))
-- 
2.50.1

From 6c165b8b42346620a92973e1003db806385f6d92 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Mon, 9 Jun 2025 20:48:43 +0100
Subject: [PATCH v3 3/3] Improve tab completion for RESET

Only complete variables that have been set in the current session,
plus the keywords ALL, ROLE and SESSION (which will subsequently be
completed with AUTHORIZATION).
---
 src/bin/psql/tab-complete.in.c | 17 ++++++++++++++---
 1 file changed, 14 insertions(+), 3 deletions(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index d31f5780727..e3b685ebe10 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1047,6 +1047,12 @@ static const SchemaQuery Query_for_trigger_of_table = {
 " WHERE context IN ('user', 'superuser') "\
 "   AND pg_catalog.lower(name) LIKE pg_catalog.lower('%s')"
 
+#define Query_for_list_of_session_vars \
+"SELECT pg_catalog.lower(name) FROM pg_catalog.pg_settings "\
+" WHERE context IN ('user', 'superuser') "\
+"   AND source = 'session' "\
+"   AND pg_catalog.lower(name) LIKE pg_catalog.lower('%s')"
+
 #define Query_for_list_of_show_vars \
 "SELECT pg_catalog.lower(name) FROM pg_catalog.pg_settings "\
 " WHERE pg_catalog.lower(name) LIKE pg_catalog.lower('%s')"
@@ -5027,9 +5033,8 @@ match_previous_words(int pattern_id,
 
 /* SET, RESET, SHOW */
     /* Complete with a variable name */
-    else if (TailMatches("SET|RESET") &&
-             !TailMatches("UPDATE", MatchAny, "SET") &&
-             !TailMatches("ALTER", "DATABASE|USER|ROLE", MatchAny, "RESET"))
+    else if (TailMatches("SET") &&
+             !TailMatches("UPDATE", MatchAny, "SET"))
         COMPLETE_WITH_QUERY_VERBATIM_PLUS(Query_for_list_of_set_vars,
                                           "CONSTRAINTS",
                                           "TRANSACTION",
@@ -5037,6 +5042,12 @@ match_previous_words(int pattern_id,
                                           "ROLE",
                                           "TABLESPACE",
                                           "ALL");
+    /* Complete with variables set in the current session */
+    else if (Matches("RESET"))
+        COMPLETE_WITH_QUERY_VERBATIM_PLUS(Query_for_list_of_session_vars,
+                                          "ALL",
+                                          "ROLE",
+                                          "SESSION");
     else if (Matches("SHOW"))
         COMPLETE_WITH_QUERY_VERBATIM_PLUS(Query_for_list_of_show_vars,
                                           "SESSION AUTHORIZATION",
-- 
2.50.1