Add psql tab completion for FOR PORTION OF clause

Add tab completion support in psql for the FOR PORTION OF clause
used in UPDATE and DELETE statements with temporal tables.

For both UPDATE and DELETE, completion now guides users through:
  <table> FOR -> PORTION -> OF -> <column> -> FROM

Author: Kiran Kaki <itskkpg@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CAD0dvCQLqLzPrQJRjjA2qXDH%3DD%2BXShcxhbSPxNhVruC8HGhkbQ%40mail.gmail.com

2 files changed, 66 insertions, 4 deletions

diff --git a/src/bin/psql/t/010_tab_completion.pl b/src/bin/psql/t/010_tab_completion.pl
index 1d2e5f5b92a..64e27ef87a3 100644
--- a/src/bin/psql/t/010_tab_completion.pl
+++ b/src/bin/psql/t/010_tab_completion.pl
@@ -44,7 +44,9 @@ $node->safe_psql('postgres',
 	  . "CREATE TABLE mytab246 (f1 int, f2 text);\n"
 	  . "CREATE TABLE \"mixedName\" (f1 int, f2 text);\n"
 	  . "CREATE TYPE enum1 AS ENUM ('foo', 'bar', 'baz', 'BLACK');\n"
-	  . "CREATE PUBLICATION some_publication;\n");
+	  . "CREATE PUBLICATION some_publication;\n"
+	  . "CREATE TABLE fpo_test (id int4range, valid_at daterange, name text);\n"
+);
 
 # In a VPATH build, we'll be started in the source directory, but we want
 # to run in the build directory so that we can use relative paths to
@@ -422,6 +424,42 @@ check_completion(
 
 clear_line();
 
+# check tab completion for DELETE ... FOR PORTION OF
+check_completion(
+	"DELETE FROM fpo_test F\t",
+	qr/FOR /,
+	"complete DELETE FROM <table> F<tab> to FOR");
+
+check_completion("P\t", qr/PORTION /, "complete FOR P<tab> to PORTION");
+
+check_completion("O\t", qr/OF /, "complete PORTION O<tab> to OF");
+
+check_completion("v\t", qr/valid_at /,
+	"complete FOR PORTION OF offers column names");
+
+check_completion("FR\t", qr/FROM /,
+	"complete FOR PORTION OF <col> FR<tab> to FROM");
+
+clear_query();
+
+# check tab completion for UPDATE ... FOR PORTION OF
+check_completion(
+	"UPDATE fpo_test F\t",
+	qr/FOR /,
+	"complete UPDATE <table> F<tab> to FOR");
+
+check_completion("P\t", qr/PORTION /, "complete FOR P<tab> to PORTION");
+
+check_completion("O\t", qr/OF /, "complete PORTION O<tab> to OF");
+
+check_completion("v\t", qr/valid_at /,
+	"complete FOR PORTION OF offers column names");
+
+check_completion("FR\t", qr/FROM /,
+	"complete FOR PORTION OF <col> FR<tab> to FROM");
+
+clear_query();
+
 # send psql an explicit \q to shut it down, else pty won't close properly
 $h->quit or die "psql returned $?";
 
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index db65d130fcb..1335479d2aa 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -4362,7 +4362,19 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_updatables);
 	/* Complete DELETE FROM <table> */
 	else if (TailMatches("DELETE", "FROM", MatchAny))
-		COMPLETE_WITH("USING", "WHERE");
+		COMPLETE_WITH("FOR", "USING", "WHERE");
+	/* Complete DELETE FROM <table> FOR with PORTION */
+	else if (TailMatches("DELETE", "FROM", MatchAny, "FOR"))
+		COMPLETE_WITH("PORTION");
+	/* Complete DELETE FROM <table> FOR PORTION with OF */
+	else if (TailMatches("DELETE", "FROM", MatchAny, "FOR", "PORTION"))
+		COMPLETE_WITH("OF");
+	/* Complete DELETE FROM <table> FOR PORTION OF with column names */
+	else if (TailMatches("DELETE", "FROM", MatchAny, "FOR", "PORTION", "OF"))
+		COMPLETE_WITH_ATTR(prev4_wd);
+	/* Complete DELETE FROM <table> FOR PORTION OF <period> with FROM */
+	else if (TailMatches("DELETE", "FROM", MatchAny, "FOR", "PORTION", "OF", MatchAny))
+		COMPLETE_WITH("FROM");
 	/* Complete DELETE FROM <table> USING with relations supporting SELECT */
 	else if (TailMatches("DELETE", "FROM", MatchAny, "USING"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_selectables);
@@ -5434,9 +5446,21 @@ match_previous_words(int pattern_id,
 	/* If prev. word is UPDATE suggest a list of tables */
 	else if (TailMatches("UPDATE"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_updatables);
-	/* Complete UPDATE <table> with "SET" */
+	/* Complete UPDATE <table> with "SET" or "FOR" (for FOR PORTION OF) */
 	else if (TailMatches("UPDATE", MatchAny))
-		COMPLETE_WITH("SET");
+		COMPLETE_WITH("FOR", "SET");
+	/* Complete UPDATE <table> FOR with PORTION */
+	else if (TailMatches("UPDATE", MatchAny, "FOR"))
+		COMPLETE_WITH("PORTION");
+	/* Complete UPDATE <table> FOR PORTION with OF */
+	else if (TailMatches("UPDATE", MatchAny, "FOR", "PORTION"))
+		COMPLETE_WITH("OF");
+	/* Complete UPDATE <table> FOR PORTION OF with column names */
+	else if (TailMatches("UPDATE", MatchAny, "FOR", "PORTION", "OF"))
+		COMPLETE_WITH_ATTR(prev4_wd);
+	/* Complete UPDATE <table> FOR PORTION OF <period> with FROM */
+	else if (TailMatches("UPDATE", MatchAny, "FOR", "PORTION", "OF", MatchAny))
+		COMPLETE_WITH("FROM");
 	/* Complete UPDATE <table> SET with list of attributes */
 	else if (TailMatches("UPDATE", MatchAny, "SET"))
 		COMPLETE_WITH_ATTR(prev2_wd);