Add missing tab completion for ALTER TABLE ADD COLUMN IF NOT EXISTS

Started by Kirill Reshkeabout 1 year ago12 messages

reshkekirill@gmail.com

about 1 year ago

1 attachment(s)

Hi hackers!

I have been working on cloudberry/greenplum tab completion features,
and I spotted that the postgres version of psql does not tab-complete
ALTER TABLE ADD COLUMN IF NOT EXISTS pattern.
So, I propose to support this.

--
Best regards,
Kirill Reshke

Attachments:

v1-0001-Add-missing-psql-tab-completion-for-som-ALTER-TAB.patchapplication/octet-stream; name=v1-0001-Add-missing-psql-tab-completion-for-som-ALTER-TAB.patchDownload

From 2a35d486b8263744f10af6702408764befc03e87 Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Fri, 1 Nov 2024 10:39:27 +0000
Subject: [PATCH v1] Add missing psql tab completion for som ALTER TABLE
 patterns

ALTER TABBLE patterns that are not tab-completed via psql:

ALTER TABLE <name> ADD IF NOT EXISTS <coldef>
ALTER TABLE <name> ADD COLUMN IF NOT EXISTS <coldef> */
---
 src/bin/psql/tab-complete.in.c | 16 ++++++++++++----
 1 file changed, 12 insertions(+), 4 deletions(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 1be0056af7..4b5334f2f0 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2689,12 +2689,20 @@ match_previous_words(int pattern_id,
 	/* ALTER TABLE xxx ADD */
 	else if (Matches("ALTER", "TABLE", MatchAny, "ADD"))
 	{
-		/* make sure to keep this list and the !Matches() below in sync */
+		/* make sure to keep this list and the !Matches() below in sync, except for 
+		* IF NOT EXISTS, becuse "IF" is valid column name */
 		COMPLETE_WITH("COLUMN", "CONSTRAINT", "CHECK", "UNIQUE", "PRIMARY KEY",
-					  "EXCLUDE", "FOREIGN KEY");
+					  "EXCLUDE", "FOREIGN KEY", "IF NOT EXISTS");
 	}
-	/* ALTER TABLE xxx ADD [COLUMN] yyy */
-	else if (Matches("ALTER", "TABLE", MatchAny, "ADD", "COLUMN", MatchAny) ||
+	/* ALTER TABLE xxx ADD IF NOT EXISTS */
+	else if (Matches("ALTER", "TABLE", MatchAny, "ADD", "COLUMN"))
+	{
+		COMPLETE_WITH("IF NOT EXISTS");
+	}
+	/* ALTER TABLE xxx ADD [IF NOT EXISTS] [COLUMN] yyy */
+	else if (Matches("ALTER", "TABLE", MatchAny, "ADD", "IF", "NOT", "EXISTS", MatchAny) ||
+			 Matches("ALTER", "TABLE", MatchAny, "ADD", "COLUMN", "IF", "NOT", "EXISTS", MatchAny) ||
+			 Matches("ALTER", "TABLE", MatchAny, "ADD", "COLUMN", MatchAny) ||
 			 Matches("ALTER", "TABLE", MatchAny, "ADD", MatchAnyExcept("COLUMN|CONSTRAINT|CHECK|UNIQUE|PRIMARY|EXCLUDE|FOREIGN")))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
 	/* ALTER TABLE xxx ADD CONSTRAINT yyy */
-- 
2.34.1

Kirill Reshke

reshkekirill@gmail.com

about 1 year ago

In reply to: Kirill Reshke (#1)

2 attachment(s)

Re: Add missing tab completion for ALTER TABLE ADD COLUMN IF NOT EXISTS

On Fri, 1 Nov 2024 at 15:43, Kirill Reshke <reshkekirill@gmail.com> wrote:

Hi hackers!

I have been working on cloudberry/greenplum tab completion features,
and I spotted that the postgres version of psql does not tab-complete
ALTER TABLE ADD COLUMN IF NOT EXISTS pattern.
So, I propose to support this.

--
Best regards,
Kirill Reshke

Hi!
I found another pattern which has no tab competition.

--
Best regards,
Kirill Reshke

Attachments:

v2-0002-Enhance-tab-completion-to-ALTER-TYPE-ADD-DROP-ATT.patchapplication/octet-stream; name=v2-0002-Enhance-tab-completion-to-ALTER-TYPE-ADD-DROP-ATT.patchDownload

From 9e2eeeb5066cbfa75d8a4be3658ed394e3efa11b Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Fri, 1 Nov 2024 17:56:23 +0000
Subject: [PATCH v2 2/2] Enhance tab completion to ALTER TYPE ADD/DROP
 ATTRIBUTE

Now psql will complete

ALTER TYPE <typname> ADD ATTRIBUTE <attname>
with possible datatypes

Also, DROP/ADD ATTRIBUTE ... will be completed with CASCADE/RESRTICT
where appropriate
---
 src/bin/psql/tab-complete.in.c | 9 +++++++++
 1 file changed, 9 insertions(+)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 4b5334f2f0..7a3826aee8 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3007,6 +3007,15 @@ match_previous_words(int pattern_id,
 	 */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ALTER|DROP|RENAME", "ATTRIBUTE"))
 		COMPLETE_WITH_ATTR(prev3_wd);
+	/* complete ALTER TYPE ADD ATTRIBUTE <foo> with list of types */
+	else if (Matches("ALTER", "TYPE", MatchAny, "ADD", "ATTRIBUTE", MatchAny))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
+	/* complete ALTER TYPE ADD ATTRIBUTE <foo> <footype> with CASCADE/RESTRICT */
+	else if (Matches("ALTER", "TYPE", MatchAny, "ADD", "ATTRIBUTE", MatchAny, MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
+	/* complete ALTER TYPE DROP ATTRIBUTE <foo> with CASCADE/RESTRICT */
+	else if (Matches("ALTER", "TYPE", MatchAny, "DROP", "ATTRIBUTE", MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
 	/* ALTER TYPE ALTER ATTRIBUTE <foo> */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ALTER", "ATTRIBUTE", MatchAny))
 		COMPLETE_WITH("TYPE");
-- 
2.34.1

v2-0001-Add-missing-psql-tab-completion-for-som-ALTER-TAB.patchapplication/octet-stream; name=v2-0001-Add-missing-psql-tab-completion-for-som-ALTER-TAB.patchDownload

From 2a35d486b8263744f10af6702408764befc03e87 Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Fri, 1 Nov 2024 10:39:27 +0000
Subject: [PATCH v2 1/2] Add missing psql tab completion for som ALTER TABLE
 patterns

ALTER TABBLE patterns that are not tab-completed via psql:

ALTER TABLE <name> ADD IF NOT EXISTS <coldef>
ALTER TABLE <name> ADD COLUMN IF NOT EXISTS <coldef> */
---
 src/bin/psql/tab-complete.in.c | 16 ++++++++++++----
 1 file changed, 12 insertions(+), 4 deletions(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 1be0056af7..4b5334f2f0 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2689,12 +2689,20 @@ match_previous_words(int pattern_id,
 	/* ALTER TABLE xxx ADD */
 	else if (Matches("ALTER", "TABLE", MatchAny, "ADD"))
 	{
-		/* make sure to keep this list and the !Matches() below in sync */
+		/* make sure to keep this list and the !Matches() below in sync, except for 
+		* IF NOT EXISTS, becuse "IF" is valid column name */
 		COMPLETE_WITH("COLUMN", "CONSTRAINT", "CHECK", "UNIQUE", "PRIMARY KEY",
-					  "EXCLUDE", "FOREIGN KEY");
+					  "EXCLUDE", "FOREIGN KEY", "IF NOT EXISTS");
 	}
-	/* ALTER TABLE xxx ADD [COLUMN] yyy */
-	else if (Matches("ALTER", "TABLE", MatchAny, "ADD", "COLUMN", MatchAny) ||
+	/* ALTER TABLE xxx ADD IF NOT EXISTS */
+	else if (Matches("ALTER", "TABLE", MatchAny, "ADD", "COLUMN"))
+	{
+		COMPLETE_WITH("IF NOT EXISTS");
+	}
+	/* ALTER TABLE xxx ADD [IF NOT EXISTS] [COLUMN] yyy */
+	else if (Matches("ALTER", "TABLE", MatchAny, "ADD", "IF", "NOT", "EXISTS", MatchAny) ||
+			 Matches("ALTER", "TABLE", MatchAny, "ADD", "COLUMN", "IF", "NOT", "EXISTS", MatchAny) ||
+			 Matches("ALTER", "TABLE", MatchAny, "ADD", "COLUMN", MatchAny) ||
 			 Matches("ALTER", "TABLE", MatchAny, "ADD", MatchAnyExcept("COLUMN|CONSTRAINT|CHECK|UNIQUE|PRIMARY|EXCLUDE|FOREIGN")))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
 	/* ALTER TABLE xxx ADD CONSTRAINT yyy */
-- 
2.34.1

Kirill Reshke

reshkekirill@gmail.com

about 1 year ago

In reply to: Kirill Reshke (#1)

3 attachment(s)

Re: Add missing tab completion for ALTER TABLE ADD COLUMN IF NOT EXISTS

On Fri, 1 Nov 2024 at 15:43, Kirill Reshke <reshkekirill@gmail.com> wrote:

Hi hackers!

I have been working on cloudberry/greenplum tab completion features,
and I spotted that the postgres version of psql does not tab-complete
ALTER TABLE ADD COLUMN IF NOT EXISTS pattern.
So, I propose to support this.

--
Best regards,
Kirill Reshke

Hi, I spotted another missing option:

CREATE TEMP TABLE <name> (...) does not complete with USING.
I suspect the reason is that commit 4cba9c2, which added this tab
completion, was before TAM support.

So, sending this enhancement (v3-0003) along with two other patches.

--
Best regards,
Kirill Reshke

Attachments:

v3-0002-Enhance-tab-completion-to-ALTER-TYPE-ADD-DROP-ATT.patchapplication/octet-stream; name=v3-0002-Enhance-tab-completion-to-ALTER-TYPE-ADD-DROP-ATT.patchDownload

From 985af98095f2b1da6d7586c6fcdae20df3fb0461 Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Fri, 1 Nov 2024 17:56:23 +0000
Subject: [PATCH v3 2/3] Enhance tab completion to ALTER TYPE ADD/DROP
 ATTRIBUTE

Now psql will complete

ALTER TYPE <typname> ADD ATTRIBUTE <attname>
with possible datatypes

Also, DROP/ADD ATTRIBUTE ... will be completed with CASCADE/RESRTICT
where appropriate
---
 src/bin/psql/tab-complete.in.c | 9 +++++++++
 1 file changed, 9 insertions(+)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 4b5334f2f0..7a3826aee8 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3007,6 +3007,15 @@ match_previous_words(int pattern_id,
 	 */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ALTER|DROP|RENAME", "ATTRIBUTE"))
 		COMPLETE_WITH_ATTR(prev3_wd);
+	/* complete ALTER TYPE ADD ATTRIBUTE <foo> with list of types */
+	else if (Matches("ALTER", "TYPE", MatchAny, "ADD", "ATTRIBUTE", MatchAny))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
+	/* complete ALTER TYPE ADD ATTRIBUTE <foo> <footype> with CASCADE/RESTRICT */
+	else if (Matches("ALTER", "TYPE", MatchAny, "ADD", "ATTRIBUTE", MatchAny, MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
+	/* complete ALTER TYPE DROP ATTRIBUTE <foo> with CASCADE/RESTRICT */
+	else if (Matches("ALTER", "TYPE", MatchAny, "DROP", "ATTRIBUTE", MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
 	/* ALTER TYPE ALTER ATTRIBUTE <foo> */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ALTER", "ATTRIBUTE", MatchAny))
 		COMPLETE_WITH("TYPE");
-- 
2.34.1

v3-0001-Add-missing-psql-tab-completion-for-som-ALTER-TAB.patchapplication/octet-stream; name=v3-0001-Add-missing-psql-tab-completion-for-som-ALTER-TAB.patchDownload

From adc85b5fabdcd6bcfc80214102b3fe200ba397f1 Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Fri, 1 Nov 2024 10:39:27 +0000
Subject: [PATCH v3 1/3] Add missing psql tab completion for som ALTER TABLE
 patterns

ALTER TABBLE patterns that are not tab-completed via psql:

ALTER TABLE <name> ADD IF NOT EXISTS <coldef>
ALTER TABLE <name> ADD COLUMN IF NOT EXISTS <coldef> */
---
 src/bin/psql/tab-complete.in.c | 16 ++++++++++++----
 1 file changed, 12 insertions(+), 4 deletions(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 1be0056af7..4b5334f2f0 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2689,12 +2689,20 @@ match_previous_words(int pattern_id,
 	/* ALTER TABLE xxx ADD */
 	else if (Matches("ALTER", "TABLE", MatchAny, "ADD"))
 	{
-		/* make sure to keep this list and the !Matches() below in sync */
+		/* make sure to keep this list and the !Matches() below in sync, except for 
+		* IF NOT EXISTS, becuse "IF" is valid column name */
 		COMPLETE_WITH("COLUMN", "CONSTRAINT", "CHECK", "UNIQUE", "PRIMARY KEY",
-					  "EXCLUDE", "FOREIGN KEY");
+					  "EXCLUDE", "FOREIGN KEY", "IF NOT EXISTS");
 	}
-	/* ALTER TABLE xxx ADD [COLUMN] yyy */
-	else if (Matches("ALTER", "TABLE", MatchAny, "ADD", "COLUMN", MatchAny) ||
+	/* ALTER TABLE xxx ADD IF NOT EXISTS */
+	else if (Matches("ALTER", "TABLE", MatchAny, "ADD", "COLUMN"))
+	{
+		COMPLETE_WITH("IF NOT EXISTS");
+	}
+	/* ALTER TABLE xxx ADD [IF NOT EXISTS] [COLUMN] yyy */
+	else if (Matches("ALTER", "TABLE", MatchAny, "ADD", "IF", "NOT", "EXISTS", MatchAny) ||
+			 Matches("ALTER", "TABLE", MatchAny, "ADD", "COLUMN", "IF", "NOT", "EXISTS", MatchAny) ||
+			 Matches("ALTER", "TABLE", MatchAny, "ADD", "COLUMN", MatchAny) ||
 			 Matches("ALTER", "TABLE", MatchAny, "ADD", MatchAnyExcept("COLUMN|CONSTRAINT|CHECK|UNIQUE|PRIMARY|EXCLUDE|FOREIGN")))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
 	/* ALTER TABLE xxx ADD CONSTRAINT yyy */
-- 
2.34.1

v3-0003-Add-missing-tab-completion-for-CREATE-TEMP-TABLE-.patchapplication/octet-stream; name=v3-0003-Add-missing-tab-completion-for-CREATE-TEMP-TABLE-.patchDownload

From 64bb09b2019aae0c4c651bdef3aa7f3655dfe3a5 Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Mon, 4 Nov 2024 11:45:26 +0000
Subject: [PATCH v3 3/3] Add missing tab completion for CREATE TEMP TABLE ...
 USING option

---
 src/bin/psql/tab-complete.in.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 7a3826aee8..0d454d4c42 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3631,7 +3631,7 @@ match_previous_words(int pattern_id,
 			 TailMatches("CREATE", "UNLOGGED", "TABLE", MatchAny, "(*)"))
 		COMPLETE_WITH("AS", "INHERITS (", "PARTITION BY", "USING", "TABLESPACE", "WITH (");
 	else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)"))
-		COMPLETE_WITH("AS", "INHERITS (", "ON COMMIT", "PARTITION BY",
+		COMPLETE_WITH("AS", "INHERITS (", "ON COMMIT", "PARTITION BY", "USING",
 					  "TABLESPACE", "WITH (");
 	/* Complete CREATE TABLE (...) USING with table access methods */
 	else if (TailMatches("CREATE", "TABLE", MatchAny, "(*)", "USING") ||
-- 
2.34.1

Karina Litskevich

litskevichkarina@gmail.com

about 1 year ago

In reply to: Kirill Reshke (#3)

Re: Add missing tab completion for ALTER TABLE ADD COLUMN IF NOT EXISTS

Hi Kirill,

I looked through your patches. First of all, please note that TAB
completion doesn't have to complete all valid grammatical
constructions. See the comment on the top of tab-complete.in.c:

* This file implements a somewhat more sophisticated readline "TAB
* completion" in psql. It is not intended to be AI, to replace
* learning SQL, or to relieve you from thinking about what you're
* doing. Also it does not always give you all the syntactically legal
* completions, only those that are the most common or the ones that
* the programmer felt most like implementing.

Considering this, I don't think 0001 should be accepted as is. Neither
"IF NOT EXIST" nor "IF EXISTS" appears anywhere in tab-complete.in.c.
Adding it for the only "ALTER TABLE ... ADD COLUMN" case doesn't seem
right. Adding it everywhere looks like lots of work of questionable
utility.

0003 looks good to me. Considering that we already have a completion of
"CREATE TEMP TABLE ... USING" with table access methods, completion of
"CREATE TABLE ..." with USING seems reasonable. It’s strange we didn’t
have this before. Good catch!

I can't judge if 0002 adds useful completion cases, but I noticed that
you add completion of "DROP/ADD ATTRIBUTE ..." with CASCADE/RESRTICT
while ignoring "ALTER/RENAME ATTRIBUTE ..." that also can be followed
by CASCADE/RESRTICT according to the documentation. Is it intentional?

Best regards,
Karina Litskevich
Postgres Professional: http://postgrespro.com/

Kirill Reshke

reshkekirill@gmail.com

about 1 year ago

In reply to: Karina Litskevich (#4)

4 attachment(s)

Re: Add missing tab completion for ALTER TABLE ADD COLUMN IF NOT EXISTS

Hi, thank you for your feedback.

On Wed, 6 Nov 2024 at 20:09, Karina Litskevich
<litskevichkarina@gmail.com> wrote:

Hi Kirill,

I looked through your patches. First of all, please note that TAB
completion doesn't have to complete all valid grammatical
constructions. See the comment on the top of tab-complete.in.c:

* This file implements a somewhat more sophisticated readline "TAB
* completion" in psql. It is not intended to be AI, to replace
* learning SQL, or to relieve you from thinking about what you're
* doing. Also it does not always give you all the syntactically legal
* completions, only those that are the most common or the ones that
* the programmer felt most like implementing.

Considering this, I don't think 0001 should be accepted as is. Neither
"IF NOT EXIST" nor "IF EXISTS" appears anywhere in tab-complete.in.c.
Adding it for the only "ALTER TABLE ... ADD COLUMN" case doesn't seem
right. Adding it everywhere looks like lots of work of questionable
utility.

Ok. I dropped this patch from v4.

0003 looks good to me. Considering that we already have a completion of
"CREATE TEMP TABLE ... USING" with table access methods, completion of
"CREATE TABLE ..." with USING seems reasonable. It’s strange we didn’t
have this before. Good catch!

Cool.

I can't judge if 0002 adds useful completion cases, but I noticed that
you add completion of "DROP/ADD ATTRIBUTE ..." with CASCADE/RESRTICT
while ignoring "ALTER/RENAME ATTRIBUTE ..." that also can be followed
by CASCADE/RESRTICT according to the documentation. Is it intentional?

v3-0002 patch actually mixes two types of completion. First one, which
adds a data type completion for ADD ATTRIBUTE, is pretty useful.
similar to existing tab completion for `ALTER TABLE xxx ADD [IF NOT
EXISTS] [COLUMN] yyy `.
The second one (CASCADE/RESTRICT) is dubious, and I'm also not sure if
we really need it, so perhaps we should wait for some other views..
For this reason, I split v3-0002 into two separate patches.

I also want to propose an Access method completion for create M.V.
<foo> using. This is a patch I forgot to attach in my last email.
Please, see v4-0004

--
Best regards,
Kirill Reshke

Attachments:

v4-0003-Add-missing-tab-completion-for-CREATE-TEMP-TABLE-.patchapplication/octet-stream; name=v4-0003-Add-missing-tab-completion-for-CREATE-TEMP-TABLE-.patchDownload

From bb839072cea10c0f32a2f14d45580b0192f6c8e8 Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Mon, 4 Nov 2024 11:45:26 +0000
Subject: [PATCH v4 3/4] Add missing tab completion for CREATE TEMP TABLE ...
 USING option

---
 src/bin/psql/tab-complete.in.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 15f16c994c..d053ede02b 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3626,7 +3626,7 @@ match_previous_words(int pattern_id,
 			 TailMatches("CREATE", "UNLOGGED", "TABLE", MatchAny, "(*)"))
 		COMPLETE_WITH("AS", "INHERITS (", "PARTITION BY", "USING", "TABLESPACE", "WITH (");
 	else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)"))
-		COMPLETE_WITH("AS", "INHERITS (", "ON COMMIT", "PARTITION BY",
+		COMPLETE_WITH("AS", "INHERITS (", "ON COMMIT", "PARTITION BY", "USING",
 					  "TABLESPACE", "WITH (");
 	/* Complete CREATE TABLE (...) USING with table access methods */
 	else if (TailMatches("CREATE", "TABLE", MatchAny, "(*)", "USING") ||
-- 
2.34.1

v4-0001-Enhance-tab-completion-to-ALTER-TYPE-ADD-ATTRIBUT.patchapplication/octet-stream; name=v4-0001-Enhance-tab-completion-to-ALTER-TYPE-ADD-ATTRIBUT.patchDownload

From 1755d05c020ef7cfb6abd48d02be77a5c06d254f Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Fri, 1 Nov 2024 17:56:23 +0000
Subject: [PATCH v4 1/4] Enhance tab completion to ALTER TYPE ADD ATTRIBUTE

Now psql will complete
ALTER TYPE <typname> ADD ATTRIBUTE <attname>
with possible datatypes
---
 src/bin/psql/tab-complete.in.c | 3 +++
 1 file changed, 3 insertions(+)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 1be0056af7..a420666542 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2999,6 +2999,9 @@ match_previous_words(int pattern_id,
 	 */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ALTER|DROP|RENAME", "ATTRIBUTE"))
 		COMPLETE_WITH_ATTR(prev3_wd);
+	/* complete ALTER TYPE ADD ATTRIBUTE <foo> with list of types */
+	else if (Matches("ALTER", "TYPE", MatchAny, "ADD", "ATTRIBUTE", MatchAny))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
 	/* ALTER TYPE ALTER ATTRIBUTE <foo> */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ALTER", "ATTRIBUTE", MatchAny))
 		COMPLETE_WITH("TYPE");
-- 
2.34.1

v4-0002-Add-CASCADE-RESRTICT-to-ALTER-TYPE-patterns.patchapplication/octet-stream; name=v4-0002-Add-CASCADE-RESRTICT-to-ALTER-TYPE-patterns.patchDownload

From 254637604717fa926a0c655f3ec680f9dd1fbe2e Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Thu, 7 Nov 2024 11:30:18 +0000
Subject: [PATCH v4 2/4] Add CASCADE/RESRTICT to ALTER TYPE patterns

DROP/ADD/RENAME ATTRIBUTE ... will be completed with CASCADE/RESRTICT
where appropriate
---
 src/bin/psql/tab-complete.in.c | 9 +++++++++
 1 file changed, 9 insertions(+)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index a420666542..15f16c994c 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2992,6 +2992,9 @@ match_previous_words(int pattern_id,
 	/* ALTER TYPE xxx RENAME (ATTRIBUTE|VALUE) yyy */
 	else if (Matches("ALTER", "TYPE", MatchAny, "RENAME", "ATTRIBUTE|VALUE", MatchAny))
 		COMPLETE_WITH("TO");
+	/* ALTER TYPE xxx RENAME (ATTRIBUTE|VALUE) yyy TO zzz */
+	else if (Matches("ALTER", "TYPE", MatchAny, "RENAME", "ATTRIBUTE|VALUE", MatchAny, "TO", MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
 
 	/*
 	 * If we have ALTER TYPE <sth> ALTER/DROP/RENAME ATTRIBUTE, provide list
@@ -3002,6 +3005,12 @@ match_previous_words(int pattern_id,
 	/* complete ALTER TYPE ADD ATTRIBUTE <foo> with list of types */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ADD", "ATTRIBUTE", MatchAny))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
+	/* complete ALTER TYPE ADD ATTRIBUTE <foo> <footype> with CASCADE/RESTRICT */
+	else if (Matches("ALTER", "TYPE", MatchAny, "ADD", "ATTRIBUTE", MatchAny, MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
+	/* complete ALTER TYPE DROP ATTRIBUTE <foo> with CASCADE/RESTRICT */
+	else if (Matches("ALTER", "TYPE", MatchAny, "DROP", "ATTRIBUTE", MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
 	/* ALTER TYPE ALTER ATTRIBUTE <foo> */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ALTER", "ATTRIBUTE", MatchAny))
 		COMPLETE_WITH("TYPE");
-- 
2.34.1

v4-0004-Suggest-table-access-method-for-create-maateriali.patchapplication/octet-stream; name=v4-0004-Suggest-table-access-method-for-create-maateriali.patchDownload

From b2988b138224fdc4c1838f82b9e8125be4991cbb Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Mon, 4 Nov 2024 16:22:59 +0000
Subject: [PATCH v4 4/4] Suggest table access method for create maaterialized
 view in tab-complete

---
 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 d053ede02b..dc2ff335b5 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3980,9 +3980,13 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH("VIEW");
 	/* Complete CREATE MATERIALIZED VIEW <name> with AS */
 	else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny))
-		COMPLETE_WITH("AS");
-	/* Complete "CREATE MATERIALIZED VIEW <sth> AS with "SELECT" */
-	else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS"))
+		COMPLETE_WITH("AS", "USING");
+	/* Complete CREATE MATERIALIZED VIEW <name> USING with list of access methods */
+	else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING"))
+		COMPLETE_WITH_QUERY(Query_for_list_of_table_access_methods);
+	/* Complete "CREATE MATERIALIZED VIEW <sth> [USING <am_name>] AS with "SELECT" */
+	else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS") || 
+			 Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING", MatchAny, "AS"))
 		COMPLETE_WITH("SELECT");
 
 /* CREATE EVENT TRIGGER */
-- 
2.34.1

Karina Litskevich

litskevichkarina@gmail.com

about 1 year ago

In reply to: Kirill Reshke (#5)

Re: Add missing tab completion for ALTER TABLE ADD COLUMN IF NOT EXISTS

I looked through the new set of patches.

On Thu, Nov 7, 2024 at 2:42 PM Kirill Reshke <reshkekirill@gmail.com> wrote:

v3-0002 patch actually mixes two types of completion. First one, which
adds a data type completion for ADD ATTRIBUTE, is pretty useful.
similar to existing tab completion for `ALTER TABLE xxx ADD [IF NOT
EXISTS] [COLUMN] yyy `.

As I understand, it's v4-0001. Looks fine, I agree that it's a useful
completion and I can't see any arguments against that.

The second one (CASCADE/RESTRICT) is dubious, and I'm also not sure if
we really need it, so perhaps we should wait for some other views..
For this reason, I split v3-0002 into two separate patches.

And this is v4-0002. Agreed that we should wait for another opinion to
see if it's needed. Anyway, I have a few remarks here:
1) "ALTER TYPE xxx RENAME VALUE yyy TO zzz" can't be followed by
CASCADE/RESTRICT at least according to the docs [1]https://www.postgresql.org/docs/current/sql-altertype.html.
2) I still can't see why you don't complete "ALTER TYPE ... ALTER
ATTRIBUTE ... TYPE ..." with CASCADE/RESTRICT.

By the way, I suggest adding a completion of "ALTER TYPE ... ALTER
ATTRIBUTE ... TYPE" with the list of types while we are here. It should
probably go together with v4-0001.

Next, v4-0003 is the same as v3-0003, and we already agreed on this.
(Just a note for myself.)

I also want to propose an Access method completion for create M.V.
<foo> using. This is a patch I forgot to attach in my last email.
Please, see v4-0004

In v4-0004 I like the idea to add this completion, but I have some
remarks to implementation:
1) Try to keep comments identical, at least in one piece of code. Right
now you have "CREATE MATERIALIZED VIEW <name>" and "CREATE MATERIALIZED
VIEW <sth>" within three consecutive lines. I can see there was the
same problem before your changes, so it's not exactly your fault. Let's
correct it, though.
2) The comment
/* Complete CREATE MATERIALIZED VIEW <name> with AS */
is no longer correct since you've changed
- COMPLETE_WITH("AS");
to
+ COMPLETE_WITH("AS", "USING");
Please fix it.

I hope to look more thoroughly into tab-complete.in.c tomorrow or on
Monday to see if there are any other problems I can't see at first
glance. I'll send another mail when I get to do this.

[1]: https://www.postgresql.org/docs/current/sql-altertype.html

Best regards,
Karina Litskevich
Postgres Professional: http://postgrespro.com/

Karina Litskevich

litskevichkarina@gmail.com

about 1 year ago

In reply to: Karina Litskevich (#6)

Re: Add missing tab completion for ALTER TABLE ADD COLUMN IF NOT EXISTS

I did a quick testing.

As expected, both "ALTER TYPE xxx RENAME VALUE yyy TO zzz CASCADE" and
"ALTER TYPE xxx RENAME VALUE yyy TO zzz RESTRICT" in v4-0002 cause
syntax errors.

In v4-0004 you might also want to complete
"CREATE MATERIALIZED VIEW... USING ..." with "AS".

Other completions work fine.

Best regards,
Karina Litskevich
Postgres Professional: http://postgrespro.com/

Kirill Reshke

reshkekirill@gmail.com

about 1 year ago

In reply to: Karina Litskevich (#6)

4 attachment(s)

Re: Add missing tab completion for ALTER TABLE ADD COLUMN IF NOT EXISTS

On Thu, 7 Nov 2024 at 22:33, Karina Litskevich
<litskevichkarina@gmail.com> wrote:

I looked through the new set of patches.

On Thu, Nov 7, 2024 at 2:42 PM Kirill Reshke <reshkekirill@gmail.com> wrote:

v3-0002 patch actually mixes two types of completion. First one, which
adds a data type completion for ADD ATTRIBUTE, is pretty useful.
similar to existing tab completion for `ALTER TABLE xxx ADD [IF NOT
EXISTS] [COLUMN] yyy `.

As I understand, it's v4-0001. Looks fine, I agree that it's a useful
completion and I can't see any arguments against that.

The second one (CASCADE/RESTRICT) is dubious, and I'm also not sure if
we really need it, so perhaps we should wait for some other views..
For this reason, I split v3-0002 into two separate patches.

And this is v4-0002. Agreed that we should wait for another opinion to
see if it's needed. Anyway, I have a few remarks here:
1) "ALTER TYPE xxx RENAME VALUE yyy TO zzz" can't be followed by
CASCADE/RESTRICT at least according to the docs [1].

Yes, my bad. I removed "VALUE".

2) I still can't see why you don't complete "ALTER TYPE ... ALTER
ATTRIBUTE ... TYPE ..." with CASCADE/RESTRICT.

My bad, fixed this in v5. Thank you.

By the way, I suggest adding a completion of "ALTER TYPE ... ALTER
ATTRIBUTE ... TYPE" with the list of types while we are here. It should
probably go together with v4-0001.

Surprisingly this is already supported in an interesting manner. psql
will tab-complete everything that end with token "TYPE" with list of
datatypes:

```
reshke=# FOO BAR BAZ
<tab> *nothing*
reshke=# FOO BAR BAZ TYPE
<tab>
Display all 119 possibilities? (y or n)
```

Next, v4-0003 is the same as v3-0003, and we already agreed on this.
(Just a note for myself.)

I also want to propose an Access method completion for create M.V.
<foo> using. This is a patch I forgot to attach in my last email.
Please, see v4-0004

In v4-0004 I like the idea to add this completion, but I have some
remarks to implementation:
1) Try to keep comments identical, at least in one piece of code. Right
now you have "CREATE MATERIALIZED VIEW <name>" and "CREATE MATERIALIZED
VIEW <sth>" within three consecutive lines. I can see there was the
same problem before your changes, so it's not exactly your fault. Let's
correct it, though.

Ok, sure. I did the correction.

2) The comment
/* Complete CREATE MATERIALIZED VIEW <name> with AS */
is no longer correct since you've changed
- COMPLETE_WITH("AS");
to
+ COMPLETE_WITH("AS", "USING");
Please fix it.

Nice catch! Fixed.

I hope to look more thoroughly into tab-complete.in.c tomorrow or on
Monday to see if there are any other problems I can't see at first
glance. I'll send another mail when I get to do this.

Your effort is much appreciated.

[1] https://www.postgresql.org/docs/current/sql-altertype.html

Best regards,
Karina Litskevich
Postgres Professional: http://postgrespro.com/

PFA v5.

--
Best regards,
Kirill Reshke

Attachments:

v5-0002-Add-CASCADE-RESRTICT-to-ALTER-TYPE-patterns.patchapplication/octet-stream; name=v5-0002-Add-CASCADE-RESRTICT-to-ALTER-TYPE-patterns.patchDownload

From df6b8f6b8594b9d594cd5a416a0e134466770dd8 Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Thu, 7 Nov 2024 11:30:18 +0000
Subject: [PATCH v5 2/4] Add CASCADE/RESRTICT to ALTER TYPE patterns

ALTER TYPE ... DROP/ADD/RENAME ATTRIBUTE ... will be completed with CASCADE/RESRTICT
where appropriate.

ALTER TYPE ... ALTER ATTRIBUTE ... TYPE ... will also be completed
with CASCADE/RESRTICT
---
 src/bin/psql/tab-complete.in.c | 12 ++++++++++++
 1 file changed, 12 insertions(+)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index ceb13ae365..2c2983bec9 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2992,6 +2992,9 @@ match_previous_words(int pattern_id,
 	/* ALTER TYPE xxx RENAME (ATTRIBUTE|VALUE) yyy */
 	else if (Matches("ALTER", "TYPE", MatchAny, "RENAME", "ATTRIBUTE|VALUE", MatchAny))
 		COMPLETE_WITH("TO");
+	/* ALTER TYPE xxx RENAME ATTRIBUTE yyy TO zzz */
+	else if (Matches("ALTER", "TYPE", MatchAny, "RENAME", "ATTRIBUTE", MatchAny, "TO", MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
 
 	/*
 	 * If we have ALTER TYPE <sth> ALTER/DROP/RENAME ATTRIBUTE, provide list
@@ -3002,9 +3005,18 @@ match_previous_words(int pattern_id,
 	/* complete ALTER TYPE ADD ATTRIBUTE <foo> with list of types */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ADD", "ATTRIBUTE", MatchAny))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
+	/* complete ALTER TYPE ADD ATTRIBUTE <foo> <footype> with CASCADE/RESTRICT */
+	else if (Matches("ALTER", "TYPE", MatchAny, "ADD", "ATTRIBUTE", MatchAny, MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
+	/* complete ALTER TYPE DROP ATTRIBUTE <foo> with CASCADE/RESTRICT */
+	else if (Matches("ALTER", "TYPE", MatchAny, "DROP", "ATTRIBUTE", MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
 	/* ALTER TYPE ALTER ATTRIBUTE <foo> */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ALTER", "ATTRIBUTE", MatchAny))
 		COMPLETE_WITH("TYPE");
+	/* ALTER TYPE ALTER ATTRIBUTE <foo> TYPE <footype> */
+	else if (Matches("ALTER", "TYPE", MatchAny, "ALTER", "ATTRIBUTE", MatchAny, "TYPE", MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
 	/* complete ALTER TYPE <sth> RENAME VALUE with list of enum values */
 	else if (Matches("ALTER", "TYPE", MatchAny, "RENAME", "VALUE"))
 		COMPLETE_WITH_ENUM_VALUE(prev3_wd);
-- 
2.34.1

v5-0004-Suggest-table-access-method-for-create-maateriali.patchapplication/octet-stream; name=v5-0004-Suggest-table-access-method-for-create-maateriali.patchDownload

From a57a1883694edc08704715f286b6a3b5b6442778 Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Mon, 4 Nov 2024 16:22:59 +0000
Subject: [PATCH v5 4/4] Suggest table access method for create maaterialized
 view in tab-complete

---
 src/bin/psql/tab-complete.in.c | 13 ++++++++++---
 1 file changed, 10 insertions(+), 3 deletions(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index e06afddcfe..1576a3e4ca 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3981,11 +3981,18 @@ match_previous_words(int pattern_id,
 /* CREATE MATERIALIZED VIEW */
 	else if (Matches("CREATE", "MATERIALIZED"))
 		COMPLETE_WITH("VIEW");
-	/* Complete CREATE MATERIALIZED VIEW <name> with AS */
+	/* Complete CREATE MATERIALIZED VIEW <name> with AS or USING */
 	else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny))
+		COMPLETE_WITH("AS", "USING");
+	/* Complete CREATE MATERIALIZED VIEW <name> USING with list of access methods */
+	else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING"))
+		COMPLETE_WITH_QUERY(Query_for_list_of_table_access_methods);
+	/* Complete CREATE MATERIALIZED VIEW <name> USING <access method> with AS */
+	else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING", MatchAny))
 		COMPLETE_WITH("AS");
-	/* Complete "CREATE MATERIALIZED VIEW <sth> AS with "SELECT" */
-	else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS"))
+	/* Complete CREATE MATERIALIZED VIEW <name> [USING <am_name>] AS with "SELECT" */
+	else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS") || 
+			 Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING", MatchAny, "AS"))
 		COMPLETE_WITH("SELECT");
 
 /* CREATE EVENT TRIGGER */
-- 
2.34.1

v5-0003-Add-missing-tab-completion-for-CREATE-TEMP-TABLE-.patchapplication/octet-stream; name=v5-0003-Add-missing-tab-completion-for-CREATE-TEMP-TABLE-.patchDownload

From 21be3cce4356bf5841431ab830d8894eb8e37ec2 Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Mon, 4 Nov 2024 11:45:26 +0000
Subject: [PATCH v5 3/4] Add missing tab completion for CREATE TEMP TABLE ...
 USING option

---
 src/bin/psql/tab-complete.in.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 2c2983bec9..e06afddcfe 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3629,7 +3629,7 @@ match_previous_words(int pattern_id,
 			 TailMatches("CREATE", "UNLOGGED", "TABLE", MatchAny, "(*)"))
 		COMPLETE_WITH("AS", "INHERITS (", "PARTITION BY", "USING", "TABLESPACE", "WITH (");
 	else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)"))
-		COMPLETE_WITH("AS", "INHERITS (", "ON COMMIT", "PARTITION BY",
+		COMPLETE_WITH("AS", "INHERITS (", "ON COMMIT", "PARTITION BY", "USING",
 					  "TABLESPACE", "WITH (");
 	/* Complete CREATE TABLE (...) USING with table access methods */
 	else if (TailMatches("CREATE", "TABLE", MatchAny, "(*)", "USING") ||
-- 
2.34.1

v5-0001-Enhance-tab-completion-to-ALTER-TYPE-ADD-ATTRIBUT.patchapplication/octet-stream; name=v5-0001-Enhance-tab-completion-to-ALTER-TYPE-ADD-ATTRIBUT.patchDownload

From ce0709422b7d086192a27a75efc99ee324edbfee Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Fri, 1 Nov 2024 17:56:23 +0000
Subject: [PATCH v5 1/4] Enhance tab completion to ALTER TYPE ADD ATTRIBUTE

Now psql will complete
ALTER TYPE <typname> ADD ATTRIBUTE <attname>
with possible datatypes
---
 src/bin/psql/tab-complete.in.c | 3 +++
 1 file changed, 3 insertions(+)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index fad2277991..ceb13ae365 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2999,6 +2999,9 @@ match_previous_words(int pattern_id,
 	 */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ALTER|DROP|RENAME", "ATTRIBUTE"))
 		COMPLETE_WITH_ATTR(prev3_wd);
+	/* complete ALTER TYPE ADD ATTRIBUTE <foo> with list of types */
+	else if (Matches("ALTER", "TYPE", MatchAny, "ADD", "ATTRIBUTE", MatchAny))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
 	/* ALTER TYPE ALTER ATTRIBUTE <foo> */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ALTER", "ATTRIBUTE", MatchAny))
 		COMPLETE_WITH("TYPE");
-- 
2.34.1

Karina Litskevich

litskevichkarina@gmail.com

about 1 year ago

In reply to: Kirill Reshke (#8)

Re: Add missing tab completion for ALTER TABLE ADD COLUMN IF NOT EXISTS

On Sun, Nov 10, 2024 at 3:43 PM Kirill Reshke <reshkekirill@gmail.com> wrote:

By the way, I suggest adding a completion of "ALTER TYPE ... ALTER
ATTRIBUTE ... TYPE" with the list of types while we are here. It should
probably go together with v4-0001.

Surprisingly this is already supported in an interesting manner. psql
will tab-complete everything that end with token "TYPE" with list of
datatypes:

```
reshke=# FOO BAR BAZ
<tab> *nothing*
reshke=# FOO BAR BAZ TYPE
<tab>
Display all 119 possibilities? (y or n)
```

Agreed.

I wrote:

I hope to look more thoroughly into tab-complete.in.c tomorrow or on
Monday to see if there are any other problems I can't see at first
glance. I'll send another mail when I get to do this.

As promised, I looked into the new set of patches more closely. Can't
see any other significant problems. However, you still need to do a
couple of cosmetic changes.

On Sun, Nov 10, 2024 at 3:43 PM Kirill Reshke <reshkekirill@gmail.com> wrote:

1) Try to keep comments identical, at least in one piece of code. Right
now you have "CREATE MATERIALIZED VIEW <name>" and "CREATE MATERIALIZED
VIEW <sth>" within three consecutive lines. I can see there was the
same problem before your changes, so it's not exactly your fault. Let's
correct it, though.

Ok, sure. I did the correction.

You now have the same problem with "USING <access method>" and
"USING <am_name>" in v5-0004.

Also, make sure you ran pgindent before creating patches. In v5-0004
there are comments that are too long for one line, and there is a line
with a trailing space:

+ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS") ||

Other than that, everything looks fine to me.

Best regards,
Karina Litskevich
Postgres Professional: http://postgrespro.com/

#10

Tomas Vondra

tomas@vondra.me

about 1 year ago

In reply to: Karina Litskevich (#9)

Re: Add missing tab completion for ALTER TABLE ADD COLUMN IF NOT EXISTS

Hi,

On 11/11/24 12:48, Karina Litskevich wrote:

...

I wrote:

I hope to look more thoroughly into tab-complete.in.c tomorrow or on
Monday to see if there are any other problems I can't see at first
glance. I'll send another mail when I get to do this.

As promised, I looked into the new set of patches more closely. Can't
see any other significant problems. However, you still need to do a
couple of cosmetic changes.

On Sun, Nov 10, 2024 at 3:43 PM Kirill Reshke <reshkekirill@gmail.com> wrote:

1) Try to keep comments identical, at least in one piece of code. Right
now you have "CREATE MATERIALIZED VIEW <name>" and "CREATE MATERIALIZED
VIEW <sth>" within three consecutive lines. I can see there was the
same problem before your changes, so it's not exactly your fault. Let's
correct it, though.

Ok, sure. I did the correction.

You now have the same problem with "USING <access method>" and
"USING <am_name>" in v5-0004.

Also, make sure you ran pgindent before creating patches. In v5-0004
there are comments that are too long for one line, and there is a line
with a trailing space:

+ else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS") ||

Other than that, everything looks fine to me.

I took a quick look at this patch series, and it looks generally fine to
me. Barring objections, I'll get it committed. Yes, there's a couple
cosmetic details, and it needs a pgindent run, but I think I can take
care of that ...

regards

--
Tomas Vondra

#11

Kirill Reshke

reshkekirill@gmail.com

about 1 year ago

In reply to: Tomas Vondra (#10)

4 attachment(s)

Re: Add missing tab completion for ALTER TABLE ADD COLUMN IF NOT EXISTS

On Sun, 8 Dec 2024 at 03:35, Tomas Vondra <tomas@vondra.me> wrote:

Hi,
I took a quick look at this patch series, and it looks generally fine to
me. Barring objections, I'll get it committed. Yes, there's a couple
cosmetic details, and it needs a pgindent run, but I think I can take
care of that ...

Thank you for taking a look.

PFA v6 with last Karina's review comments addressed.

--
Best regards,
Kirill Reshke

Attachments:

v6-0003-Add-missing-tab-completion-for-CREATE-TEMP-TABLE-.patchapplication/octet-stream; name=v6-0003-Add-missing-tab-completion-for-CREATE-TEMP-TABLE-.patchDownload

From 5b5eee1f77da9e15bb0ebf1c6712fc63506aa5ef Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Mon, 4 Nov 2024 11:45:26 +0000
Subject: [PATCH v6 3/4] Add missing tab completion for CREATE TEMP TABLE ...
 USING option

Author: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-By: Karina Litskevich <litskevichkarina(at)gmail(dot)com>
---
 src/bin/psql/tab-complete.in.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 79b02047174..d1cd7c54f6e 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3629,7 +3629,7 @@ match_previous_words(int pattern_id,
 			 TailMatches("CREATE", "UNLOGGED", "TABLE", MatchAny, "(*)"))
 		COMPLETE_WITH("AS", "INHERITS (", "PARTITION BY", "USING", "TABLESPACE", "WITH (");
 	else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)"))
-		COMPLETE_WITH("AS", "INHERITS (", "ON COMMIT", "PARTITION BY",
+		COMPLETE_WITH("AS", "INHERITS (", "ON COMMIT", "PARTITION BY", "USING",
 					  "TABLESPACE", "WITH (");
 	/* Complete CREATE TABLE (...) USING with table access methods */
 	else if (TailMatches("CREATE", "TABLE", MatchAny, "(*)", "USING") ||
-- 
2.34.1

v6-0001-Enhance-tab-completion-to-ALTER-TYPE-ADD-ATTRIBUT.patchapplication/octet-stream; name=v6-0001-Enhance-tab-completion-to-ALTER-TYPE-ADD-ATTRIBUT.patchDownload

From aa1808d2b7ea68f18fe0f916fce25a500cfdd42f Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Fri, 1 Nov 2024 17:56:23 +0000
Subject: [PATCH v6 1/4] Enhance tab completion to ALTER TYPE ADD ATTRIBUTE

Now psql will complete
ALTER TYPE <typname> ADD ATTRIBUTE <attname>
with possible datatypes

Author: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-By: Karina Litskevich <litskevichkarina@gmail.com>
---
 src/bin/psql/tab-complete.in.c | 3 +++
 1 file changed, 3 insertions(+)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index bbd08770c3d..d2c7f940008 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2999,6 +2999,9 @@ match_previous_words(int pattern_id,
 	 */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ALTER|DROP|RENAME", "ATTRIBUTE"))
 		COMPLETE_WITH_ATTR(prev3_wd);
+	/* complete ALTER TYPE ADD ATTRIBUTE <foo> with list of types */
+	else if (Matches("ALTER", "TYPE", MatchAny, "ADD", "ATTRIBUTE", MatchAny))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
 	/* ALTER TYPE ALTER ATTRIBUTE <foo> */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ALTER", "ATTRIBUTE", MatchAny))
 		COMPLETE_WITH("TYPE");
-- 
2.34.1

v6-0002-Add-CASCADE-RESRTICT-to-ALTER-TYPE-patterns.patchapplication/octet-stream; name=v6-0002-Add-CASCADE-RESRTICT-to-ALTER-TYPE-patterns.patchDownload

From 5dd7359549dfddbf64195681853ed7974593306d Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Thu, 7 Nov 2024 11:30:18 +0000
Subject: [PATCH v6 2/4] Add CASCADE/RESRTICT to ALTER TYPE patterns

ALTER TYPE ... DROP/ADD/RENAME ATTRIBUTE ... will be completed with CASCADE/RESRTICT
where appropriate.

ALTER TYPE ... ALTER ATTRIBUTE ... TYPE ... will also be completed
with CASCADE/RESRTICT

Author: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-By: Karina Litskevich <litskevichkarina(at)gmail(dot)com>
---
 src/bin/psql/tab-complete.in.c | 12 ++++++++++++
 1 file changed, 12 insertions(+)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index d2c7f940008..79b02047174 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2992,6 +2992,9 @@ match_previous_words(int pattern_id,
 	/* ALTER TYPE xxx RENAME (ATTRIBUTE|VALUE) yyy */
 	else if (Matches("ALTER", "TYPE", MatchAny, "RENAME", "ATTRIBUTE|VALUE", MatchAny))
 		COMPLETE_WITH("TO");
+	/* ALTER TYPE xxx RENAME ATTRIBUTE yyy TO zzz */
+	else if (Matches("ALTER", "TYPE", MatchAny, "RENAME", "ATTRIBUTE", MatchAny, "TO", MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
 
 	/*
 	 * If we have ALTER TYPE <sth> ALTER/DROP/RENAME ATTRIBUTE, provide list
@@ -3002,9 +3005,18 @@ match_previous_words(int pattern_id,
 	/* complete ALTER TYPE ADD ATTRIBUTE <foo> with list of types */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ADD", "ATTRIBUTE", MatchAny))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
+	/* complete ALTER TYPE ADD ATTRIBUTE <foo> <footype> with CASCADE/RESTRICT */
+	else if (Matches("ALTER", "TYPE", MatchAny, "ADD", "ATTRIBUTE", MatchAny, MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
+	/* complete ALTER TYPE DROP ATTRIBUTE <foo> with CASCADE/RESTRICT */
+	else if (Matches("ALTER", "TYPE", MatchAny, "DROP", "ATTRIBUTE", MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
 	/* ALTER TYPE ALTER ATTRIBUTE <foo> */
 	else if (Matches("ALTER", "TYPE", MatchAny, "ALTER", "ATTRIBUTE", MatchAny))
 		COMPLETE_WITH("TYPE");
+	/* ALTER TYPE ALTER ATTRIBUTE <foo> TYPE <footype> */
+	else if (Matches("ALTER", "TYPE", MatchAny, "ALTER", "ATTRIBUTE", MatchAny, "TYPE", MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
 	/* complete ALTER TYPE <sth> RENAME VALUE with list of enum values */
 	else if (Matches("ALTER", "TYPE", MatchAny, "RENAME", "VALUE"))
 		COMPLETE_WITH_ENUM_VALUE(prev3_wd);
-- 
2.34.1

v6-0004-Suggest-table-access-method-for-create-maateriali.patchapplication/octet-stream; name=v6-0004-Suggest-table-access-method-for-create-maateriali.patchDownload

From de8cb36bb97ae0c570ae710a375fc4bf4fdb6e64 Mon Sep 17 00:00:00 2001
From: reshke kirill <reshke@double.cloud>
Date: Mon, 4 Nov 2024 16:22:59 +0000
Subject: [PATCH v6 4/4] Suggest table access method for create maaterialized
 view in tab-complete

Author: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-By: Karina Litskevich <litskevichkarina@gmail.com>
---
 src/bin/psql/tab-complete.in.c | 21 ++++++++++++++++++---
 1 file changed, 18 insertions(+), 3 deletions(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index d1cd7c54f6e..9e23272fc0e 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3981,11 +3981,26 @@ match_previous_words(int pattern_id,
 /* CREATE MATERIALIZED VIEW */
 	else if (Matches("CREATE", "MATERIALIZED"))
 		COMPLETE_WITH("VIEW");
-	/* Complete CREATE MATERIALIZED VIEW <name> with AS */
+	/* Complete CREATE MATERIALIZED VIEW <name> with AS or USING */
 	else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny))
+		COMPLETE_WITH("AS", "USING");
+
+	/*
+	 * Complete CREATE MATERIALIZED VIEW <name> USING with list of access
+	 * methods
+	 */
+	else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING"))
+		COMPLETE_WITH_QUERY(Query_for_list_of_table_access_methods);
+	/* Complete CREATE MATERIALIZED VIEW <name> USING <access method> with AS */
+	else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING", MatchAny))
 		COMPLETE_WITH("AS");
-	/* Complete "CREATE MATERIALIZED VIEW <sth> AS with "SELECT" */
-	else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS"))
+
+	/*
+	 * Complete CREATE MATERIALIZED VIEW <name> [USING <access method> ] AS
+	 * with "SELECT"
+	 */
+	else if (Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "AS") ||
+			 Matches("CREATE", "MATERIALIZED", "VIEW", MatchAny, "USING", MatchAny, "AS"))
 		COMPLETE_WITH("SELECT");
 
 /* CREATE EVENT TRIGGER */
-- 
2.34.1

#12

Tomas Vondra

tomas@vondra.me

about 1 year ago

In reply to: Kirill Reshke (#11)

Re: Add missing tab completion for ALTER TABLE ADD COLUMN IF NOT EXISTS

On 12/8/24 05:22, Kirill Reshke wrote:

On Sun, 8 Dec 2024 at 03:35, Tomas Vondra <tomas@vondra.me> wrote:

Hi,
I took a quick look at this patch series, and it looks generally fine to
me. Barring objections, I'll get it committed. Yes, there's a couple
cosmetic details, and it needs a pgindent run, but I think I can take
care of that ...

Thank you for taking a look.

PFA v6 with last Karina's review comments addressed.

Thanks. Pushed.

I went through the patches, and pushed them with some pretty minor
tweaks. Most of the work was about writing the commit messages. I
considered squashing the commits, but decided not to do that - each of
the 4 patches does tab completion for different command.

regards

--
Tomas Vondra