Subject: [PATCH 4/6] Introduce word shift and removal feature to psql-completion

From: Kyotaro Horiguchi <[email-masked]>
Date: Thu, 15 Sep 2016 14:44:55 +0900

Currently completion of psql is vulnerable for noise words such like
temp/temporary, unlogged or concurrent. Addition to that, schema
elemsnts in CREATE SCHEMA syntax or some recursive syntaxes are
processed in somewhat bogus way.  To accommodate completion mechanism
for the cases, this patch introduces mainly two features.

1. Add a feature to ignore leading words to process.  New macros
  HEAD_SHIFT, HEAD_SET to shift or set the position of the first word
  to match using other macros. All *MatchesN macros follow
  this. SHIFT_TO_LAST1 is a macro to shift the head to the position
  where the specified word is found last.

2. Add a feature to remove intermediate words from previous_words
  list.  COLLAPSE(s, n) macro removes n words from the s'th position
  (1-based). Removing "noise" words let the succeeding operations
  simple.

Using this features, this patch implements the following things.

1. Properly treat schema elements by shifting head.

 Now we can treat schema elements as the same as normal syntax by
 isolating from leading words. This allow more complex completion for
 each create xxxs.

2. Simplify some "||"-connected matches required to cover noise words
  by removing noise wors.

 CREATE INDEX or some other syntaxes have rather many optional
 elements so appropriate to demonstrate how COLLAPSE can be used.
 ALTER TABLE/COMMENT/COPY/GRANT/REVOKE/BEGINs.

3. Process recursive of CREATE RULE/EXPLAIN/PREPARE using
  Matches/HeadMatches, not with TailMtaches.

 The recursive syntaxes are previously completed using TailMatches
 instead of Match/HeadMatch but that replacement increases the
 restriction in completing the inner commands.  psql_complete_internal
 can be called recursively and it can be used to resolve this.

The changes in this patch also allows us to encapsulate completion
code for common subsyntaxes in functions but this doesn't that.
---
 src/bin/psql/tab-complete-macros.h | 139 ++++++--
 src/bin/psql/tab-complete.c        | 696 ++++++++++++++++++++++---------------
 2 files changed, 514 insertions(+), 321 deletions(-)

diff --git a/src/bin/psql/tab-complete-macros.h b/src/bin/psql/tab-complete-macros.h
index d7e2e3f..bb3cf68 100644
--- a/src/bin/psql/tab-complete-macros.h
+++ b/src/bin/psql/tab-complete-macros.h
@@ -25,41 +25,69 @@
 #define prev8_wd  (previous_words[7])
 #define prev9_wd  (previous_words[8])
 
+/* Return the number of stored words counting head shift */
+#define WORD_COUNT() (previous_words_count - head_shift)
+
 /*
  * Return the index in previous_words for index from the beginning. n is
  * 1-based and the result is 0-based.
  */
-#define HEAD_INDEX(n) \
-	(previous_words_count - (n))
+#define HEAD_INDEX(n) (WORD_COUNT() - (n))
+
+/* Move the position of the beginning word for matching macros.  */
+#define HEAD_SHIFT(n) (head_shift += (n))
+
+/* Set the position of the beginning word for matching macros.  */
+#define HEAD_SET(n) (head_shift = (n))
+
+/*
+ * remove n words from current shifted position. This moves entire the
+ * previous_words regardless of head_shift.
+ */
+#define COLLAPSE(s, n)							\
+	do { \
+		memmove(previous_words + HEAD_INDEX((s) + (n) - 1), \
+				previous_words + HEAD_INDEX((s) - 1), \
+				sizeof(char *) * \
+				(previous_words_count - HEAD_INDEX((s) - 1)));	\
+		previous_words_count -= (n); \
+	} while (0)
+
+/*
+ * Find the position where the specified word appears last and shift to there.
+ * The words before the position will be ignored ever after.
+ */
+#define SHIFT_TO_LAST1(p1) \
+	HEAD_SHIFT(find_last_index_of(p1, previous_words, previous_words_count))
 
 /*
  * Macros for matching the last N words before point, and after head_sift,
  * case-insensitively.
  */
 #define TailMatches1(p1) \
