On 2022/09/21 0:51, Alvaro Herrera wrote:
The rules starting at line 4111 make me a bit nervous, since nowhere
we're restricting them to operating only on MERGE lines. I don't think
it's a real problem since USING is not terribly common anyway. Likewise
for the ones with WHEN [NOT] MATCHED. I kinda wish we had a way to
search for stuff like "keyword MERGE appears earlier in the command",
but we don't have that.
Yeah, I was thinking the same when updating the patch.
How about adding something like PartialMatches() that checks whether
the keywords are included in the input string or not? If so, we can restrict
some tab-completion rules to operating only on MERGE, as follows. I attached
the WIP patch (0002 patch) that introduces PartialMatches().
Is this approach over-complicated? Thought?
+ else if (PartialMatches("MERGE", "INTO", MatchAny, "USING") ||
+ PartialMatches("MERGE", "INTO", MatchAny, "AS", MatchAny,
"USING") ||
+ PartialMatches("MERGE", "INTO", MatchAny, MatchAny,
"USING"))
+ {
+ /* Complete MERGE INTO ... ON with target table attributes */
+ if (TailMatches("INTO", MatchAny, "USING", MatchAny, "ON"))
+ COMPLETE_WITH_ATTR(prev4_wd);
+ else if (TailMatches("INTO", MatchAny, "AS", MatchAny, "USING", MatchAny,
"AS", MatchAny, "ON"))
+ COMPLETE_WITH_ATTR(prev8_wd);
+ else if (TailMatches("INTO", MatchAny, MatchAny, "USING", MatchAny,
MatchAny, "ON"))
+ COMPLETE_WITH_ATTR(prev6_wd);
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONFrom 671b4dff2e259b4d148dcbfaee0611fc2bddce85 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fu...@postgresql.org>
Date: Wed, 21 Sep 2022 11:46:59 +0900
Subject: [PATCH 1/2] psql: Improve tab-completion for MERGE.
---
src/bin/psql/tab-complete.c | 102 +++++++++++++++++++++++-------------
1 file changed, 67 insertions(+), 35 deletions(-)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index f3465adb85..820f47d23a 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1669,7 +1669,7 @@ psql_completion(const char *text, int start, int end)
"COMMENT", "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE",
"DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE",
"EXPLAIN",
"FETCH", "GRANT", "IMPORT FOREIGN SCHEMA", "INSERT INTO",
"LISTEN", "LOAD", "LOCK",
- "MERGE", "MOVE", "NOTIFY", "PREPARE",
+ "MERGE INTO", "MOVE", "NOTIFY", "PREPARE",
"REASSIGN", "REFRESH MATERIALIZED VIEW", "REINDEX", "RELEASE",
"RESET", "REVOKE", "ROLLBACK",
"SAVEPOINT", "SECURITY LABEL", "SELECT", "SET", "SHOW", "START",
@@ -3641,7 +3641,7 @@ psql_completion(const char *text, int start, int end)
*/
else if (Matches("EXPLAIN"))
COMPLETE_WITH("SELECT", "INSERT INTO", "DELETE FROM", "UPDATE",
"DECLARE",
- "MERGE", "EXECUTE", "ANALYZE",
"VERBOSE");
+ "MERGE INTO", "EXECUTE", "ANALYZE",
"VERBOSE");
else if (HeadMatches("EXPLAIN", "(*") &&
!HeadMatches("EXPLAIN", "(*)"))
{
@@ -3660,12 +3660,12 @@ psql_completion(const char *text, int start, int end)
}
else if (Matches("EXPLAIN", "ANALYZE"))
COMPLETE_WITH("SELECT", "INSERT INTO", "DELETE FROM", "UPDATE",
"DECLARE",
- "MERGE", "EXECUTE", "VERBOSE");
+ "MERGE INTO", "EXECUTE", "VERBOSE");
else if (Matches("EXPLAIN", "(*)") ||
Matches("EXPLAIN", "VERBOSE") ||
Matches("EXPLAIN", "ANALYZE", "VERBOSE"))
COMPLETE_WITH("SELECT", "INSERT INTO", "DELETE FROM", "UPDATE",
"DECLARE",
- "MERGE", "EXECUTE");
+ "MERGE INTO", "EXECUTE");
/* FETCH && MOVE */
@@ -4065,58 +4065,90 @@ psql_completion(const char *text, int start, int end)
else if (HeadMatches("LOCK") && TailMatches("IN", "SHARE"))
COMPLETE_WITH("MODE", "ROW EXCLUSIVE MODE",
"UPDATE EXCLUSIVE MODE");
+
+ /* Complete LOCK [TABLE] [ONLY] <table> [IN lockmode MODE] with
"NOWAIT" */
+ else if (HeadMatches("LOCK") && TailMatches("MODE"))
+ COMPLETE_WITH("NOWAIT");
+
/* MERGE --- can be inside EXPLAIN */
else if (TailMatches("MERGE"))
COMPLETE_WITH("INTO");
else if (TailMatches("MERGE", "INTO"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_mergetargets);
+
+ /* Complete MERGE INTO <table> [[AS] <alias>] with USING */
else if (TailMatches("MERGE", "INTO", MatchAny))
COMPLETE_WITH("USING", "AS");
- else if (TailMatches("MERGE", "INTO", MatchAny, "USING"))
- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
- /* with [AS] alias */
- else if (TailMatches("MERGE", "INTO", MatchAny, "AS", MatchAny))
- COMPLETE_WITH("USING");
- else if (TailMatches("MERGE", "INTO", MatchAny, MatchAny))
+ else if (TailMatches("MERGE", "INTO", MatchAny, "AS", MatchAny) ||
+ TailMatches("MERGE", "INTO", MatchAny,
MatchAnyExcept("USING|AS")))
COMPLETE_WITH("USING");
- else if (TailMatches("MERGE", "INTO", MatchAny, "AS", MatchAny,
"USING"))
- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
- else if (TailMatches("MERGE", "INTO", MatchAny, MatchAny, "USING"))
- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
- /* ON */
- else if (TailMatches("MERGE", "INTO", MatchAny, "USING", MatchAny))
- COMPLETE_WITH("ON");
- else if (TailMatches("INTO", MatchAny, "AS", MatchAny, "USING",
MatchAny, "AS", MatchAny))
- COMPLETE_WITH("ON");
- else if (TailMatches("INTO", MatchAny, MatchAny, "USING", MatchAny,
MatchAny))
+
+ /*
+ * Complete MERGE INTO ... USING with a list of relations supporting
+ * SELECT
+ */
+ else if (TailMatches("MERGE", "INTO", MatchAny, "USING") ||
+ TailMatches("MERGE", "INTO", MatchAny, "AS", MatchAny,
"USING") ||
+ TailMatches("MERGE", "INTO", MatchAny, MatchAny,
"USING"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_selectables);
+
+ /*
+ * Complete MERGE INTO <table> [[AS] <alias>] USING <relations> [[AS]
+ * alias] with ON
+ */
+ else if (TailMatches("MERGE", "INTO", MatchAny, "USING", MatchAny) ||
+ TailMatches("MERGE", "INTO", MatchAny, "AS", MatchAny,
"USING", MatchAny) ||
+ TailMatches("MERGE", "INTO", MatchAny, MatchAny,
"USING", MatchAny))
+ COMPLETE_WITH("AS", "ON");
+ else if (TailMatches("MERGE", "INTO", MatchAny, "USING", MatchAny,
"AS", MatchAny) ||
+ TailMatches("MERGE", "INTO", MatchAny, "AS", MatchAny,
"USING", MatchAny, "AS", MatchAny) ||
+ TailMatches("MERGE", "INTO", MatchAny, MatchAny,
"USING", MatchAny, "AS", MatchAny) ||
+ TailMatches("MERGE", "INTO", MatchAny, "USING",
MatchAny, MatchAnyExcept("ON|AS")) ||
+ TailMatches("MERGE", "INTO", MatchAny, "AS", MatchAny,
"USING", MatchAny, MatchAnyExcept("ON|AS")) ||
+ TailMatches("MERGE", "INTO", MatchAny, MatchAny,
"USING", MatchAny, MatchAnyExcept("ON|AS")))
COMPLETE_WITH("ON");
- /* ON condition */
+
+ /* Complete MERGE INTO ... ON with target table attributes */
else if (TailMatches("INTO", MatchAny, "USING", MatchAny, "ON"))
COMPLETE_WITH_ATTR(prev4_wd);
else if (TailMatches("INTO", MatchAny, "AS", MatchAny, "USING",
MatchAny, "AS", MatchAny, "ON"))
COMPLETE_WITH_ATTR(prev8_wd);
else if (TailMatches("INTO", MatchAny, MatchAny, "USING", MatchAny,
MatchAny, "ON"))
COMPLETE_WITH_ATTR(prev6_wd);
- /* WHEN [NOT] MATCHED */
- else if (TailMatches("USING", MatchAny, "ON", MatchAny))
- COMPLETE_WITH("WHEN MATCHED", "WHEN NOT MATCHED");
- else if (TailMatches("USING", MatchAny, "AS", MatchAny, "ON", MatchAny))
- COMPLETE_WITH("WHEN MATCHED", "WHEN NOT MATCHED");
- else if (TailMatches("USING", MatchAny, MatchAny, "ON", MatchAny))
+
+ /*
+ * Complete ... USING <relation> [[AS] alias] ON join condition
+ * (consisting of one or three words typically used) with WHEN [NOT]
+ * MATCHED
+ */
+ else if (TailMatches("USING", MatchAny, "ON", MatchAny) ||
+ TailMatches("USING", MatchAny, "AS", MatchAny, "ON",
MatchAny) ||
+ TailMatches("USING", MatchAny, MatchAny, "ON",
MatchAny) ||
+ TailMatches("USING", MatchAny, "ON", MatchAny,
MatchAnyExcept("WHEN"), MatchAnyExcept("WHEN")) ||
+ TailMatches("USING", MatchAny, "AS", MatchAny, "ON",
MatchAny, MatchAnyExcept("WHEN"), MatchAnyExcept("WHEN")) ||
+ TailMatches("USING", MatchAny, MatchAny, "ON",
MatchAny, MatchAnyExcept("WHEN"), MatchAnyExcept("WHEN")))
COMPLETE_WITH("WHEN MATCHED", "WHEN NOT MATCHED");
- else if (TailMatches("WHEN", "MATCHED"))
- COMPLETE_WITH("THEN", "AND");
- else if (TailMatches("WHEN", "NOT", "MATCHED"))
+ else if (TailMatches("USING", MatchAny, "ON", MatchAny, "WHEN") ||
+ TailMatches("USING", MatchAny, "AS", MatchAny, "ON",
MatchAny, "WHEN") ||
+ TailMatches("USING", MatchAny, MatchAny, "ON",
MatchAny, "WHEN") ||
+ TailMatches("USING", MatchAny, "ON", MatchAny,
MatchAny, MatchAny, "WHEN") ||
+ TailMatches("USING", MatchAny, "AS", MatchAny, "ON",
MatchAny, MatchAny, MatchAny, "WHEN") ||
+ TailMatches("USING", MatchAny, MatchAny, "ON",
MatchAny, MatchAny, MatchAny, "WHEN"))
+ COMPLETE_WITH("MATCHED", "NOT MATCHED");
+
+ /* Complete ... WHEN [NOT] MATCHED with THEN/AND */
+ else if (TailMatches("WHEN", "MATCHED") ||
+ TailMatches("WHEN", "NOT", "MATCHED"))
COMPLETE_WITH("THEN", "AND");
+
+ /* Complete ... WHEN MATCHED THEN with UPDATE SET/DELETE/DO NOTHING */
else if (TailMatches("WHEN", "MATCHED", "THEN"))
- COMPLETE_WITH("UPDATE", "DELETE");
+ COMPLETE_WITH("UPDATE SET", "DELETE", "DO NOTHING");
+
+ /* Complete ... WHEN NOT MATCHED THEN with INSERT/DO NOTHING */
else if (TailMatches("WHEN", "NOT", "MATCHED", "THEN"))
COMPLETE_WITH("INSERT", "DO NOTHING");
- /* Complete LOCK [TABLE] [ONLY] <table> [IN lockmode MODE] with
"NOWAIT" */
- else if (HeadMatches("LOCK") && TailMatches("MODE"))
- COMPLETE_WITH("NOWAIT");
-
/* NOTIFY --- can be inside EXPLAIN, RULE, etc */
else if (TailMatches("NOTIFY"))
COMPLETE_WITH_QUERY(Query_for_list_of_channels);
--
2.37.1
From 8c7e25e4a2d939a32751bd9a0d487c510ec66191 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fu...@postgresql.org>
Date: Wed, 21 Sep 2022 13:58:50 +0900
Subject: [PATCH 2/2] psql: Add PartialMatches() macro for better
tab-completion.
---
src/bin/psql/tab-complete.c | 152 +++++++++++++++++++++++++++---------
1 file changed, 113 insertions(+), 39 deletions(-)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 820f47d23a..0b8c252615 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1582,6 +1582,65 @@ HeadMatchesImpl(bool case_sensitive,
return true;
}
+/*
+ * Implementation of PartialMatches and PartialMatchesCS macros: do parts of
+ * the words in previous_words match the variadic arguments?
+ */
+static bool
+PartialMatchesImpl(bool case_sensitive,
+ int previous_words_count, char
**previous_words,
+ int narg,...)
+{
+ va_list args;
+ char *firstarg = NULL;
+
+ if (previous_words_count < narg)
+ return false;
+
+ for (int startpos = 0; startpos < previous_words_count; startpos++)
+ {
+ int argno;
+
+ if (firstarg == NULL)
+ {
+ va_start(args, narg);
+ firstarg = va_arg(args, char *);
+ }
+
+ if (!word_matches(firstarg,
+
previous_words[previous_words_count - startpos - 1],
+ case_sensitive))
+ continue;
+
+ if (previous_words_count - startpos < narg)
+ {
+ va_end(args);
+ return false;
+ }
+
+ for (argno = 1; argno < narg; argno++)
+ {
+ const char *arg = va_arg(args, const char *);
+
+ if (!word_matches(arg,
+
previous_words[previous_words_count - argno - startpos - 1],
+ case_sensitive))
+ break;
+ }
+
+ va_end(args);
+ firstarg = NULL;
+
+ if (argno == narg)
+ return true;
+ }
+
+ if (firstarg != NULL)
+ va_end(args);
+
+ return false;
+}
+
/*
* Check if the final character of 's' is 'c'.
*/
@@ -1663,6 +1722,16 @@ psql_completion(const char *text, int start, int end)
HeadMatchesImpl(true, previous_words_count, previous_words, \
VA_ARGS_NARGS(__VA_ARGS__), __VA_ARGS__)
+ /* Match N words on the line partially, case-insensitively. */
+#define PartialMatches(...) \
+ PartialMatchesImpl(false, previous_words_count, previous_words, \
+ VA_ARGS_NARGS(__VA_ARGS__), __VA_ARGS__)
+
+ /* Match N words on the line partially, case-sensitively. */
+#define PartialMatchesCS(...) \
+ PartialMatchesImpl(true, previous_words_count, previous_words, \
+ VA_ARGS_NARGS(__VA_ARGS__), __VA_ARGS__)
+
/* Known command-starting keywords. */
static const char *const sql_commands[] = {
"ABORT", "ALTER", "ANALYZE", "BEGIN", "CALL", "CHECKPOINT",
"CLOSE", "CLUSTER",
@@ -4108,46 +4177,51 @@ psql_completion(const char *text, int start, int end)
TailMatches("MERGE", "INTO", MatchAny, MatchAny,
"USING", MatchAny, MatchAnyExcept("ON|AS")))
COMPLETE_WITH("ON");
- /* Complete MERGE INTO ... ON with target table attributes */
- else if (TailMatches("INTO", MatchAny, "USING", MatchAny, "ON"))
- COMPLETE_WITH_ATTR(prev4_wd);
- else if (TailMatches("INTO", MatchAny, "AS", MatchAny, "USING",
MatchAny, "AS", MatchAny, "ON"))
- COMPLETE_WITH_ATTR(prev8_wd);
- else if (TailMatches("INTO", MatchAny, MatchAny, "USING", MatchAny,
MatchAny, "ON"))
- COMPLETE_WITH_ATTR(prev6_wd);
+ else if (PartialMatches("MERGE", "INTO", MatchAny, "USING") ||
+ PartialMatches("MERGE", "INTO", MatchAny, "AS",
MatchAny, "USING") ||
+ PartialMatches("MERGE", "INTO", MatchAny, MatchAny,
"USING"))
+ {
+ /* Complete MERGE INTO ... ON with target table attributes */
+ if (TailMatches("INTO", MatchAny, "USING", MatchAny, "ON"))
+ COMPLETE_WITH_ATTR(prev4_wd);
+ else if (TailMatches("INTO", MatchAny, "AS", MatchAny, "USING",
MatchAny, "AS", MatchAny, "ON"))
+ COMPLETE_WITH_ATTR(prev8_wd);
+ else if (TailMatches("INTO", MatchAny, MatchAny, "USING",
MatchAny, MatchAny, "ON"))
+ COMPLETE_WITH_ATTR(prev6_wd);
- /*
- * Complete ... USING <relation> [[AS] alias] ON join condition
- * (consisting of one or three words typically used) with WHEN [NOT]
- * MATCHED
- */
- else if (TailMatches("USING", MatchAny, "ON", MatchAny) ||
- TailMatches("USING", MatchAny, "AS", MatchAny, "ON",
MatchAny) ||
- TailMatches("USING", MatchAny, MatchAny, "ON",
MatchAny) ||
- TailMatches("USING", MatchAny, "ON", MatchAny,
MatchAnyExcept("WHEN"), MatchAnyExcept("WHEN")) ||
- TailMatches("USING", MatchAny, "AS", MatchAny, "ON",
MatchAny, MatchAnyExcept("WHEN"), MatchAnyExcept("WHEN")) ||
- TailMatches("USING", MatchAny, MatchAny, "ON",
MatchAny, MatchAnyExcept("WHEN"), MatchAnyExcept("WHEN")))
- COMPLETE_WITH("WHEN MATCHED", "WHEN NOT MATCHED");
- else if (TailMatches("USING", MatchAny, "ON", MatchAny, "WHEN") ||
- TailMatches("USING", MatchAny, "AS", MatchAny, "ON",
MatchAny, "WHEN") ||
- TailMatches("USING", MatchAny, MatchAny, "ON",
MatchAny, "WHEN") ||
- TailMatches("USING", MatchAny, "ON", MatchAny,
MatchAny, MatchAny, "WHEN") ||
- TailMatches("USING", MatchAny, "AS", MatchAny, "ON",
MatchAny, MatchAny, MatchAny, "WHEN") ||
- TailMatches("USING", MatchAny, MatchAny, "ON",
MatchAny, MatchAny, MatchAny, "WHEN"))
- COMPLETE_WITH("MATCHED", "NOT MATCHED");
-
- /* Complete ... WHEN [NOT] MATCHED with THEN/AND */
- else if (TailMatches("WHEN", "MATCHED") ||
- TailMatches("WHEN", "NOT", "MATCHED"))
- COMPLETE_WITH("THEN", "AND");
-
- /* Complete ... WHEN MATCHED THEN with UPDATE SET/DELETE/DO NOTHING */
- else if (TailMatches("WHEN", "MATCHED", "THEN"))
- COMPLETE_WITH("UPDATE SET", "DELETE", "DO NOTHING");
-
- /* Complete ... WHEN NOT MATCHED THEN with INSERT/DO NOTHING */
- else if (TailMatches("WHEN", "NOT", "MATCHED", "THEN"))
- COMPLETE_WITH("INSERT", "DO NOTHING");
+ /*
+ * Complete ... USING <relation> [[AS] alias] ON join condition
+ * (consisting of one or three words typically used) with WHEN
[NOT]
+ * MATCHED
+ */
+ else if (TailMatches("USING", MatchAny, "ON", MatchAny) ||
+ TailMatches("USING", MatchAny, "AS", MatchAny,
"ON", MatchAny) ||
+ TailMatches("USING", MatchAny, MatchAny, "ON",
MatchAny) ||
+ TailMatches("USING", MatchAny, "ON", MatchAny,
MatchAnyExcept("WHEN"), MatchAnyExcept("WHEN")) ||
+ TailMatches("USING", MatchAny, "AS", MatchAny,
"ON", MatchAny, MatchAnyExcept("WHEN"), MatchAnyExcept("WHEN")) ||
+ TailMatches("USING", MatchAny, MatchAny, "ON",
MatchAny, MatchAnyExcept("WHEN"), MatchAnyExcept("WHEN")))
+ COMPLETE_WITH("WHEN MATCHED", "WHEN NOT MATCHED");
+ else if (TailMatches("USING", MatchAny, "ON", MatchAny, "WHEN")
||
+ TailMatches("USING", MatchAny, "AS", MatchAny,
"ON", MatchAny, "WHEN") ||
+ TailMatches("USING", MatchAny, MatchAny, "ON",
MatchAny, "WHEN") ||
+ TailMatches("USING", MatchAny, "ON", MatchAny,
MatchAny, MatchAny, "WHEN") ||
+ TailMatches("USING", MatchAny, "AS", MatchAny,
"ON", MatchAny, MatchAny, MatchAny, "WHEN") ||
+ TailMatches("USING", MatchAny, MatchAny, "ON",
MatchAny, MatchAny, MatchAny, "WHEN"))
+ COMPLETE_WITH("MATCHED", "NOT MATCHED");
+
+ /* Complete ... WHEN [NOT] MATCHED with THEN/AND */
+ else if (TailMatches("WHEN", "MATCHED") ||
+ TailMatches("WHEN", "NOT", "MATCHED"))
+ COMPLETE_WITH("THEN", "AND");
+
+ /* Complete ... WHEN MATCHED THEN with UPDATE SET/DELETE/DO
NOTHING */
+ else if (TailMatches("WHEN", "MATCHED", "THEN"))
+ COMPLETE_WITH("UPDATE SET", "DELETE", "DO NOTHING");
+
+ /* Complete ... WHEN NOT MATCHED THEN with INSERT/DO NOTHING */
+ else if (TailMatches("WHEN", "NOT", "MATCHED", "THEN"))
+ COMPLETE_WITH("INSERT", "DO NOTHING");
+ }
/* NOTIFY --- can be inside EXPLAIN, RULE, etc */
else if (TailMatches("NOTIFY"))
--
2.37.1