-	(previous_words_count >= 1 && \
+	(WORD_COUNT() >= 1 && \
 	 word_matches(p1, prev_wd))
 
 #define TailMatches2(p2, p1) \
-	(previous_words_count >= 2 && \
+	(WORD_COUNT() >= 2 && \
 	 word_matches(p1, prev_wd) && \
 	 word_matches(p2, prev2_wd))
 
 #define TailMatches3(p3, p2, p1) \
-	(previous_words_count >= 3 && \
+	(WORD_COUNT() >= 3 && \
 	 word_matches(p1, prev_wd) && \
 	 word_matches(p2, prev2_wd) && \
 	 word_matches(p3, prev3_wd))
 
 #define TailMatches4(p4, p3, p2, p1) \
-	(previous_words_count >= 4 && \
+	(WORD_COUNT() >= 4 && \
 	 word_matches(p1, prev_wd) && \
 	 word_matches(p2, prev2_wd) && \
 	 word_matches(p3, prev3_wd) && \
 	 word_matches(p4, prev4_wd))
 
 #define TailMatches5(p5, p4, p3, p2, p1) \
-	(previous_words_count >= 5 && \
+	(WORD_COUNT() >= 5 && \
 	 word_matches(p1, prev_wd) && \
 	 word_matches(p2, prev2_wd) && \
 	 word_matches(p3, prev3_wd) && \
@@ -67,7 +95,7 @@
 	 word_matches(p5, prev5_wd))
 
 #define TailMatches6(p6, p5, p4, p3, p2, p1) \
-	(previous_words_count >= 6 && \
+	(WORD_COUNT() >= 6 && \
 	 word_matches(p1, prev_wd) && \
 	 word_matches(p2, prev2_wd) && \
 	 word_matches(p3, prev3_wd) && \
@@ -76,7 +104,7 @@
 	 word_matches(p6, prev6_wd))
 
 #define TailMatches7(p7, p6, p5, p4, p3, p2, p1) \
-	(previous_words_count >= 7 && \
+	(WORD_COUNT() >= 7 && \
 	 word_matches(p1, prev_wd) && \
 	 word_matches(p2, prev2_wd) && \
 	 word_matches(p3, prev3_wd) && \
@@ -86,7 +114,7 @@
 	 word_matches(p7, prev7_wd))
 
 #define TailMatches8(p8, p7, p6, p5, p4, p3, p2, p1) \
-	(previous_words_count >= 8 && \
+	(WORD_COUNT() >= 8 && \
 	 word_matches(p1, prev_wd) && \
 	 word_matches(p2, prev2_wd) && \
 	 word_matches(p3, prev3_wd) && \
@@ -97,7 +125,7 @@
 	 word_matches(p8, prev8_wd))
 
 #define TailMatches9(p9, p8, p7, p6, p5, p4, p3, p2, p1) \
-	(previous_words_count >= 9 && \
+	(WORD_COUNT() >= 9 && \
 	 word_matches(p1, prev_wd) && \
 	 word_matches(p2, prev2_wd) && \
 	 word_matches(p3, prev3_wd) && \
@@ -113,10 +141,10 @@
 	 * head_shift, case-sensitively.
 	 */
 #define TailMatchesCS1(p1) \
-	(previous_words_count >= 1 && \
+	(WORD_COUNT() >= 1 && \
 	 word_matches_cs(p1, prev_wd))
 #define TailMatchesCS2(p2, p1) \
-	(previous_words_count >= 2 && \
+	(WORD_COUNT() >= 2 && \
 	 word_matches_cs(p1, prev_wd) && \
 	 word_matches_cs(p2, prev2_wd))
 
@@ -125,31 +153,31 @@
 	 * case-insensitively.
 	 */
 #define Matches1(p1) \
-	(previous_words_count == 1 && \
+	(WORD_COUNT() == 1 && \
 	 TailMatches1(p1))
 #define Matches2(p1, p2) \
-	(previous_words_count == 2 && \
+	(WORD_COUNT() == 2 && \
 	 TailMatches2(p1, p2))
 #define Matches3(p1, p2, p3) \
-	(previous_words_count == 3 && \
+	(WORD_COUNT() == 3 && \
 	 TailMatches3(p1, p2, p3))
 #define Matches4(p1, p2, p3, p4) \
-	(previous_words_count == 4 && \
+	(WORD_COUNT() == 4 && \
 	 TailMatches4(p1, p2, p3, p4))
 #define Matches5(p1, p2, p3, p4, p5) \
-	(previous_words_count == 5 && \
+	(WORD_COUNT() == 5 && \
 	 TailMatches5(p1, p2, p3, p4, p5))
 #define Matches6(p1, p2, p3, p4, p5, p6) \
-	(previous_words_count == 6 && \
+	(WORD_COUNT() == 6 && \
 	 TailMatches6(p1, p2, p3, p4, p5, p6))
 #define Matches7(p1, p2, p3, p4, p5, p6, p7) \
-	(previous_words_count == 7 && \
+	(WORD_COUNT() == 7 && \
 	 TailMatches7(p1, p2, p3, p4, p5, p6, p7))
 #define Matches8(p1, p2, p3, p4, p5, p6, p7, p8) \
-	(previous_words_count == 8 && \
+	(WORD_COUNT() == 8 && \
 	 TailMatches8(p1, p2, p3, p4, p5, p6, p7, p8))
 #define Matches9(p1, p2, p3, p4, p5, p6, p7, p8, p9) \
-	(previous_words_count == 9 && \
+	(WORD_COUNT() == 9 && \
 	 TailMatches9(p1, p2, p3, p4, p5, p6, p7, p8, p9))
 
 /*
@@ -195,16 +223,39 @@
 	 word_matches(p5, previous_words[HEAD_INDEX((s) + 4)]) &&			\
 	 word_matches(p6, previous_words[HEAD_INDEX((s) + 5)]))
 
-#define MidMatches7(s,p1, p2, p3, p4, p5, p6, p7)	\
+#define MidMatches7(s,p1, p2, p3, p4, p5, p6, p7)			\
 	(HEAD_INDEX((s) + 6) >= 0 &&							\
-	 word_matches(p1, previous_words[HEAD_INDEX(s)]) &&	\
-	 word_matches(p2, previous_words[HEAD_INDEX((s) + 1)]) &&	\
-	 word_matches(p3, previous_words[HEAD_INDEX((s) + 2)]) &&		\
+	 word_matches(p1, previous_words[HEAD_INDEX(s)]) &&			\
+	 word_matches(p2, previous_words[HEAD_INDEX((s) + 1)]) &&		\
+	 word_matches(p3, previous_words[HEAD_INDEX((s) + 2)]) &&			\
 	 word_matches(p4, previous_words[HEAD_INDEX((s) + 3)]) &&			\
 	 word_matches(p5, previous_words[HEAD_INDEX((s) + 4)]) &&			\
 	 word_matches(p6, previous_words[HEAD_INDEX((s) + 5)]) &&			\
 	 word_matches(p7, previous_words[HEAD_INDEX((s) + 6)]))
 
+#define MidMatches8(s,p1, p2, p3, p4, p5, p6, p7, p8)		\
+	(HEAD_INDEX((s) + 7) >= 0 &&							\
+	 word_matches(p1, previous_words[HEAD_INDEX(s)]) &&				\
+	 word_matches(p2, previous_words[HEAD_INDEX((s) + 1)]) &&		\
+	 word_matches(p3, previous_words[HEAD_INDEX((s) + 2)]) &&		\
+	 word_matches(p4, previous_words[HEAD_INDEX((s) + 3)]) &&		\
+	 word_matches(p5, previous_words[HEAD_INDEX((s) + 4)]) &&		\
+	 word_matches(p6, previous_words[HEAD_INDEX((s) + 5)]) &&		\
+	 word_matches(p7, previous_words[HEAD_INDEX((s) + 6)]) &&		\
+	 word_matches(p8, previous_words[HEAD_INDEX((s) + 7)]))
+
+#define MidMatches9(s,p1, p2, p3, p4, p5, p6, p7, p8, p9)		\
+	(HEAD_INDEX((s) + 8) >= 0 &&							\
+	 word_matches(p1, previous_words[HEAD_INDEX(s)]) &&				\
+	 word_matches(p2, previous_words[HEAD_INDEX((s) + 1)]) &&		\
+	 word_matches(p3, previous_words[HEAD_INDEX((s) + 2)]) &&		\
+	 word_matches(p4, previous_words[HEAD_INDEX((s) + 3)]) &&		\
+	 word_matches(p5, previous_words[HEAD_INDEX((s) + 4)]) &&		\
+	 word_matches(p6, previous_words[HEAD_INDEX((s) + 5)]) &&		\
+	 word_matches(p7, previous_words[HEAD_INDEX((s) + 6)]) &&		\
+	 word_matches(p8, previous_words[HEAD_INDEX((s) + 7)]) &&		\
+	 word_matches(p9, previous_words[HEAD_INDEX((s) + 8)]))
+
 #define HeadMatches1(p1) \
 	MidMatches1(1, p1)
 #define HeadMatches2(p1, p2) \
@@ -219,6 +270,10 @@
 	MidMatches6(1, p1, p2, p3, p4, p5, p6)
 #define HeadMatches7(p1, p2, p3, p4, p5, p6, p7) \
 	MidMatches7(1, p1, p2, p3, p4, p5, p6, p7)
+#define HeadMatches8(p1, p2, p3, p4, p5, p6, p7, p8)	\
+	MidMatches8(1, p1, p2, p3, p4, p5, p6, p7, p8)
+#define HeadMatches9(p1, p2, p3, p4, p5, p6, p7, p8, p9)	\
+	MidMatches9(1, p1, p2, p3, p4, p5, p6, p7, p8, p9)
 
 /*
  * A few macros to ease typing. You can use these to complete the given
@@ -240,12 +295,6 @@
 
 #define COMPLETION_CHARP (completion_charp->data)
 
-#define COMPLETE_WITH_QUERY(query)				\
-do { \
-	SET_COMP_CHARP(query);	\
-	return completion_matches(text, complete_from_query);	\
-} while (0)
-
 /*
  * COMPLETE_WITH_QUERY with additional keywords. Keywords are complete
  * case-sensitively
@@ -257,11 +306,8 @@ do { \
 	return completion_matches(text, complete_from_query);	\
 } while (0)
 
-#define COMPLETE_WITH_SCHEMA_QUERY(query) \
-do { \
-	completion_squery = &(query); \
-	return completion_matches(text, complete_from_schema_query); \
-} while (0)
+#define COMPLETE_WITH_QUERY(query) COMPLETE_WITH_QUERY_KW((query), "")
+
 
 /*
  * COMPLETE_WITH_SCHEMA_QUERY with additional keywords. Keywords are complete
@@ -274,6 +320,8 @@ do { \
 	return completion_matches(text, complete_from_schema_query); \
 } while (0)
 
+#define COMPLETE_WITH_SCHEMA_QUERY(query) COMPLETE_WITH_SCHEMA_QUERY_KW((query), "")
+
 #define COMPLETE_WITH_LIST_CS(list) \
 do { \
 	completion_charpp = list; \
@@ -295,7 +343,7 @@ do { \
 	return completion_matches(text, complete_from_const);	\
 } while (0)
 
-#define COMPLETE_WITH_ATTR(relation, addon) \
+#define COMPLETE_WITH_ATTR_KW(relation, addon) \
 do { \
 	char   *_completion_schema; \
 	char   *_completion_table; \
@@ -322,6 +370,8 @@ do { \
 	return completion_matches(text, complete_from_query); \
 } while (0)
 
+#define COMPLETE_WITH_ATTR(query) COMPLETE_WITH_ATTR_KW((query), "")
+
 #define COMPLETE_WITH_ENUM_VALUE(type) \
 do { \
 	char   *_completion_schema; \
@@ -471,4 +521,17 @@ do { \
 	additional_kw_query(text, 12, s1, s2, s3, s4, s5, s6, s7,		\
 						s8, s9, s10, s11, s12, s13, s14, s15)
 
+#define COMPLETE_THING(p) \
+do { \
+	const pgsql_thing_t *ent = find_thing_entry(previous_words[-(p) - 1]);	\
+	if (ent) \
+	{ \
+		if (ent->query) \
+			COMPLETE_WITH_QUERY(ent->query); \
+		else if (ent->squery) \
+			COMPLETE_WITH_SCHEMA_QUERY(*ent->squery); \
+	} \
+	return NULL; \
+} while (0)
+
 #endif   /* TAB_COMPLETE_MACROS_H */
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index c14f9f3..ec5a700 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -790,6 +790,7 @@ static char **complete_from_variables(const char *text,
 					const char *prefix, const char *suffix, bool need_value);
 static char *complete_from_files(const char *text, int state);
 
+static int find_last_index_of(char *w, char **previous_words, int len);
 static char *pg_strdup_keyword_case(const char *s, const char *ref);
 static char *concatenate_strings(const char *s1, const char *s2);
 static char *additional_kw_query( const char *ref, int n, ...);
@@ -802,6 +803,7 @@ static char *get_guctype(const char *varname);
 
 static char **psql_completion_internal(const char *text, char **previous_words,
 										   int previous_words_count);
+static const pgsql_thing_t *find_thing_entry(char *word);
 #ifdef NOT_USED
 static char *quote_file_name(char *text, int match_type, char *quote_pointer);
 static char *dequote_file_name(char *text, char quote_char);
@@ -1010,6 +1012,9 @@ static char **
 psql_completion_internal(const char *text, char **previous_words,
 						 int previous_words_count)
 {
+	/* The number of prefixing words to be ignored */
+	int			head_shift = 0;
+
 	/* Known command-starting keywords. */
 	static const char *const sql_commands[] = {
 		"ABORT", "ALTER", "ANALYZE", "BEGIN", "CHECKPOINT", "CLOSE", "CLUSTER",
@@ -1060,10 +1065,24 @@ psql_completion_internal(const char *text, char **previous_words,
 	if (previous_words_count == 0)
 		COMPLETE_WITH_LIST(sql_commands);
 
+	/*
+	 * If this is in CREATE SCHEMA, seek to the last CREATE and regard it as
+	 * current command to complete.
+	 */
+	if (HeadMatches2("CREATE", "SCHEMA"))
+		SHIFT_TO_LAST1("CREATE|GRANT");
+
 /* CREATE */
 	/* complete with something you can create */
 	if (Matches1("CREATE"))
-		return completion_matches(text, create_command_generator);
+	{
+		if (head_shift == 0)
+			return completion_matches(text, create_command_generator);
+		else
+			/* schema_element allows some kinds of object */
+			COMPLETE_WITH_LIST5("TABLE", "VIEW", "INDEX", "SEQUENCE",
+								"TRIGGER");
+	}			
 
 /* DROP, but not DROP embedded in other commands */
 	/* complete with something you can drop */
@@ -1443,13 +1462,13 @@ psql_completion_internal(const char *text, char **previous_words,
 
 	/* ALTER TABLE xxx ALTER */
 	if (Matches4("ALTER", "TABLE", MatchAny, "ALTER"))
-		COMPLETE_WITH_ATTR(prev2_wd, ADDLIST2("COLUMN", "CONSTRAINT"));
+		COMPLETE_WITH_ATTR_KW(prev2_wd, ADDLIST2("COLUMN", "CONSTRAINT"));
 
 	/* ALTER TABLE xxx RENAME */
 	if (Matches4("ALTER", "TABLE", MatchAny, "RENAME"))
-		COMPLETE_WITH_ATTR(prev2_wd, ADDLIST3("COLUMN", "CONSTRAINT", "TO"));
+		COMPLETE_WITH_ATTR_KW(prev2_wd, ADDLIST3("COLUMN", "CONSTRAINT", "TO"));
 	if (Matches5("ALTER", "TABLE", MatchAny, "ALTER|RENAME", "COLUMN"))
-		COMPLETE_WITH_ATTR(prev3_wd, "");
+		COMPLETE_WITH_ATTR(prev3_wd);
 
 	/* ALTER TABLE xxx RENAME yyy */
 	if (Matches5("ALTER", "TABLE", MatchAny, "RENAME", MatchAnyExcept("CONSTRAINT|TO")))
@@ -1464,7 +1483,7 @@ psql_completion_internal(const char *text, char **previous_words,
 		COMPLETE_WITH_LIST2("COLUMN", "CONSTRAINT");
 	/* If we have ALTER TABLE <sth> DROP COLUMN, provide list of columns */
 	if (Matches5("ALTER", "TABLE", MatchAny, "DROP", "COLUMN"))
-		COMPLETE_WITH_ATTR(prev3_wd, "");
+		COMPLETE_WITH_ATTR(prev3_wd);
 
 	/*
 	 * If we have ALTER TABLE <sth> ALTER|DROP|RENAME|VALIDATE CONSTRAINT,
@@ -1475,26 +1494,25 @@ psql_completion_internal(const char *text, char **previous_words,
 		completion_info_charp = prev3_wd;
 		COMPLETE_WITH_QUERY(Query_for_constraint_of_table);
 	}
+	/* Remove COLUMN just after ALTER */
+	if (HeadMatches5("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN"))
+		COLLAPSE(5, 1);
 	/* ALTER TABLE ALTER [COLUMN] <foo> */
-	if (Matches6("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny) ||
-			 Matches5("ALTER", "TABLE", MatchAny, "ALTER", MatchAny))
+	if (Matches5("ALTER", "TABLE", MatchAny, "ALTER", MatchAny))
 		COMPLETE_WITH_LIST4("TYPE", "SET", "RESET", "DROP");
 	/* ALTER TABLE ALTER [COLUMN] <foo> SET */
-	if (Matches7("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET") ||
-			 Matches6("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET"))
+	if (Matches6("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET"))
 		COMPLETE_WITH_LIST5("(", "DEFAULT", "NOT NULL", "STATISTICS", "STORAGE");
 	/* ALTER TABLE ALTER [COLUMN] <foo> SET ( */
-	if (Matches8("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET", "(") ||
-		 Matches7("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET", "("))
+	if (Matches7("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET", "("))
 		COMPLETE_WITH_LIST2("n_distinct", "n_distinct_inherited");
 	/* ALTER TABLE ALTER [COLUMN] <foo> SET STORAGE */
-	if (Matches8("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET", "STORAGE") ||
-	Matches7("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET", "STORAGE"))
+	if (Matches7("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET", "STORAGE"))
 		COMPLETE_WITH_LIST4("PLAIN", "EXTERNAL", "EXTENDED", "MAIN");
 	/* ALTER TABLE ALTER [COLUMN] <foo> DROP */
-	if (Matches7("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "DROP") ||
-			 Matches8("ALTER", "TABLE", MatchAny, "TABLE", MatchAny, "ALTER", MatchAny, "DROP"))
+	if (Matches6("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "DROP"))
 		COMPLETE_WITH_LIST2("DEFAULT", "NOT NULL");
+
 	if (Matches4("ALTER", "TABLE", MatchAny, "CLUSTER"))
 		COMPLETE_WITH_CONST("ON");
 	if (Matches5("ALTER", "TABLE", MatchAny, "CLUSTER", "ON"))
@@ -1615,7 +1633,7 @@ psql_completion_internal(const char *text, char **previous_words,
 	 * of attributes
 	 */
 	if (Matches5("ALTER", "TYPE", MatchAny, "ALTER|DROP|RENAME", "ATTRIBUTE"))
-		COMPLETE_WITH_ATTR(prev3_wd, "");
+		COMPLETE_WITH_ATTR(prev3_wd);
 	/* ALTER TYPE ALTER ATTRIBUTE <foo> */
 	if (Matches6("ALTER", "TYPE", MatchAny, "ALTER", "ATTRIBUTE", MatchAny))
 		COMPLETE_WITH_CONST("TYPE");
@@ -1632,7 +1650,7 @@ psql_completion_internal(const char *text, char **previous_words,
 	/*
 	 * If we have ALTER TYPE <sth> RENAME VALUE, provide list of enum values
 	 */
-	else if (Matches5("ALTER", "TYPE", MatchAny, "RENAME", "VALUE"))
+	if (Matches5("ALTER", "TYPE", MatchAny, "RENAME", "VALUE"))
 		COMPLETE_WITH_ENUM_VALUE(prev3_wd);
 
 /* BEGIN */
@@ -1654,17 +1672,16 @@ psql_completion_internal(const char *text, char **previous_words,
 	if (Matches1("CLUSTER"))
 		COMPLETE_WITH_SCHEMA_QUERY_KW(Query_for_list_of_tm,
 									  ADDLIST1("VERBOSE"));
-	if (Matches2("CLUSTER", "VERBOSE"))
+	/* Remove VERBOSE for further completion */
+	if (HeadMatches2("CLUSTER", "VERBOSE"))
+		COLLAPSE(2, 1);
+	if (Matches1("CLUSTER"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm);
 	/* If we have CLUSTER <sth>, then add "USING" */
-	if (Matches2("CLUSTER", MatchAnyExcept("VERBOSE|ON")))
-		COMPLETE_WITH_CONST("USING");
-	/* If we have CLUSTER VERBOSE <sth>, then add "USING" */
-	if (Matches3("CLUSTER", "VERBOSE", MatchAny))
+	if (Matches2("CLUSTER", MatchAny))
 		COMPLETE_WITH_CONST("USING");
 	/* If we have CLUSTER <sth> USING, then add the index as well */
-	if (Matches3("CLUSTER", MatchAny, "USING") ||
-			 Matches4("CLUSTER", "VERBOSE", MatchAny, "USING"))
+	if (Matches3("CLUSTER", MatchAny, "USING"))
 	{
 		completion_info_charp = prev2_wd;
 		COMPLETE_WITH_QUERY(Query_for_index_of_table);
@@ -1705,9 +1722,8 @@ psql_completion_internal(const char *text, char **previous_words,
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews);
 	if (Matches4("COMMENT", "ON", "EVENT", "TRIGGER"))
 		COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
-	if (Matches4("COMMENT", "ON", MatchAny, MatchAnyExcept("IS")) ||
-		Matches5("COMMENT", "ON", MatchAny, MatchAny, MatchAnyExcept("IS")) ||
-			 Matches6("COMMENT", "ON", MatchAny, MatchAny, MatchAny, MatchAnyExcept("IS")))
+	if (HeadMatches3("COMMENT", "ON", MatchAny) &&
+		TailMatches1(MatchAnyExcept("IS")))
 		COMPLETE_WITH_CONST("IS");
 
 /* COPY */
@@ -1718,34 +1734,32 @@ psql_completion_internal(const char *text, char **previous_words,
 	 */
 	if (Matches1("COPY|\\copy"))
 		COMPLETE_WITH_SCHEMA_QUERY_KW(Query_for_list_of_tables,
-									  ADDLIST1("("));
+									  ADDLIST2("(", "BINARY"));
 	/* If we have COPY BINARY, complete with list of tables */
 	if (Matches2("COPY", "BINARY"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
+	/* Remove BINARY of COPY for further completion */
+	if (HeadMatches2("COPY", "BINARY"))
 	/* If we have COPY (, complete it with legal commands */
 	if (Matches2("COPY|\\copy", "("))
 		COMPLETE_WITH_LIST7("SELECT", "TABLE", "VALUES", "INSERT", "UPDATE", "DELETE", "WITH");
 	/* If we have COPY [BINARY] <sth>, complete it with "TO" or "FROM" */
-	if (Matches2("COPY|\\copy", MatchAny) ||
-			 Matches3("COPY", "BINARY", MatchAny))
+	if (Matches2("COPY|\\copy", MatchAny))
 		COMPLETE_WITH_LIST2("FROM", "TO");
 	/* If we have COPY [BINARY] <sth> FROM|TO, complete with filename */
-	if (Matches3("COPY|\\copy", MatchAny, "FROM|TO") ||
-			 Matches4("COPY", "BINARY", MatchAny, "FROM|TO"))
+	if (Matches3("COPY|\\copy", MatchAny, "FROM|TO"))
 	{
 		SET_COMP_CHARP("");
 		return completion_matches(text, complete_from_files);
 	}
 
 	/* Handle COPY [BINARY] <sth> FROM|TO filename */
-	if (Matches4("COPY|\\copy", MatchAny, "FROM|TO", MatchAny) ||
-			 Matches5("COPY", "BINARY", MatchAny, "FROM|TO", MatchAny))
+	if (Matches4("COPY|\\copy", MatchAny, "FROM|TO", MatchAny))
 		COMPLETE_WITH_LIST6("BINARY", "OIDS", "DELIMITER", "NULL", "CSV",
 							"ENCODING");
 
 	/* Handle COPY [BINARY] <sth> FROM|TO filename CSV */
-	if (Matches5("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "CSV") ||
-			 Matches6("COPY", "BINARY", MatchAny, "FROM|TO", MatchAny, "CSV"))
+	if (Matches5("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "CSV"))
 		COMPLETE_WITH_LIST5("HEADER", "QUOTE", "ESCAPE", "FORCE QUOTE",
 							"FORCE NOT NULL");
 
@@ -1792,57 +1806,47 @@ psql_completion_internal(const char *text, char **previous_words,
 	if (Matches5("CREATE", "FOREIGN", "DATA", "WRAPPER", MatchAny))
 		COMPLETE_WITH_LIST3("HANDLER", "VALIDATOR", "OPTIONS");
 
-	/* CREATE INDEX --- is allowed inside CREATE SCHEMA, so use TailMatches */
+	/* CREATE INDEX */
 	/* First off we complete CREATE UNIQUE with "INDEX" */
-	if (TailMatches2("CREATE", "UNIQUE"))
+	if (Matches2("CREATE", "UNIQUE"))
 		COMPLETE_WITH_CONST("INDEX");
 
-	/*
-	 * If we have CREATE|UNIQUE INDEX, then add "ON", "CONCURRENTLY", and
-	 * existing indexes
-	 */
-	if (TailMatches2("CREATE|UNIQUE", "INDEX"))
+	/* Remove UNIQUE for further completion */
+	if (HeadMatches3("CREATE", "UNIQUE", "INDEX"))
+		COLLAPSE(2, 1);
+	/* Complete with index names as category suggestion and possible keywords */
+	if (Matches2("CREATE", "INDEX"))
 		COMPLETE_WITH_SCHEMA_QUERY_KW(Query_for_list_of_indexes,
 									  ADDLIST2("ON", "CONCURRENTLY"));
-	/* Complete ... INDEX|CONCURRENTLY [<name>] ON with a list of tables  */
-	if (TailMatches3("INDEX|CONCURRENTLY", MatchAny, "ON") ||
-			 TailMatches2("INDEX|CONCURRENTLY", "ON"))
-		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm);
-
-	/*
-	 * Complete CREATE|UNIQUE INDEX CONCURRENTLY with "ON" and existing
-	 * indexes
-	 */
-	if (TailMatches3("CREATE|UNIQUE", "INDEX", "CONCURRENTLY"))
+	/* Remove CONCURRENTLY for further completion */
+	if (HeadMatches3("CREATE", "INDEX", "CONCURRENTLY"))
+		COLLAPSE(3, 1);
+	/* Complete with existing index names as word category suggestion */
+	if (Matches2("CREATE", "INDEX"))
 		COMPLETE_WITH_SCHEMA_QUERY_KW(Query_for_list_of_indexes,
 									  ADDLIST1("ON"));
-	/* Complete CREATE|UNIQUE INDEX [CONCURRENTLY] <sth> with "ON" */
-	if (TailMatches3("CREATE|UNIQUE", "INDEX", MatchAny) ||
-			 TailMatches4("CREATE|UNIQUE", "INDEX", "CONCURRENTLY", MatchAny))
+	/* Suggest ON just after index name */
+	if (Matches3("CREATE", "INDEX", MatchAnyExcept("ON")))
 		COMPLETE_WITH_CONST("ON");
-
-	/*
-	 * Complete INDEX <name> ON <table> with a list of table columns (which
-	 * should really be in parens)
-	 */
-	if (TailMatches4("INDEX", MatchAny, "ON", MatchAny) ||
-		TailMatches3("INDEX|CONCURRENTLY", "ON", MatchAny))
+	/* Specified index name does not matter ever after */
+	if (HeadMatches4("CREATE", "INDEX", MatchAny, "ON"))
+		COLLAPSE(3, 1);
+	/* Complete with table names only*/
+	if (Matches3("CREATE", "INDEX", "ON"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm);
+	/* MatchAny is table name */
+	if (Matches4("CREATE", "INDEX", "ON", MatchAny))
 		COMPLETE_WITH_LIST2("(", "USING");
-	if (TailMatches5("INDEX", MatchAny, "ON", MatchAny, "(") ||
-		TailMatches4("INDEX|CONCURRENTLY", "ON", MatchAny, "("))
-		COMPLETE_WITH_ATTR(prev2_wd, "");
-	/* same if you put in USING */
-	if (TailMatches5("ON", MatchAny, "USING", MatchAny, "("))
-		COMPLETE_WITH_ATTR(prev4_wd, "");
 	/* Complete USING with an index method */
-	if (TailMatches6("INDEX", MatchAny, MatchAny, "ON", MatchAny, "USING") ||
-			 TailMatches5("INDEX", MatchAny, "ON", MatchAny, "USING") ||
-			 TailMatches4("INDEX", "ON", MatchAny, "USING"))
+	if (Matches5("CREATE", "INDEX", "ON", MatchAny, "USING"))
 		COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
-	if (TailMatches4("ON", MatchAny, "USING", MatchAny) &&
-			 !TailMatches6("POLICY", MatchAny, MatchAny, MatchAny, MatchAny, MatchAny) &&
-			 !TailMatches4("FOR", MatchAny, MatchAny, MatchAny))
+	/*  Remove "Using xxx" for further completion*/
+	if (HeadMatches6("CREATE", "INDEX", "ON", MatchAny, "USING", MatchAny))
+		COLLAPSE(5, 2);
+	if (Matches4("CREATE", "INDEX", "ON", MatchAny))
 		COMPLETE_WITH_CONST("(");
+	if (Matches5("CREATE", "INDEX", "ON", MatchAny, "("))
+		COMPLETE_WITH_ATTR(prev2_wd);
 
 	/* CREATE POLICY */
 	/* Complete "CREATE POLICY <name> ON" */
@@ -1874,43 +1878,72 @@ psql_completion_internal(const char *text, char **previous_words,
 		COMPLETE_WITH_CONST("(");
 
 /* CREATE RULE */
-	/* Complete "CREATE RULE <sth>" with "AS ON" */
-	if (Matches3("CREATE", "RULE", MatchAny))
-		COMPLETE_WITH_CONST("AS ON");
-	/* Complete "CREATE RULE <sth> AS" with "ON" */
-	if (Matches4("CREATE", "RULE", MatchAny, "AS"))
-		COMPLETE_WITH_CONST("ON");
-	/* Complete "CREATE RULE <sth> AS ON" with SELECT|UPDATE|INSERT|DELETE */
-	if (Matches5("CREATE", "RULE", MatchAny, "AS", "ON"))
-		COMPLETE_WITH_LIST4("SELECT", "UPDATE", "INSERT", "DELETE");
-	/* Complete "AS ON SELECT|UPDATE|INSERT|DELETE" with a "TO" */
-	if (TailMatches3("AS", "ON", "SELECT|UPDATE|INSERT|DELETE"))
-		COMPLETE_WITH_CONST("TO");
-	/* Complete "AS ON <sth> TO" with a table name */
-	if (TailMatches4("AS", "ON", "SELECT|UPDATE|INSERT|DELETE", "TO"))
-		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
+	if (HeadMatches2("CREATE", "RULE"))
+	{
+		/* Complete "CREATE RULE <sth>" with "AS ON" */
+		if (Matches3("CREATE", "RULE", MatchAny))
+			COMPLETE_WITH_CONST("AS ON");
+		/* Complete "CREATE RULE <sth> AS" with "ON" */
+		if (Matches4("CREATE", "RULE", MatchAny, "AS"))
+			COMPLETE_WITH_CONST("ON");
+		/* Complete "CREATE RULE <sth> AS ON" with SELECT|UPDATE|INSERT|DELETE */
+		if (Matches5("CREATE", "RULE", MatchAny, "AS", "ON"))
+			COMPLETE_WITH_LIST4("SELECT", "UPDATE", "INSERT", "DELETE");
+		/* Complete "AS ON SELECT|UPDATE|INSERT|DELETE" with a "TO" */
+		if (TailMatches3("AS", "ON", "SELECT|UPDATE|INSERT|DELETE"))
+			COMPLETE_WITH_CONST("TO");
+		/* Complete "AS ON <sth> TO" with a table name */
+		if (TailMatches4("AS", "ON", "SELECT|UPDATE|INSERT|DELETE", "TO"))
+			COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
+		/* Complete "ON <sth> TO <name>" with DO INSTEAD*/
+		if (TailMatches4("ON", "SELECT|UPDATE|INSERT|DELETE", "TO", MatchAny))
+			COMPLETE_WITH_CONST("DO INSTEAD");
+		/* Complete DO INSTEAD with actions */
+		if (TailMatches2("DO", "INSTEAD"))
+			COMPLETE_WITH_LIST5("SELECT", "INSERT", "UPDATE", "DELETE", "NOTIFY");
+		/* Complete DO INSTEAD and further */
+		SHIFT_TO_LAST1("SELECT|INSERT|UPDATE|DELETE|NOTIFY");
+		if (HeadMatches1("SELECT|INSERT|UPDATE|DELETE|NOTIFY"))
+			return psql_completion_internal(text, previous_words, WORD_COUNT());
+		COMPLETE_WITH_CONST("");
+	}
+	
+	/* Complete "CREATE TEMP/TEMPORARY" with the possible temp objects */
+	if (TailMatches2("CREATE", "TEMP|TEMPORARY"))
+		COMPLETE_WITH_LIST3("SEQUENCE", "TABLE", "VIEW");
+	/* Remove TEMPORARY/TEMP for further completion */
+	if (HeadMatches3("CREATE", "TEMP|TEMPORARY", "TABLE|VIEW|SEQUENCE"))
+		COLLAPSE(2, 1);
 
-/* CREATE SEQUENCE --- is allowed inside CREATE SCHEMA, so use TailMatches */
-	if (TailMatches3("CREATE", "SEQUENCE", MatchAny) ||
-			 TailMatches4("CREATE", "TEMP|TEMPORARY", "SEQUENCE", MatchAny))
-		COMPLETE_WITH_LIST8("INCREMENT BY", "MINVALUE", "MAXVALUE", "NO", "CACHE",
-							"CYCLE", "OWNED BY", "START WITH");
-	if (TailMatches4("CREATE", "SEQUENCE", MatchAny, "NO") ||
-		TailMatches5("CREATE", "TEMP|TEMPORARY", "SEQUENCE", MatchAny, "NO"))
+	/* CREATE SEQUENCE */
+	if (Matches2("CREATE", "SEQUENCE"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences);
+	if (Matches3("CREATE", "SEQUENCE", MatchAny))
+		COMPLETE_WITH_LIST8("INCREMENT BY", "MINVALUE", "MAXVALUE", "NO",
+							"CACHE", "CYCLE", "OWNED BY", "START WITH");
+	if (TailMatches4("CREATE", "SEQUENCE", MatchAny, "NO"))
 		COMPLETE_WITH_LIST3("MINVALUE", "MAXVALUE", "CYCLE");
 
 /* CREATE SERVER <name> */
 	if (Matches3("CREATE", "SERVER", MatchAny))
 		COMPLETE_WITH_LIST3("TYPE", "VERSION", "FOREIGN DATA WRAPPER");
 
-/* CREATE TABLE --- is allowed inside CREATE SCHEMA, so use TailMatches */
-	/* Complete "CREATE TEMP/TEMPORARY" with the possible temp objects */
-	if (TailMatches2("CREATE", "TEMP|TEMPORARY"))
-		COMPLETE_WITH_LIST3("SEQUENCE", "TABLE", "VIEW");
+/* CREATE SCHEMA <name> */
+	if (Matches2("CREATE", "SCHEMA"))
+		COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
+
+/* CREATE TABLE  */
 	/* Complete "CREATE UNLOGGED" with TABLE or MATVIEW */
-	if (TailMatches2("CREATE", "UNLOGGED"))
+	if (Matches2("CREATE", "UNLOGGED"))
 		COMPLETE_WITH_LIST2("TABLE", "MATERIALIZED VIEW");
 
+	/* Remove UNLOGGED for further completion */
+	if (HeadMatches2("CREATE", "UNLOGGED"))
+		COLLAPSE(2, 1);
+	/* Complete CREATE TABLE with existing table names */
+	if (Matches2("CREATE", "TABLE"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
+
 /* CREATE TABLESPACE */
 	if (Matches3("CREATE", "TABLESPACE", MatchAny))
 		COMPLETE_WITH_LIST2("OWNER", "LOCATION");
@@ -1924,62 +1957,136 @@ psql_completion_internal(const char *text, char **previous_words,
 	if (Matches5("CREATE", "TEXT", "SEARCH", "CONFIGURATION", MatchAny))
 		COMPLETE_WITH_CONST("(");
 
-/* CREATE TRIGGER --- is allowed inside CREATE SCHEMA, so use TailMatches */
+/* CREATE TRIGGER */
+	if (HeadMatches3("CREATE", "CONSTRAINT", "TRIGGER"))
+		COLLAPSE(2, 1);
 	/* complete CREATE TRIGGER <name> with BEFORE,AFTER,INSTEAD OF */
-	if (TailMatches3("CREATE", "TRIGGER", MatchAny))
+	if (Matches3("CREATE", "TRIGGER", MatchAny))
 		COMPLETE_WITH_LIST3("BEFORE", "AFTER", "INSTEAD OF");
-	/* complete CREATE TRIGGER <name> BEFORE,AFTER with an event */
-	if (TailMatches4("CREATE", "TRIGGER", MatchAny, "BEFORE|AFTER"))
-		COMPLETE_WITH_LIST4("INSERT", "DELETE", "UPDATE", "TRUNCATE");
-	/* complete CREATE TRIGGER <name> INSTEAD OF with an event */
-	if (TailMatches5("CREATE", "TRIGGER", MatchAny, "INSTEAD", "OF"))
-		COMPLETE_WITH_LIST3("INSERT", "DELETE", "UPDATE");
-	/* complete CREATE TRIGGER <name> BEFORE,AFTER sth with OR,ON */
-	if (TailMatches5("CREATE", "TRIGGER", MatchAny, "BEFORE|AFTER", MatchAny) ||
-	  TailMatches6("CREATE", "TRIGGER", MatchAny, "INSTEAD", "OF", MatchAny))
-		COMPLETE_WITH_LIST2("ON", "OR");
+	{
+		bool instead_of = false;
+
+		/* complete CREATE TRIGGER <name> BEFORE,AFTER with an event */
+		if (Matches4("CREATE", "TRIGGER", MatchAny, "BEFORE|AFTER"))
+			COMPLETE_WITH_LIST4("INSERT", "DELETE", "UPDATE", "TRUNCATE");
+		/* complete CREATE TRIGGER <name> INSTEAD OF with an event */
+		if (Matches5("CREATE", "TRIGGER", MatchAny, "INSTEAD", "OF"))
+			COMPLETE_WITH_LIST3("INSERT", "DELETE", "UPDATE");
+
+		/* Repeatedly remove OR <event> */
+		while (HeadMatches7("CREATE", "TRIGGER", MatchAny, "BEFORE|AFTER",
+							MatchAny, "OR", "INSERT|DELETE|UPDATE|TRUNCATE"))
+			COLLAPSE(6, 2);
+		while (HeadMatches8("CREATE", "TRIGGER", MatchAny, "INSTEAD", "OF",
+							MatchAny, "OR", "INSERT|DELETE|UPDATE|TRUNCATE"))
+			COLLAPSE(7, 2);
+
+		/* Remove BEFORE|AFTER|INSTEAD OF <event> */
+		if (HeadMatches5("CREATE", "TRIGGER", MatchAny, "BEFORE|AFTER",
+						 MatchAny))
+			COLLAPSE(4, 2);
+		if (HeadMatches6("CREATE", "TRIGGER", MatchAny, "INSTEAD", "OF",
+						 MatchAny))
+		{
+			COLLAPSE(4, 3);
+			instead_of = true;
+		}
+		/* complete CREATE TRIGGER <name> BEFORE,AFTER sth with OR,ON */
+		if (Matches3("CREATE", "TRIGGER", MatchAny))
+			COMPLETE_WITH_LIST2("ON", "OR");
+		if (Matches4("CREATE", "TRIGGER", MatchAny, "OR"))
+		{
+			if (instead_of)
+				COMPLETE_WITH_LIST3("INSERT", "DELETE", "UPDATE");
+			else
+				COMPLETE_WITH_LIST4("INSERT", "DELETE", "UPDATE", "TRUNCATE");
+		}
 
-	/*
-	 * complete CREATE TRIGGER <name> BEFORE,AFTER event ON with a list of
-	 * tables
-	 */
-	if (TailMatches6("CREATE", "TRIGGER", MatchAny, "BEFORE|AFTER", MatchAny, "ON"))
-		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
-	/* complete CREATE TRIGGER ... INSTEAD OF event ON with a list of views */
-	if (TailMatches7("CREATE", "TRIGGER", MatchAny, "INSTEAD", "OF", MatchAny, "ON"))
-		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views);
-	if (HeadMatches2("CREATE", "TRIGGER") && TailMatches2("ON", MatchAny))
-		COMPLETE_WITH_LIST7("NOT DEFERRABLE", "DEFERRABLE", "INITIALLY",
-							"REFERENCING", "FOR", "WHEN (", "EXECUTE PROCEDURE");
-	if (HeadMatches2("CREATE", "TRIGGER") &&
-			 (TailMatches1("DEFERRABLE") || TailMatches2("INITIALLY", "IMMEDIATE|DEFERRED")))
-		COMPLETE_WITH_LIST4("REFERENCING", "FOR", "WHEN (", "EXECUTE PROCEDURE");
-	if (HeadMatches2("CREATE", "TRIGGER") && TailMatches1("REFERENCING"))
-		COMPLETE_WITH_LIST2("OLD TABLE", "NEW TABLE");
-	if (HeadMatches2("CREATE", "TRIGGER") && TailMatches2("OLD|NEW", "TABLE"))
-		COMPLETE_WITH_CONST("AS");
-	if (HeadMatches2("CREATE", "TRIGGER") &&
-		(TailMatches5("REFERENCING", "OLD", "TABLE", "AS", MatchAny) ||
-		 TailMatches4("REFERENCING", "OLD", "TABLE", MatchAny)))
-		COMPLETE_WITH_LIST4("NEW TABLE", "FOR", "WHEN (", "EXECUTE PROCEDURE");
-	if (HeadMatches2("CREATE", "TRIGGER") &&
-		(TailMatches5("REFERENCING", "NEW", "TABLE", "AS", MatchAny) ||
-		 TailMatches4("REFERENCING", "NEW", "TABLE", MatchAny)))
-		COMPLETE_WITH_LIST4("OLD TABLE", "FOR", "WHEN (", "EXECUTE PROCEDURE");
-	if (HeadMatches2("CREATE", "TRIGGER") &&
-		(TailMatches9("REFERENCING", "OLD|NEW", "TABLE", "AS", MatchAny, "OLD|NEW", "TABLE", "AS", MatchAny) ||
-		 TailMatches8("REFERENCING", "OLD|NEW", "TABLE", MatchAny, "OLD|NEW", "TABLE", "AS", MatchAny) ||
-		 TailMatches8("REFERENCING", "OLD|NEW", "TABLE", "AS", MatchAny, "OLD|NEW", "TABLE", MatchAny) ||
-		 TailMatches7("REFERENCING", "OLD|NEW", "TABLE", MatchAny, "OLD|NEW", "TABLE", MatchAny)))
-		COMPLETE_WITH_LIST3("FOR", "WHEN (", "EXECUTE PROCEDURE");
-	if (HeadMatches2("CREATE", "TRIGGER") && TailMatches1("FOR"))
-		COMPLETE_WITH_LIST3("EACH", "ROW", "STATEMENT");
-	if (HeadMatches2("CREATE", "TRIGGER") && TailMatches2("FOR", "EACH"))
-		COMPLETE_WITH_LIST2("ROW", "STATEMENT");
-	if (HeadMatches2("CREATE", "TRIGGER") &&
-			 (TailMatches3("FOR", "EACH", "ROW|STATEMENT") ||
-			  TailMatches2("FOR", "ROW|STATEMENT")))
-		COMPLETE_WITH_LIST2("WHEN (", "EXECUTE PROCEDURE");
+		/*
+		 * complete CREATE TRIGGER <name> BEFORE,AFTER event ON with a list of
+		 * tables
+		 * complete CREATE TRIGGER ... INSTEAD OF event ON with a list of
+		 * views
+		 */
+		if (Matches4("CREATE", "TRIGGER", MatchAny, "ON"))
+		{
+			if (instead_of)
+				COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views);
+			else
+				COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
+		}
+
+		/*
+		 * word_list became too long for matching macors, shift to just
+		 * after CREATE TRIGGER name ... ON table_name and go on.
+		 */
+		if (HeadMatches5("CREATE", "TRIGGER", MatchAny, "ON", MatchAny))
+		{
+			HEAD_SHIFT(5);
+
+			if (WORD_COUNT() == 0)
+				COMPLETE_WITH_LIST7("NOT DEFERRABLE", "DEFERRABLE", "INITIALLY",
+									"REFERENCING", "FOR", "WHEN (",
+									"EXECUTE PROCEDURE");
+			if (Matches1("INITIALLY"))
+				COMPLETE_WITH_LIST2("IMMEDIATE", "DEFERRED");
+
+			/* Remove last keyword that doesn't matter in further completion */
+			if (HeadMatches1("DEFERRABLE"))
+				COLLAPSE(1, 1);
+			if (HeadMatches2("INITIALLY", "IMMEDIATE|DEFERRED") ||
+				HeadMatches2("NOT", "DEFERRABLE"))
+				COLLAPSE(1, 2);
+
+			if (WORD_COUNT() == 0)
+				COMPLETE_WITH_LIST4("REFERENCING", "FOR", "WHEN (",
+									"EXECUTE PROCEDURE");
+			if (Matches1("REFERENCING"))
+				COMPLETE_WITH_LIST2("OLD TABLE", "NEW TABLE");
+			if (Matches3("REFERENCING", "OLD|NEW", "TABLE"))
+				COMPLETE_WITH_CONST("AS");
+
+			/*  Remove AS if exists*/
+			if (HeadMatches4("REFERENCING", "OLD|NEW", "TABLE", "AS"))
+				COLLAPSE(4, 1);
+			if (Matches4("REFERENCING", "OLD", "TABLE", MatchAny))
+				COMPLETE_WITH_LIST4("NEW TABLE", "FOR", "WHEN (",
+									"EXECUTE PROCEDURE");
+			if (Matches4("REFERENCING", "NEW", "TABLE", MatchAny))
+				COMPLETE_WITH_LIST4("OLD TABLE", "FOR", "WHEN (",
+									"EXECUTE PROCEDURE");
+			if (Matches6("REFERENCING", "OLD|NEW", "TABLE", MatchAny,
+						 "OLD|NEW", "TABLE"))
+				COMPLETE_WITH_CONST("AS");
+			if (HeadMatches8("REFERENCING", "OLD|NEW", "TABLE", MatchAny,
+							 "OLD|NEW", "TABLE", "AS", MatchAny))
+				COLLAPSE(1, 8);
+			if (HeadMatches7("REFERENCING", "OLD|NEW", "TABLE", MatchAny,
+							 "OLD|NEW", "TABLE", MatchAny))
+				COLLAPSE(1, 7);
+			if (HeadMatches4("REFERENCING", "OLD|NEW", "TABLE", MatchAny))
+				COLLAPSE(1, 4);
+			/* REFERENCING close has vanished here */
+
+			if (WORD_COUNT() == 0)
+				COMPLETE_WITH_LIST3("FOR", "WHEN (", "EXECUTE PROCEDURE");
+			if (Matches1("FOR"))
+				COMPLETE_WITH_LIST3("EACH", "ROW", "STATEMENT");
+			/* Remove EACH */
+			if (HeadMatches2("FOR", "EACH"))
+				COLLAPSE(2, 1);
+			if (Matches1("FOR"))
+				COMPLETE_WITH_LIST2("ROW", "STATEMENT");
+			if (HeadMatches2("FOR", "ROW|STATEMENT"))
+				COLLAPSE(1, 2);
+			/* FOR close has vanished here */
+			if (WORD_COUNT() == 0)
+				COMPLETE_WITH_LIST2("WHEN (", "EXECUTE PROCEDURE");
+
+			/* Continue matching on the whole command line */
+			HEAD_SHIFT(-5);
+		}
+	}
 	/* complete CREATE TRIGGER ... EXECUTE with PROCEDURE */
 	if (HeadMatches2("CREATE", "TRIGGER") && TailMatches1("EXECUTE"))
 		COMPLETE_WITH_CONST("PROCEDURE");
@@ -2026,12 +2133,12 @@ psql_completion_internal(const char *text, char **previous_words,
 	if (Matches4("CREATE", "ROLE|USER|GROUP", MatchAny, "IN"))
 		COMPLETE_WITH_LIST2("GROUP", "ROLE");
 
-/* CREATE VIEW --- is allowed inside CREATE SCHEMA, so use TailMatches */
+/* CREATE VIEW */
 	/* Complete CREATE VIEW <name> with AS */
-	if (TailMatches3("CREATE", "VIEW", MatchAny))
+	if (Matches3("CREATE", "VIEW", MatchAny))
 		COMPLETE_WITH_CONST("AS");
 	/* Complete "CREATE VIEW <sth> AS with "SELECT" */
-	if (TailMatches4("CREATE", "VIEW", MatchAny, "AS"))
+	if (Matches4("CREATE", "VIEW", MatchAny, "AS"))
 		COMPLETE_WITH_CONST("SELECT");
 
 /* CREATE MATERIALIZED VIEW */
@@ -2061,15 +2168,15 @@ psql_completion_internal(const char *text, char **previous_words,
 	if (HeadMatches1("DECLARE") && TailMatches1("CURSOR"))
 		COMPLETE_WITH_LIST3("WITH HOLD", "WITHOUT HOLD", "FOR");
 
-/* DELETE --- can be inside EXPLAIN, RULE, etc */
+/* DELETE */
 	/* ... despite which, only complete DELETE with FROM at start of line */
 	if (Matches1("DELETE"))
 		COMPLETE_WITH_CONST("FROM");
 	/* Complete DELETE FROM with a list of tables */
-	if (TailMatches2("DELETE", "FROM"))
+	if (Matches2("DELETE", "FROM"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_updatables);
 	/* Complete DELETE FROM <table> */
-	if (TailMatches3("DELETE", "FROM", MatchAny))
+	if (Matches3("DELETE", "FROM", MatchAny))
 		COMPLETE_WITH_LIST2("USING", "WHERE");
 	/* XXX: implement tab completion for DELETE ... USING */
 
@@ -2107,16 +2214,17 @@ psql_completion_internal(const char *text, char **previous_words,
 	if (Matches2("DROP", "INDEX"))
 		COMPLETE_WITH_SCHEMA_QUERY_KW(Query_for_list_of_indexes,
 									  ADDLIST1("CONCURRENTLY"));
-	if (Matches3("DROP", "INDEX", "CONCURRENTLY"))
-		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes);
+	if (HeadMatches3("DROP", "INDEX", "CONCURRENTLY"))
+		COLLAPSE(3, 1);
 	if (Matches3("DROP", "INDEX", MatchAny))
 		COMPLETE_WITH_LIST2("CASCADE", "RESTRICT");
-	if (Matches4("DROP", "INDEX", "CONCURRENTLY", MatchAny))
-		COMPLETE_WITH_LIST2("CASCADE", "RESTRICT");
 
 	/* DROP MATERIALIZED VIEW */
 	if (Matches2("DROP", "MATERIALIZED"))
 		COMPLETE_WITH_CONST("VIEW");
+
+	/* DROP VIEW is suggested as a general thing */
+
 	if (Matches3("DROP", "MATERIALIZED", "VIEW"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews);
 
@@ -2188,13 +2296,23 @@ psql_completion_internal(const char *text, char **previous_words,
 	if (Matches1("EXPLAIN"))
 		COMPLETE_WITH_LIST7("SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE",
 							"ANALYZE", "VERBOSE");
-	if (Matches2("EXPLAIN", "ANALYZE"))
+	if (HeadMatches2("EXPLAIN", "ANALYZE"))
+		COLLAPSE(2, 1);
+	if (Matches1("EXPLAIN"))
 		COMPLETE_WITH_LIST6("SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE",
 							"VERBOSE");
-	if (Matches2("EXPLAIN", "VERBOSE") ||
-			 Matches3("EXPLAIN", "ANALYZE", "VERBOSE"))
+	if (HeadMatches2("EXPLAIN", "VERBOSE"))
+		COLLAPSE(2, 1);
+	if (Matches1("EXPLAIN"))
 		COMPLETE_WITH_LIST5("SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE");
 
+	/* complete on individual syntaxes here after */
+	if (Matches2("EXPLAIN", "SELECT|INSERT|DELETE|UPDATE|DECLARE"))
+	{
+		COLLAPSE(1, 1);
+		return psql_completion_internal(text, previous_words, WORD_COUNT());
+	}
+
 /* FETCH && MOVE */
 	/* Complete FETCH with one of FORWARD, BACKWARD, RELATIVE */
 	if (Matches1("FETCH|MOVE"))
@@ -2230,9 +2348,9 @@ psql_completion_internal(const char *text, char **previous_words,
 	if (TailMatches2("FOREIGN", "SERVER"))
 		COMPLETE_WITH_QUERY(Query_for_list_of_servers);
 
-/* GRANT && REVOKE --- is allowed inside CREATE SCHEMA, so use TailMatches */
+/* GRANT && REVOKE */
 	/* Complete GRANT/REVOKE with a list of roles and privileges */
-	if (TailMatches1("GRANT|REVOKE"))
+	if (Matches1("GRANT|REVOKE"))
 		COMPLETE_WITH_QUERY_KW(Query_for_list_of_roles,
 			ADDLIST13("SELECT", "INSERT", "UPDATE", "DELETE", "TRUNCATE",
 					  "REFERENCES", "TRIGGER", "CREATE", "CONNECT", "TEMPORARY",
@@ -2242,13 +2360,13 @@ psql_completion_internal(const char *text, char **previous_words,
 	 * Complete GRANT/REVOKE <privilege> with "ON", GRANT/REVOKE <role> with
 	 * TO/FROM
 	 */
-	if (TailMatches2("GRANT|REVOKE", MatchAny))
+	if (Matches2("GRANT|REVOKE", MatchAny))
 	{
 		if (TailMatches1("SELECT|INSERT|UPDATE|DELETE|TRUNCATE|REFERENCES|TRIGGER|CREATE|CONNECT|TEMPORARY|TEMP|EXECUTE|USAGE|ALL"))
 			COMPLETE_WITH_CONST("ON");
-		if (TailMatches2("GRANT", MatchAny))
+		if (HeadMatches1("GRANT"))		/* GRANT roles */
 			COMPLETE_WITH_CONST("TO");
-		else
+		else							/* REVOKE roles */
 			COMPLETE_WITH_CONST("FROM");
 	}
 
@@ -2263,7 +2381,7 @@ psql_completion_internal(const char *text, char **previous_words,
 	 * here will only work if the privilege list contains exactly one
 	 * privilege.
 	 */
-	if (TailMatches3("GRANT|REVOKE", MatchAny, "ON"))
+	if (Matches3("GRANT|REVOKE", MatchAny, "ON"))
 		COMPLETE_WITH_SCHEMA_QUERY_KW(Query_for_list_of_tsvmf,
 			   ADDLIST15("ALL FUNCTIONS IN SCHEMA",
 						 "ALL SEQUENCES IN SCHEMA",
@@ -2281,11 +2399,11 @@ psql_completion_internal(const char *text, char **previous_words,
 						 "TABLESPACE",
 						 "TYPE"));
 
-	if (TailMatches4("GRANT|REVOKE", MatchAny, "ON", "ALL"))
+	if (Matches4("GRANT|REVOKE", MatchAny, "ON", "ALL"))
 		COMPLETE_WITH_LIST3("FUNCTIONS IN SCHEMA", "SEQUENCES IN SCHEMA",
 							"TABLES IN SCHEMA");
 
-	if (TailMatches4("GRANT|REVOKE", MatchAny, "ON", "FOREIGN"))
+	if (Matches4("GRANT|REVOKE", MatchAny, "ON", "FOREIGN"))
 		COMPLETE_WITH_LIST2("DATA WRAPPER", "SERVER");
 
 	/*
@@ -2294,27 +2412,11 @@ psql_completion_internal(const char *text, char **previous_words,
 	 *
 	 * Complete "GRANT/REVOKE * ON *" with "TO/FROM".
 	 */
-	if (TailMatches4("GRANT|REVOKE", MatchAny, "ON", MatchAny))
+	if (Matches4("GRANT|REVOKE", MatchAny, "ON", MatchAny))
 	{
-		if (TailMatches1("DATABASE"))
-			COMPLETE_WITH_QUERY(Query_for_list_of_databases);
-		if (TailMatches1("DOMAIN"))
-			COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_domains);
-		if (TailMatches1("FUNCTION"))
-			COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions);
-		if (TailMatches1("LANGUAGE"))
-			COMPLETE_WITH_QUERY(Query_for_list_of_languages);
-		if (TailMatches1("SCHEMA"))
-			COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
-		if (TailMatches1("SEQUENCE"))
-			COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences);
-		if (TailMatches1("TABLE"))
-			COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvmf);
-		if (TailMatches1("TABLESPACE"))
-			COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
-		if (TailMatches1("TYPE"))
-			COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
-		if (TailMatches4("GRANT", MatchAny, MatchAny, MatchAny))
+		if (TailMatches1("DATABASE|DOMAIN|FUNCTION|LANGUAGE|SCHEMA|SEQUENCE|TABLE|TABLESPACE|TYPE"))
+			COMPLETE_THING(-1);
+		if (HeadMatches1("GRANT"))
 			COMPLETE_WITH_CONST("TO");
 		else
 			COMPLETE_WITH_CONST("FROM");
@@ -2335,27 +2437,13 @@ psql_completion_internal(const char *text, char **previous_words,
 		COMPLETE_WITH_CONST("FROM");
 
 	/* Complete "GRANT/REVOKE * ON ALL * IN SCHEMA *" with TO/FROM */
-	if (TailMatches8("GRANT|REVOKE", MatchAny, "ON", "ALL", MatchAny, "IN", "SCHEMA", MatchAny))
-	{
-		if (TailMatches8("GRANT", MatchAny, MatchAny, MatchAny, MatchAny, MatchAny, MatchAny, MatchAny))
-			COMPLETE_WITH_CONST("TO");
-		else
-			COMPLETE_WITH_CONST("FROM");
-	}
-
 	/* Complete "GRANT/REVOKE * ON FOREIGN DATA WRAPPER *" with TO/FROM */
-	if (TailMatches7("GRANT|REVOKE", MatchAny, "ON", "FOREIGN", "DATA", "WRAPPER", MatchAny))
-	{
-		if (TailMatches7("GRANT", MatchAny, MatchAny, MatchAny, MatchAny, MatchAny, MatchAny))
-			COMPLETE_WITH_CONST("TO");
-		else
-			COMPLETE_WITH_CONST("FROM");
-	}
-
 	/* Complete "GRANT/REVOKE * ON FOREIGN SERVER *" with TO/FROM */
-	if (TailMatches6("GRANT|REVOKE", MatchAny, "ON", "FOREIGN", "SERVER", MatchAny))
+	if (Matches8("GRANT|REVOKE", MatchAny, "ON", "ALL", MatchAny, "IN", "SCHEMA", MatchAny) ||
+		Matches7("GRANT|REVOKE", MatchAny, "ON", "FOREIGN", "DATA", "WRAPPER", MatchAny) ||
+		Matches6("GRANT|REVOKE", MatchAny, "ON", "FOREIGN", "SERVER", MatchAny))
 	{
-		if (TailMatches6("GRANT", MatchAny, MatchAny, MatchAny, MatchAny, MatchAny))
+		if (HeadMatches1("GRANT"))
 			COMPLETE_WITH_CONST("TO");
 		else
 			COMPLETE_WITH_CONST("FROM");
@@ -2371,29 +2459,29 @@ psql_completion_internal(const char *text, char **previous_words,
 	if (Matches2("IMPORT", "FOREIGN"))
 		COMPLETE_WITH_CONST("SCHEMA");
 
-/* INSERT --- can be inside EXPLAIN, RULE, etc */
+/* INSERT */
 	/* Complete INSERT with "INTO" */
-	if (TailMatches1("INSERT"))
+	if (Matches1("INSERT"))
 		COMPLETE_WITH_CONST("INTO");
 	/* Complete INSERT INTO with table names */
-	if (TailMatches2("INSERT", "INTO"))
+	if (Matches2("INSERT", "INTO"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_updatables);
 	/* Complete "INSERT INTO <table> (" with attribute names */
-	if (TailMatches4("INSERT", "INTO", MatchAny, "("))
-		COMPLETE_WITH_ATTR(prev2_wd, "");
+	if (Matches4("INSERT", "INTO", MatchAny, "("))
+		COMPLETE_WITH_ATTR(prev2_wd);
 
 	/*
 	 * Complete INSERT INTO <table> with "(" or "VALUES" or "SELECT" or
 	 * "TABLE" or "DEFAULT VALUES"
 	 */
-	if (TailMatches3("INSERT", "INTO", MatchAny))
+	if (Matches3("INSERT", "INTO", MatchAny))
 		COMPLETE_WITH_LIST5("(", "DEFAULT VALUES", "SELECT", "TABLE", "VALUES");
 
 	/*
 	 * Complete INSERT INTO <table> (attribs) with "VALUES" or "SELECT" or
 	 * "TABLE"
 	 */
-	if (TailMatches4("INSERT", "INTO", MatchAny, MatchAny) &&
+	if (Matches4("INSERT", "INTO", MatchAny, MatchAny) &&
 			 ends_with(prev_wd, ')'))
 		COMPLETE_WITH_LIST3("SELECT", "TABLE", "VALUES");
 
@@ -2411,14 +2499,18 @@ psql_completion_internal(const char *text, char **previous_words,
 
 	/* For the following, handle the case of a single table only for now */
 
+	/* Remove TABLE and ONLY from LOCK */
+	if (HeadMatches3("LOCK", "TABLE", MatchAny))
+		COLLAPSE(2, 1);
+	if (HeadMatches3("LOCK", "ONLY", MatchAny))
+		COLLAPSE(2, 1);
+
 	/* Complete LOCK [TABLE] <table> with "IN" */
-	if (Matches2("LOCK", MatchAnyExcept("TABLE")) ||
-			 Matches3("LOCK", "TABLE", MatchAny))
+	if (Matches2("LOCK", MatchAnyExcept("TABLE")))
 		COMPLETE_WITH_CONST("IN");
 
 	/* Complete LOCK [TABLE] <table> IN with a lock mode */
-	if (Matches3("LOCK", MatchAny, "IN") ||
-			 Matches4("LOCK", "TABLE", MatchAny, "IN"))
+	if (Matches3("LOCK", MatchAny, "IN"))
 		COMPLETE_WITH_LIST8("ACCESS SHARE MODE",
 							"ROW SHARE MODE", "ROW EXCLUSIVE MODE",
 							"SHARE UPDATE EXCLUSIVE MODE", "SHARE MODE",
@@ -2426,18 +2518,15 @@ psql_completion_internal(const char *text, char **previous_words,
 							"EXCLUSIVE MODE", "ACCESS EXCLUSIVE MODE");
 
 	/* Complete LOCK [TABLE] <table> IN ACCESS|ROW with rest of lock mode */
-	else if (Matches4("LOCK", MatchAny, "IN", "ACCESS|ROW") ||
-			 Matches5("LOCK", "TABLE", MatchAny, "IN", "ACCESS|ROW"))
+	if (Matches4("LOCK", MatchAny, "IN", "ACCESS|ROW"))
 		COMPLETE_WITH_LIST2("EXCLUSIVE MODE", "SHARE MODE");
-
+	
 	/* Complete LOCK [TABLE] <table> IN SHARE with rest of lock mode */
-	else if (Matches4("LOCK", MatchAny, "IN", "SHARE") ||
-			 Matches5("LOCK", "TABLE", MatchAny, "IN", "SHARE"))
+	if (Matches4("LOCK", MatchAny, "IN", "SHARE"))
 		COMPLETE_WITH_LIST3("MODE", "ROW EXCLUSIVE MODE",
 							"UPDATE EXCLUSIVE MODE");
 
-/* NOTIFY --- can be inside EXPLAIN, RULE, etc */
-	if (TailMatches1("NOTIFY"))
+	if (Matches1("NOTIFY"))
 		COMPLETE_WITH_QUERY("SELECT pg_catalog.quote_ident(channel) FROM pg_catalog.pg_listening_channels() AS channel WHERE substring(pg_catalog.quote_ident(channel),1,%d)='%s'");
 
 /* OPTIONS */
@@ -2452,11 +2541,18 @@ psql_completion_internal(const char *text, char **previous_words,
 	if (TailMatches3("FROM", MatchAny, "ORDER"))
 		COMPLETE_WITH_CONST("BY");
 	if (TailMatches4("FROM", MatchAny, "ORDER", "BY"))
-		COMPLETE_WITH_ATTR(prev3_wd, "");
+		COMPLETE_WITH_ATTR(prev3_wd);
 
 /* PREPARE xx AS */
-	if (Matches3("PREPARE", MatchAny, "AS"))
-		COMPLETE_WITH_LIST4("SELECT", "UPDATE", "INSERT", "DELETE FROM");
+	if (HeadMatches1("PREPARE"))
+	{
+		if (Matches3("PREPARE", MatchAny, "AS"))
+			COMPLETE_WITH_LIST4("SELECT", "UPDATE", "INSERT", "DELETE FROM");
+
+		/* Complete for indivisual command */
+		SHIFT_TO_LAST1("SELECT|UPDATE|INSERT|DELETE");
+		return psql_completion_internal(text, previous_words, WORD_COUNT());
+	}
 
 /*
  * PREPARE TRANSACTION is missing on purpose. It's intended for transaction
@@ -2517,8 +2613,9 @@ psql_completion_internal(const char *text, char **previous_words,
 		COMPLETE_WITH_LIST2("ON", "FOR");
 	if (Matches4("SECURITY", "LABEL", "FOR", MatchAny))
 		COMPLETE_WITH_CONST("ON");
-	if (Matches3("SECURITY", "LABEL", "ON") ||
-			 Matches5("SECURITY", "LABEL", "FOR", MatchAny, "ON"))
+	if (HeadMatches4("SECURITY", "LABEL", "FOR", MatchAny))
+		COLLAPSE(3, 2);
+	if (Matches3("SECURITY", "LABEL", "ON"))
 	{
 		static const char *const list_SECURITY_LABEL[] =
 		{"TABLE", "COLUMN", "AGGREGATE", "DATABASE", "DOMAIN",
@@ -2546,35 +2643,42 @@ psql_completion_internal(const char *text, char **previous_words,
 	/* Complete "SET TRANSACTION" */
 	if (Matches2("SET", "TRANSACTION"))
 		COMPLETE_WITH_LIST5("SNAPSHOT", "ISOLATION LEVEL", "READ", "DEFERRABLE", "NOT DEFERRABLE");
-	if (Matches2("BEGIN|START", "TRANSACTION") ||
-		Matches2("BEGIN", "WORK") ||
-		Matches1("BEGIN") ||
-		Matches5("SET", "SESSION", "CHARACTERISTICS", "AS", "TRANSACTION"))
-		COMPLETE_WITH_LIST4("ISOLATION LEVEL", "READ", "DEFERRABLE", "NOT DEFERRABLE");
-	if (Matches3("SET|BEGIN|START", "TRANSACTION|WORK", "NOT") ||
-		Matches2("BEGIN", "NOT") ||
-		Matches6("SET", "SESSION", "CHARACTERISTICS", "AS", "TRANSACTION", "NOT"))
-		COMPLETE_WITH_CONST("DEFERRABLE");
-	if (Matches3("SET|BEGIN|START", "TRANSACTION|WORK", "ISOLATION") ||
-		Matches2("BEGIN", "ISOLATION") ||
-		Matches6("SET", "SESSION", "CHARACTERISTICS", "AS", "TRANSACTION", "ISOLATION"))
-		COMPLETE_WITH_CONST("LEVEL");
-	if (Matches4("SET|BEGIN|START", "TRANSACTION|WORK", "ISOLATION", "LEVEL") ||
-		Matches3("BEGIN", "ISOLATION", "LEVEL") ||
-		Matches7("SET", "SESSION", "CHARACTERISTICS", "AS", "TRANSACTION", "ISOLATION", "LEVEL"))
-		COMPLETE_WITH_LIST3("READ", "REPEATABLE READ", "SERIALIZABLE");
-	if (Matches5("SET|BEGIN|START", "TRANSACTION|WORK", "ISOLATION", "LEVEL", "READ") ||
-		Matches4("BEGIN", "ISOLATION", "LEVEL", "READ") ||
-		Matches8("SET", "SESSION", "CHARACTERISTICS", "AS", "TRANSACTION", "ISOLATION", "LEVEL", "READ"))
-		COMPLETE_WITH_LIST2("UNCOMMITTED", "COMMITTED");
-	if (Matches5("SET|BEGIN|START", "TRANSACTION|WORK", "ISOLATION", "LEVEL", "REPEATABLE") ||
-		Matches4("BEGIN", "ISOLATION", "LEVEL", "REPEATABLE") ||
-		Matches8("SET", "SESSION", "CHARACTERISTICS", "AS", "TRANSACTION", "ISOLATION", "LEVEL", "REPEATABLE"))
-		COMPLETE_WITH_CONST("READ");
-	if (Matches3("SET|BEGIN|START", "TRANSACTION|WORK", "READ") ||
-		Matches2("BEGIN", "READ") ||
-		Matches6("SET", "SESSION", "CHARACTERISTICS", "AS", "TRANSACTION", "READ"))
-		COMPLETE_WITH_LIST2("ONLY", "WRITE");
+	if (HeadMatches2("BEGIN", "WORK|TRANSACTION"))
+		COLLAPSE(2, 1);
+	{
+		int shift = 0;
+
+		if (HeadMatches2("START", "TRANSACTION"))
+			shift = 2;
+		if (HeadMatches1("BEGIN"))
+			shift = 1;
+		if (HeadMatches5("SET", "SESSION", "CHARACTERISTICS", "AS",
+						 "TRANSACTION"))
+			shift = 5;
+
+		if (shift > 0)
+		{
+			/* complete with transaction mode */
+			HEAD_SHIFT(shift);
+
+			if (WORD_COUNT() == 0)
+				COMPLETE_WITH_LIST4("ISOLATION LEVEL", "READ", "DEFERRABLE",
+									"NOT DEFERRABLE");
+			if (Matches1("NOT"))
+				COMPLETE_WITH_CONST("DEFERRABLE");
+			if (Matches1("ISOLATION"))
+				COMPLETE_WITH_CONST("LEVEL");
+			if (Matches2("ISOLATION", "LEVEL"))
+				COMPLETE_WITH_LIST3("READ", "REPEATABLE READ", "SERIALIZABLE");
+			if (Matches3("ISOLATION", "LEVEL", "REPEATABLE"))
+				COMPLETE_WITH_CONST("READ");
+			if (Matches3("ISOLATION", "LEVEL", "READ"))
+				COMPLETE_WITH_LIST2("UNCOMMITTED", "COMMITTED");
+			if (Matches1("READ"))
+				COMPLETE_WITH_LIST2("ONLY", "WRITE");
+			COMPLETE_WITH_CONST("");
+		}
+	}
 	/* SET CONSTRAINTS */
 	if (Matches2("SET", "CONSTRAINTS"))
 		COMPLETE_WITH_SCHEMA_QUERY_KW(Query_for_list_of_constraints_with_schema,
@@ -2671,18 +2775,18 @@ psql_completion_internal(const char *text, char **previous_words,
 	if (Matches1("UNLISTEN"))
 		COMPLETE_WITH_QUERY("SELECT pg_catalog.quote_ident(channel) FROM pg_catalog.pg_listening_channels() AS channel WHERE substring(pg_catalog.quote_ident(channel),1,%d)='%s' UNION SELECT '*'");
 
-/* UPDATE --- can be inside EXPLAIN, RULE, etc */
+/* UPDATE  */
 	/* If prev. word is UPDATE suggest a list of tables */
-	if (TailMatches1("UPDATE"))
+	if (Matches1("UPDATE"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_updatables);
 	/* Complete UPDATE <table> with "SET" */
-	if (TailMatches2("UPDATE", MatchAny))
+	if (Matches2("UPDATE", MatchAny))
 		COMPLETE_WITH_CONST("SET");
 	/* Complete UPDATE <table> SET with list of attributes */
-	if (TailMatches3("UPDATE", MatchAny, "SET"))
-		COMPLETE_WITH_ATTR(prev2_wd, "");
+	if (Matches3("UPDATE", MatchAny, "SET"))
+		COMPLETE_WITH_ATTR(prev2_wd);
 	/* UPDATE <table> SET <attr> = */
-	if (TailMatches4("UPDATE", MatchAny, "SET", MatchAny))
+	if (Matches4("UPDATE", MatchAny, "SET", MatchAny))
 		COMPLETE_WITH_CONST("=");
 
 /* USER MAPPING */
@@ -2741,7 +2845,7 @@ psql_completion_internal(const char *text, char **previous_words,
 /* WHERE */
 	/* Simple case of the word before the where being the table name */
 	if (TailMatches2(MatchAny, "WHERE"))
-		COMPLETE_WITH_ATTR(prev2_wd, "");
+		COMPLETE_WITH_ATTR(prev2_wd);
 
 /* ... FROM ... */
 /* TODO: also include SRF ? */
@@ -2914,18 +3018,14 @@ psql_completion_internal(const char *text, char **previous_words,
 	 */
 	else
 	{
-		int			i;
+		const pgsql_thing_t *ent = find_thing_entry(prev_wd);
 
-		for (i = 0; words_after_create[i].name; i++)
+		if (ent)
 		{
-			if (pg_strcasecmp(prev_wd, words_after_create[i].name) == 0)
-			{
-				if (words_after_create[i].query)
-					COMPLETE_WITH_QUERY(words_after_create[i].query);
-				if (words_after_create[i].squery)
-					COMPLETE_WITH_SCHEMA_QUERY(*words_after_create[i].squery);
-				break;
-			}
+			if (ent->query)
+				COMPLETE_WITH_QUERY(ent->query);
+			else if (ent->squery)
+				COMPLETE_WITH_SCHEMA_QUERY(*ent->squery);
 		}
 	}
 
@@ -3436,6 +3536,18 @@ complete_from_files(const char *text, int state)
 
 /* HELPER FUNCTIONS */
 
+/*
+ * Return the index (reverse to the index of previous_words) of the tailmost
+ * (topmost in the array) appearance of w.
+ */
+static int
+find_last_index_of(char *w, char **previous_words, int len)
+{
+	int i;
+
+	for (i = 0 ; i < len && !word_matches(w, previous_words[i]) ; i++);
+	return i < len ? (len - i - 1) : 0;
+}
 
 /*
  * Make a pg_strdup copy of s and convert the case according to
@@ -3735,6 +3847,24 @@ get_guctype(const char *varname)
 	return guctype;
 }
 
+/*
+ * Finds the entry in words_after_create[] that matches the word.
+ * NULL if not found.
+ */
+static const pgsql_thing_t *
+find_thing_entry(char *word)
+{
+	int			i;
+
+	for (i = 0; words_after_create[i].name; i++)
+	{
+		if (pg_strcasecmp(word, words_after_create[i].name) == 0)
+			return words_after_create + i;
+	}
+
+	return NULL;
+}
+
 #ifdef NOT_USED
 
 /*
-- 
2.9.2

----Next_Part(Tue_Nov_15_20_26_42_2016_708)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="0005-Add-suggestion-for-IF-NOT-EXISTS-for-some-syntaxes.patch"