Thread: [PATCH] Improve tab completion for CREATE TABLE

[PATCH] Improve tab completion for CREATE TABLE

From
ilmari@ilmari.org (Dagfinn Ilmari Mannsåker)
Date:
Hi hackers,

Please find attached a patch that adds the following tab completions for
CREATE TABLE:

- ( or PARTITION OF after the name
- options after the column list
- ON COMMIT actions for temp tables

Regards,

ilmari
-- 
"The surreality of the universe tends towards a maximum" -- Skud's Law
"Never formulate a law or axiom that you're not prepared to live with
 the consequences of."                              -- Skud's Meta-Law

From ed15e53b370d8ee4320961f17ed66cbf60621d28 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Fri, 23 Nov 2018 15:23:21 +0000
Subject: [PATCH] Tab complete more options for CREATE TABLE

- ( or PARTITION OF after the name
- options after the column list
- ON COMMIT actions for temp tables
---
 src/bin/psql/tab-complete.c | 13 +++++++++++++
 1 file changed, 13 insertions(+)

diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 90cc1fe215..61b89c9370 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2412,6 +2412,19 @@ psql_completion(const char *text, int start, int end)
     /* Limited completion support for partition bound specification */
     else if (TailMatches("PARTITION", "OF", MatchAny))
         COMPLETE_WITH("FOR VALUES", "DEFAULT");
+    /* Complete after CREATE TABLE <name> */
+    else if (TailMatches("CREATE", "TABLE", MatchAny) ||
+             TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny))
+        COMPLETE_WITH("(", "PARTITION OF");
+    /* Complete options after CREATE TABLE name (...) */
+    else if (TailMatches("CREATE", "TABLE", MatchAny, "(*)") ||
+             TailMatches("CREATE", "UNLOGGED", "TABLE", MatchAny, "(*)"))
+        COMPLETE_WITH("INHERITS (", "PARTITION BY", "WITH (", "TABLESPACE");
+    else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)"))
+        COMPLETE_WITH("INHERITS (", "PARTITION BY", "WITH (", "ON COMMIT", "TABLESPACE");
+    /* Complete ON COMMIT actions for temp tables */
+    else if (TailMatches("ON", "COMMIT"))
+        COMPLETE_WITH("PRESERVE ROWS", "DELETE ROWS", "DROP");
 
 /* CREATE TABLESPACE */
     else if (Matches("CREATE", "TABLESPACE", MatchAny))
-- 
2.19.2


Re: [PATCH] Improve tab completion for CREATE TABLE

From
ilmari@ilmari.org (Dagfinn Ilmari Mannsåker)
Date:
ilmari@ilmari.org (Dagfinn Ilmari Mannsåker) writes:

> Hi hackers,
>
> Please find attached a patch that adds the following tab completions for
> CREATE TABLE:

Added to the 2019-01 commitfest: https://commitfest.postgresql.org/21/1895/

- ilmari
-- 
"A disappointingly low fraction of the human race is,
 at any given time, on fire." - Stig Sandbeck Mathisen


Re: [PATCH] Improve tab completion for CREATE TABLE

From
Michael Paquier
Date:
On Fri, Nov 30, 2018 at 03:44:38PM +0000, Dagfinn Ilmari Mannsåker wrote:
> ilmari@ilmari.org (Dagfinn Ilmari Mannsåker) writes:
>> Please find attached a patch that adds the following tab completions for
>> CREATE TABLE:
>
> Added to the 2019-01 commitfest: https://commitfest.postgresql.org/21/1895/

+   else if (TailMatches("CREATE", "TABLE", MatchAny) ||
+            TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny))
+       COMPLETE_WITH("(", "PARTITION OF");
This is missing the completion of "OF TYPE" (no need to print the type
matches).

+   else if (TailMatches("CREATE", "TABLE", MatchAny, "(*)") ||
+            TailMatches("CREATE", "UNLOGGED", "TABLE", MatchAny, "(*)"))
+       COMPLETE_WITH("INHERITS (", "PARTITION BY", "WITH (", "TABLESPACE")
Temporary tables can be part of a partition tree, with the parent being
temporary or permanent.

+   /* Complete ON COMMIT actions for temp tables */
+   else if (TailMatches("ON", "COMMIT"))
+       COMPLETE_WITH("PRESERVE ROWS", "DELETE ROWS", "DROP");
This causes ON COMMIT to show up for permanent and unlogged tables.
--
Michael

Attachment

Re: [PATCH] Improve tab completion for CREATE TABLE

From
ilmari@ilmari.org (Dagfinn Ilmari Mannsåker)
Date:
Michael Paquier <michael@paquier.xyz> writes:

> On Fri, Nov 30, 2018 at 03:44:38PM +0000, Dagfinn Ilmari Mannsåker wrote:
>> ilmari@ilmari.org (Dagfinn Ilmari Mannsåker) writes:
>>> Please find attached a patch that adds the following tab completions for
>>> CREATE TABLE:
>> 
>> Added to the 2019-01 commitfest: https://commitfest.postgresql.org/21/1895/
>
> +   else if (TailMatches("CREATE", "TABLE", MatchAny) ||
> +            TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny))
> +       COMPLETE_WITH("(", "PARTITION OF");
>
> This is missing the completion of "OF TYPE" (no need to print the type
> matches).

Good catch.  I've added that, and separate completion of composite type
names after it in the attached v2 patch.

> +   else if (TailMatches("CREATE", "TABLE", MatchAny, "(*)") ||
> +            TailMatches("CREATE", "UNLOGGED", "TABLE", MatchAny, "(*)"))
> +       COMPLETE_WITH("INHERITS (", "PARTITION BY", "WITH (", "TABLESPACE")
>
> Temporary tables can be part of a partition tree, with the parent being
> temporary or permanent.

Yes, the next clause completes all the same options as non-temporary
tables, plus "ON COMMIT":

+   else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)"))
+       COMPLETE_WITH("INHERITS (", "PARTITION BY", "WITH (", "ON COMMIT", "TABLESPACE");

> +   /* Complete ON COMMIT actions for temp tables */
> +   else if (TailMatches("ON", "COMMIT"))
> +       COMPLETE_WITH("PRESERVE ROWS", "DELETE ROWS", "DROP");
>
> This causes ON COMMIT to show up for permanent and unlogged tables.

No, this is for completing _after_ ON COMMIT, which is only suggested by
the clause for temporary tables above.

- ilmari
-- 
"The surreality of the universe tends towards a maximum" -- Skud's Law
"Never formulate a law or axiom that you're not prepared to live with
 the consequences of."                              -- Skud's Meta-Law

From 3aff843fb5c5c608b33a8c9bf6008134c9b75ef5 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Fri, 23 Nov 2018 15:23:21 +0000
Subject: [PATCH v2] Tab complete more options for CREATE TABLE

- ( or OF or PARTITION OF after the name
- composite type names after CREATE TABLE <name> OF
- options after the column list
- actions after ON COMMIT
---
 src/bin/psql/tab-complete.c | 29 +++++++++++++++++++++++++++++
 1 file changed, 29 insertions(+)

diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index fa44b2820b..e7a618b3e3 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -344,6 +344,18 @@ static const SchemaQuery Query_for_list_of_datatypes = {
     .qualresult = "pg_catalog.quote_ident(t.typname)",
 };
 
+static const SchemaQuery Query_for_list_of_composite_datatypes = {
+    .catname = "pg_catalog.pg_type t",
+    /* selcondition --- only get free-standing composite types */
+    .selcondition = "(SELECT c.relkind = " CppAsString2(RELKIND_COMPOSITE_TYPE)
+    " FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid) "
+    "AND t.typname !~ '^_'"
+    .viscondition = "pg_catalog.pg_type_is_visible(t.oid)",
+    .namespace = "t.typnamespace",
+    .result = "pg_catalog.format_type(t.oid, NULL)",
+    .qualresult = "pg_catalog.quote_ident(t.typname)",
+};
+
 static const SchemaQuery Query_for_list_of_domains = {
     .catname = "pg_catalog.pg_type t",
     .selcondition = "t.typtype = 'd'",
@@ -2412,6 +2424,23 @@ psql_completion(const char *text, int start, int end)
     /* Limited completion support for partition bound specification */
     else if (TailMatches("PARTITION", "OF", MatchAny))
         COMPLETE_WITH("FOR VALUES", "DEFAULT");
+    /* Complete after CREATE TABLE <name> */
+    else if (TailMatches("CREATE", "TABLE", MatchAny) ||
+             TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny))
+        COMPLETE_WITH("(", "OF", "PARTITION OF");
+    /* Complete with list of composite types after CREATE TABLE <name> OF */
+    else if (TailMatches("CREATE", "TABLE", MatchAny, "OF") ||
+             TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny, "OF"))
+        COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_composite_datatypes, NULL);
+    /* Complete options after CREATE TABLE name (...) */
+    else if (TailMatches("CREATE", "TABLE", MatchAny, "(*)") ||
+             TailMatches("CREATE", "UNLOGGED", "TABLE", MatchAny, "(*)"))
+        COMPLETE_WITH("INHERITS (", "PARTITION BY", "WITH (", "TABLESPACE");
+    else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)"))
+        COMPLETE_WITH("INHERITS (", "PARTITION BY", "WITH (", "ON COMMIT", "TABLESPACE");
+    /* Complete ON COMMIT actions for temp tables */
+    else if (TailMatches("ON", "COMMIT"))
+        COMPLETE_WITH("PRESERVE ROWS", "DELETE ROWS", "DROP");
 
 /* CREATE TABLESPACE */
     else if (Matches("CREATE", "TABLESPACE", MatchAny))
-- 
2.20.1


Re: [PATCH] Improve tab completion for CREATE TABLE

From
ilmari@ilmari.org (Dagfinn Ilmari Mannsåker)
Date:
ilmari@ilmari.org (Dagfinn Ilmari Mannsåker) writes:

> Michael Paquier <michael@paquier.xyz> writes:
>
>> On Fri, Nov 30, 2018 at 03:44:38PM +0000, Dagfinn Ilmari Mannsåker wrote:
>>> ilmari@ilmari.org (Dagfinn Ilmari Mannsåker) writes:
>>>> Please find attached a patch that adds the following tab completions for
>>>> CREATE TABLE:
>>> 
>>> Added to the 2019-01 commitfest: https://commitfest.postgresql.org/21/1895/
>>
>> +   else if (TailMatches("CREATE", "TABLE", MatchAny) ||
>> +            TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny))
>> +       COMPLETE_WITH("(", "PARTITION OF");
>>
>> This is missing the completion of "OF TYPE" (no need to print the type
>> matches).
>
> Good catch.  I've added that, and separate completion of composite type
> names after it in the attached v2 patch.

Oops, that had a typo after I fiddled with the formatting of the
selcondition in the query and forgot to compile check it.  Fixed v3
patch attached.

Another omission I just realised of is that it doesn't complete the list
of table storage options after after "WITH (".  That should be fairly
easy to add (we already have the list for completing after ALTER TABLE
<name> SET|RESET), but it's getting late here now.

Also, when there are multiple things that can all appear in any order in
a given place in the grammar, it completes them all there, but it
doesn't complete the rest after you've typed one one. E.g. it won't
complete WITH or any of the other options after CREATE TABLE <name> (
... ) TABLESPACE <name>.  This seems like a more involved change, and
probably a more widespread problem, so I'm tempted to leave that for
another patch.

- ilmari 
-- 
"A disappointingly low fraction of the human race is,
 at any given time, on fire." - Stig Sandbeck Mathisen

From 3aff843fb5c5c608b33a8c9bf6008134c9b75ef5 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Fri, 23 Nov 2018 15:23:21 +0000
Subject: [PATCH v3] Tab complete more options for CREATE TABLE

- ( or OF or PARTITION OF after the name
- composite type names after CREATE TABLE <name> OF
- options after the column list
- actions after ON COMMIT
---
 src/bin/psql/tab-complete.c | 29 +++++++++++++++++++++++++++++
 1 file changed, 29 insertions(+)

diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index fa44b2820b..e7a618b3e3 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -344,6 +344,18 @@ static const SchemaQuery Query_for_list_of_datatypes = {
     .qualresult = "pg_catalog.quote_ident(t.typname)",
 };
 
+static const SchemaQuery Query_for_list_of_composite_datatypes = {
+    .catname = "pg_catalog.pg_type t",
+    /* selcondition --- only get free-standing composite types */
+    .selcondition = "(SELECT c.relkind = " CppAsString2(RELKIND_COMPOSITE_TYPE)
+    " FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid) "
+    "AND t.typname !~ '^_'"
+    .viscondition = "pg_catalog.pg_type_is_visible(t.oid)",
+    .namespace = "t.typnamespace",
+    .result = "pg_catalog.format_type(t.oid, NULL)",
+    .qualresult = "pg_catalog.quote_ident(t.typname)",
+};
+
 static const SchemaQuery Query_for_list_of_domains = {
     .catname = "pg_catalog.pg_type t",
     .selcondition = "t.typtype = 'd'",
@@ -2412,6 +2424,23 @@ psql_completion(const char *text, int start, int end)
     /* Limited completion support for partition bound specification */
     else if (TailMatches("PARTITION", "OF", MatchAny))
         COMPLETE_WITH("FOR VALUES", "DEFAULT");
+    /* Complete after CREATE TABLE <name> */
+    else if (TailMatches("CREATE", "TABLE", MatchAny) ||
+             TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny))
+        COMPLETE_WITH("(", "OF", "PARTITION OF");
+    /* Complete with list of composite types after CREATE TABLE <name> OF */
+    else if (TailMatches("CREATE", "TABLE", MatchAny, "OF") ||
+             TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny, "OF"))
+        COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_composite_datatypes, NULL);
+    /* Complete options after CREATE TABLE name (...) */
+    else if (TailMatches("CREATE", "TABLE", MatchAny, "(*)") ||
+             TailMatches("CREATE", "UNLOGGED", "TABLE", MatchAny, "(*)"))
+        COMPLETE_WITH("INHERITS (", "PARTITION BY", "WITH (", "TABLESPACE");
+    else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)"))
+        COMPLETE_WITH("INHERITS (", "PARTITION BY", "WITH (", "ON COMMIT", "TABLESPACE");
+    /* Complete ON COMMIT actions for temp tables */
+    else if (TailMatches("ON", "COMMIT"))
+        COMPLETE_WITH("PRESERVE ROWS", "DELETE ROWS", "DROP");
 
 /* CREATE TABLESPACE */
     else if (Matches("CREATE", "TABLESPACE", MatchAny))
-- 
2.20.1


Re: [PATCH] Improve tab completion for CREATE TABLE

From
Michael Paquier
Date:
On Wed, Dec 19, 2018 at 11:22:29PM +0000, Dagfinn Ilmari Mannsåker wrote:
> Michael Paquier <michael@paquier.xyz> writes:
>> +   /* Complete ON COMMIT actions for temp tables */
>> +   else if (TailMatches("ON", "COMMIT"))
>> +       COMPLETE_WITH("PRESERVE ROWS", "DELETE ROWS", "DROP");
>>
>> This causes ON COMMIT to show up for permanent and unlogged tables.
>
> No, this is for completing _after_ ON COMMIT, which is only suggested by
> the clause for temporary tables above.

If a user types "CREATE TABLE foo () ON COMMIT" by himself and then asks
for tab completion then he would get the suggestion, which is incorrect?
--
Michael

Attachment

Re: [PATCH] Improve tab completion for CREATE TABLE

From
ilmari@ilmari.org (Dagfinn Ilmari Mannsåker)
Date:
Michael Paquier <michael@paquier.xyz> writes:

> On Wed, Dec 19, 2018 at 11:22:29PM +0000, Dagfinn Ilmari Mannsåker wrote:
>> Michael Paquier <michael@paquier.xyz> writes:
>>> +   /* Complete ON COMMIT actions for temp tables */
>>> +   else if (TailMatches("ON", "COMMIT"))
>>> +       COMPLETE_WITH("PRESERVE ROWS", "DELETE ROWS", "DROP");
>>>
>>> This causes ON COMMIT to show up for permanent and unlogged tables.
>> 
>> No, this is for completing _after_ ON COMMIT, which is only suggested by
>> the clause for temporary tables above.
>
> If a user types "CREATE TABLE foo () ON COMMIT" by himself and then asks
> for tab completion then he would get the suggestion, which is incorrect?

Point, fixed in the attached v4.  OTOH, as I mentioned in my other
email, that runs into the problem that it won't complete the actions
after e.g.  "CREATE TEMP TABLE FOO () WITH () ON COMMIT".

-- 
- Twitter seems more influential [than blogs] in the 'gets reported in
  the mainstream press' sense at least.               - Matt McLeod
- That'd be because the content of a tweet is easier to condense down
  to a mainstream media article.                      - Calle Dybedahl

From 294480116f52ba96ad10ed49850ffc3785e1a43c Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Fri, 23 Nov 2018 15:23:21 +0000
Subject: [PATCH v4] Tab complete more options for CREATE TABLE

- ( or OF or PARTITION OF after the name
- composite type names after CREATE TABLE <name> OF
- options after the column list
- actions after ON COMMIT
---
 src/bin/psql/tab-complete.c | 29 +++++++++++++++++++++++++++++
 1 file changed, 29 insertions(+)

diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index fa44b2820b..2996c8e68f 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -344,6 +344,18 @@ static const SchemaQuery Query_for_list_of_datatypes = {
     .qualresult = "pg_catalog.quote_ident(t.typname)",
 };
 
+static const SchemaQuery Query_for_list_of_composite_datatypes = {
+    .catname = "pg_catalog.pg_type t",
+    /* selcondition --- only get free-standing composite types */
+    .selcondition = "(SELECT c.relkind = " CppAsString2(RELKIND_COMPOSITE_TYPE)
+    " FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid) "
+    "AND t.typname !~ '^_'",
+    .viscondition = "pg_catalog.pg_type_is_visible(t.oid)",
+    .namespace = "t.typnamespace",
+    .result = "pg_catalog.format_type(t.oid, NULL)",
+    .qualresult = "pg_catalog.quote_ident(t.typname)",
+};
+
 static const SchemaQuery Query_for_list_of_domains = {
     .catname = "pg_catalog.pg_type t",
     .selcondition = "t.typtype = 'd'",
@@ -2412,6 +2424,23 @@ psql_completion(const char *text, int start, int end)
     /* Limited completion support for partition bound specification */
     else if (TailMatches("PARTITION", "OF", MatchAny))
         COMPLETE_WITH("FOR VALUES", "DEFAULT");
+    /* Complete after CREATE TABLE <name> */
+    else if (TailMatches("CREATE", "TABLE", MatchAny) ||
+             TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny))
+        COMPLETE_WITH("(", "OF", "PARTITION OF");
+    /* Complete with list of composite types after CREATE TABLE <name> OF */
+    else if (TailMatches("CREATE", "TABLE", MatchAny, "OF") ||
+             TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny, "OF"))
+        COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_composite_datatypes, NULL);
+    /* Complete options after CREATE TABLE name (...) */
+    else if (TailMatches("CREATE", "TABLE", MatchAny, "(*)") ||
+             TailMatches("CREATE", "UNLOGGED", "TABLE", MatchAny, "(*)"))
+        COMPLETE_WITH("INHERITS (", "PARTITION BY", "WITH (", "TABLESPACE");
+    else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)"))
+        COMPLETE_WITH("INHERITS (", "PARTITION BY", "WITH (", "ON COMMIT", "TABLESPACE");
+    /* Complete ON COMMIT actions for temp tables */
+    else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)", "ON", "COMMIT"))
+        COMPLETE_WITH("PRESERVE ROWS", "DELETE ROWS", "DROP");
 
 /* CREATE TABLESPACE */
     else if (Matches("CREATE", "TABLESPACE", MatchAny))
-- 
2.20.1


Re: [PATCH] Improve tab completion for CREATE TABLE

From
Michael Paquier
Date:
On Thu, Dec 20, 2018 at 12:02:52AM +0000, Dagfinn Ilmari Mannsåker wrote:
> Point, fixed in the attached v4.  OTOH, as I mentioned in my other
> email, that runs into the problem that it won't complete the actions
> after e.g.  "CREATE TEMP TABLE FOO () WITH () ON COMMIT".

I am fine to do that later on if that's adapted, one complication being
that the completion is dependent on the order of the clauses for CREATE
TABLE as we need something compatible with CREATE TABLE commands bundled
with CREATE SCHEMA calls so only tail checks can be done.

So committed your last version after some comment tweaks and reordering
the entries in alphabetical order.
--
Michael

Attachment

Re: [PATCH] Improve tab completion for CREATE TABLE

From
ilmari@ilmari.org (Dagfinn Ilmari Mannsåker)
Date:
Michael Paquier <michael@paquier.xyz> writes:

> On Thu, Dec 20, 2018 at 12:02:52AM +0000, Dagfinn Ilmari Mannsåker wrote:
>> Point, fixed in the attached v4.  OTOH, as I mentioned in my other
>> email, that runs into the problem that it won't complete the actions
>> after e.g.  "CREATE TEMP TABLE FOO () WITH () ON COMMIT".
>
> I am fine to do that later on if that's adapted, one complication being
> that the completion is dependent on the order of the clauses for CREATE
> TABLE as we need something compatible with CREATE TABLE commands bundled
> with CREATE SCHEMA calls so only tail checks can be done.

Yeah, because of that we can't do the obvious HeadMatches("CREATE",
"TABLE") && (TailMatches(...) || TailMatches(...) || ...).  I believe
this would require extending the match syntax with regex-like grouping,
alternation and repetition operators, which I'm not volunteering to do.

> So committed your last version after some comment tweaks and reordering
> the entries in alphabetical order.

Thanks!

- ilmari
-- 
"The surreality of the universe tends towards a maximum" -- Skud's Law
"Never formulate a law or axiom that you're not prepared to live with
 the consequences of."                              -- Skud's Meta-Law


Re: [PATCH] Improve tab completion for CREATE TABLE

From
ilmari@ilmari.org (Dagfinn Ilmari Mannsåker)
Date:
I wrote:
> Another omission I just realised of is that it doesn't complete the list
> of table storage options after after "WITH (".  That should be fairly
> easy to add (we already have the list for completing after ALTER TABLE
> <name> SET|RESET), but it's getting late here now.

Here's a patch that does this (and in passing alphabetises the list of
options).

- ilmari
-- 
- Twitter seems more influential [than blogs] in the 'gets reported in
  the mainstream press' sense at least.               - Matt McLeod
- That'd be because the content of a tweet is easier to condense down
  to a mainstream media article.                      - Calle Dybedahl

From 5f948f2ebff03a89d18ab621bc21d03cfaa07529 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Fri, 21 Dec 2018 15:03:19 +0000
Subject: [PATCH] Add completion for CREATE TABLE ... WITH options

Move the list of options from the "ALTER TABLE <foo> SET|RESET (" block
to the top-level, and reuse it after "CREATE TABLE <foo> ( ... ) WITH (".

In passing, alphabetise the option list properly.
---
 src/bin/psql/tab-complete.c | 74 ++++++++++++++++++-------------------
 1 file changed, 37 insertions(+), 37 deletions(-)

diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 5ba6ffba8c..074c88378b 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1005,6 +1005,40 @@ static const pgsql_thing_t words_after_create[] = {
     {NULL}                        /* end of list */
 };
 
+static const char *const list_TABLEOPTIONS[] = {
+    "autovacuum_analyze_scale_factor",
+    "autovacuum_analyze_threshold",
+    "autovacuum_enabled",
+    "autovacuum_freeze_max_age",
+    "autovacuum_freeze_min_age",
+    "autovacuum_freeze_table_age",
+    "autovacuum_multixact_freeze_max_age",
+    "autovacuum_multixact_freeze_min_age",
+    "autovacuum_multixact_freeze_table_age",
+    "autovacuum_vacuum_cost_delay",
+    "autovacuum_vacuum_cost_limit",
+    "autovacuum_vacuum_scale_factor",
+    "autovacuum_vacuum_threshold",
+    "fillfactor",
+    "log_autovacuum_min_duration",
+    "parallel_workers",
+    "toast.autovacuum_enabled",
+    "toast.autovacuum_freeze_max_age",
+    "toast.autovacuum_freeze_min_age",
+    "toast.autovacuum_freeze_table_age",
+    "toast.autovacuum_multixact_freeze_max_age",
+    "toast.autovacuum_multixact_freeze_min_age",
+    "toast.autovacuum_multixact_freeze_table_age",
+    "toast.autovacuum_vacuum_cost_delay",
+    "toast.autovacuum_vacuum_cost_limit",
+    "toast.autovacuum_vacuum_scale_factor",
+    "toast.autovacuum_vacuum_threshold",
+    "toast.log_autovacuum_min_duration",
+    "toast_tuple_target",
+    "user_catalog_table",
+    NULL
+};
+
 
 /* Forward declaration of functions */
 static char **psql_completion(const char *text, int start, int end);
@@ -1904,44 +1938,7 @@ psql_completion(const char *text, int start, int end)
         COMPLETE_WITH("(");
     /* ALTER TABLE <foo> SET|RESET ( */
     else if (Matches("ALTER", "TABLE", MatchAny, "SET|RESET", "("))
-    {
-        static const char *const list_TABLEOPTIONS[] =
-        {
-            "autovacuum_analyze_scale_factor",
-            "autovacuum_analyze_threshold",
-            "autovacuum_enabled",
-            "autovacuum_freeze_max_age",
-            "autovacuum_freeze_min_age",
-            "autovacuum_freeze_table_age",
-            "autovacuum_multixact_freeze_max_age",
-            "autovacuum_multixact_freeze_min_age",
-            "autovacuum_multixact_freeze_table_age",
-            "autovacuum_vacuum_cost_delay",
-            "autovacuum_vacuum_cost_limit",
-            "autovacuum_vacuum_scale_factor",
-            "autovacuum_vacuum_threshold",
-            "fillfactor",
-            "parallel_workers",
-            "log_autovacuum_min_duration",
-            "toast_tuple_target",
-            "toast.autovacuum_enabled",
-            "toast.autovacuum_freeze_max_age",
-            "toast.autovacuum_freeze_min_age",
-            "toast.autovacuum_freeze_table_age",
-            "toast.autovacuum_multixact_freeze_max_age",
-            "toast.autovacuum_multixact_freeze_min_age",
-            "toast.autovacuum_multixact_freeze_table_age",
-            "toast.autovacuum_vacuum_cost_delay",
-            "toast.autovacuum_vacuum_cost_limit",
-            "toast.autovacuum_vacuum_scale_factor",
-            "toast.autovacuum_vacuum_threshold",
-            "toast.log_autovacuum_min_duration",
-            "user_catalog_table",
-            NULL
-        };
-
         COMPLETE_WITH_LIST(list_TABLEOPTIONS);
-    }
     else if (Matches("ALTER", "TABLE", MatchAny, "REPLICA", "IDENTITY", "USING", "INDEX"))
     {
         completion_info_charp = prev5_wd;
@@ -2439,6 +2436,9 @@ psql_completion(const char *text, int start, int end)
     else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)"))
         COMPLETE_WITH("INHERITS (", "ON COMMIT", "PARTITION BY",
                       "TABLESPACE", "WITH (");
+    else if (TailMatches("CREATE", "TABLE", MatchAny, "(*)", "WITH", "(") ||
+             TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny, "(*)", "WITH", "("))
+        COMPLETE_WITH_LIST(list_TABLEOPTIONS);
     /* Complete CREATE TABLE ON COMMIT with actions */
     else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)", "ON", "COMMIT"))
         COMPLETE_WITH("DELETE ROWS", "DROP", "PRESERVE ROWS");
-- 
2.20.1


Re: [PATCH] Improve tab completion for CREATE TABLE

From
Michael Paquier
Date:
On Fri, Dec 21, 2018 at 01:57:40PM +0000, Dagfinn Ilmari Mannsåker wrote:
> Yeah, because of that we can't do the obvious HeadMatches("CREATE",
> "TABLE") && (TailMatches(...) || TailMatches(...) || ...).  I believe
> this would require extending the match syntax with regex-like grouping,
> alternation and repetition operators, which I'm not volunteering to
> do.

That seems to be a lot of work for little benefit as few queries are
able to work within CREATE SCHEMA, so I would not take this road.
--
Michael

Attachment

Re: [PATCH] Improve tab completion for CREATE TABLE

From
Michael Paquier
Date:
On Fri, Dec 21, 2018 at 03:14:36PM +0000, Dagfinn Ilmari Mannsåker wrote:
> Here's a patch that does this (and in passing alphabetises the list of
> options).

Cool, thanks.  The position of the option list is fine.  However
list_TABLEOPTIONS is not a name consistent with the surroundings.  So
we could just use table_level_option?  Reordering them is a good idea,
log_autovacuum_min_duration being the bad entry.
--
Michael

Attachment

Re: [PATCH] Improve tab completion for CREATE TABLE

From
ilmari@ilmari.org (Dagfinn Ilmari Mannsåker)
Date:
Michael Paquier <michael@paquier.xyz> writes:

> On Fri, Dec 21, 2018 at 03:14:36PM +0000, Dagfinn Ilmari Mannsåker wrote:
>> Here's a patch that does this (and in passing alphabetises the list of
>> options).
>
> Cool, thanks.  The position of the option list is fine.  However
> list_TABLEOPTIONS is not a name consistent with the surroundings.  So
> we could just use table_level_option?

The CREATE and ALTER TABLE documentation calls them storage parameters,
so I've gone for table_storage_parameters in the attached v2 patch.

> Reordering them is a good idea, log_autovacuum_min_duration being the
> bad entry.

toast_tuple_target was also on the wrong side of the toast.* options.

- ilmari
-- 
- Twitter seems more influential [than blogs] in the 'gets reported in
  the mainstream press' sense at least.               - Matt McLeod
- That'd be because the content of a tweet is easier to condense down
  to a mainstream media article.                      - Calle Dybedahl

From 59f30579b72106cf14338b890acfd0c6f0b6009a Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Fri, 21 Dec 2018 15:03:19 +0000
Subject: [PATCH v2] =?UTF-8?q?Add=20completion=20for=20storage=20parameter?=
 =?UTF-8?q?s=20after=20CREATE=20TABLE=20=E2=80=A6=20WITH=20=E2=80=A6?=
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Move the list of parameters from the "ALTER TABLE <foo> SET|RESET ("
block to the top-level, and reuse it after "CREATE TABLE <foo> ( … )
WITH (".

In passing, rename the variable to and alphabetise the list properly.
---
 src/bin/psql/tab-complete.c | 76 ++++++++++++++++++-------------------
 1 file changed, 38 insertions(+), 38 deletions(-)

diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 5ba6ffba8c..b12ffab312 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1005,6 +1005,40 @@ static const pgsql_thing_t words_after_create[] = {
     {NULL}                        /* end of list */
 };
 
+static const char *const table_storage_parameters[] = {
+    "autovacuum_analyze_scale_factor",
+    "autovacuum_analyze_threshold",
+    "autovacuum_enabled",
+    "autovacuum_freeze_max_age",
+    "autovacuum_freeze_min_age",
+    "autovacuum_freeze_table_age",
+    "autovacuum_multixact_freeze_max_age",
+    "autovacuum_multixact_freeze_min_age",
+    "autovacuum_multixact_freeze_table_age",
+    "autovacuum_vacuum_cost_delay",
+    "autovacuum_vacuum_cost_limit",
+    "autovacuum_vacuum_scale_factor",
+    "autovacuum_vacuum_threshold",
+    "fillfactor",
+    "log_autovacuum_min_duration",
+    "parallel_workers",
+    "toast.autovacuum_enabled",
+    "toast.autovacuum_freeze_max_age",
+    "toast.autovacuum_freeze_min_age",
+    "toast.autovacuum_freeze_table_age",
+    "toast.autovacuum_multixact_freeze_max_age",
+    "toast.autovacuum_multixact_freeze_min_age",
+    "toast.autovacuum_multixact_freeze_table_age",
+    "toast.autovacuum_vacuum_cost_delay",
+    "toast.autovacuum_vacuum_cost_limit",
+    "toast.autovacuum_vacuum_scale_factor",
+    "toast.autovacuum_vacuum_threshold",
+    "toast.log_autovacuum_min_duration",
+    "toast_tuple_target",
+    "user_catalog_table",
+    NULL
+};
+
 
 /* Forward declaration of functions */
 static char **psql_completion(const char *text, int start, int end);
@@ -1904,44 +1938,7 @@ psql_completion(const char *text, int start, int end)
         COMPLETE_WITH("(");
     /* ALTER TABLE <foo> SET|RESET ( */
     else if (Matches("ALTER", "TABLE", MatchAny, "SET|RESET", "("))
-    {
-        static const char *const list_TABLEOPTIONS[] =
-        {
-            "autovacuum_analyze_scale_factor",
-            "autovacuum_analyze_threshold",
-            "autovacuum_enabled",
-            "autovacuum_freeze_max_age",
-            "autovacuum_freeze_min_age",
-            "autovacuum_freeze_table_age",
-            "autovacuum_multixact_freeze_max_age",
-            "autovacuum_multixact_freeze_min_age",
-            "autovacuum_multixact_freeze_table_age",
-            "autovacuum_vacuum_cost_delay",
-            "autovacuum_vacuum_cost_limit",
-            "autovacuum_vacuum_scale_factor",
-            "autovacuum_vacuum_threshold",
-            "fillfactor",
-            "parallel_workers",
-            "log_autovacuum_min_duration",
-            "toast_tuple_target",
-            "toast.autovacuum_enabled",
-            "toast.autovacuum_freeze_max_age",
-            "toast.autovacuum_freeze_min_age",
-            "toast.autovacuum_freeze_table_age",
-            "toast.autovacuum_multixact_freeze_max_age",
-            "toast.autovacuum_multixact_freeze_min_age",
-            "toast.autovacuum_multixact_freeze_table_age",
-            "toast.autovacuum_vacuum_cost_delay",
-            "toast.autovacuum_vacuum_cost_limit",
-            "toast.autovacuum_vacuum_scale_factor",
-            "toast.autovacuum_vacuum_threshold",
-            "toast.log_autovacuum_min_duration",
-            "user_catalog_table",
-            NULL
-        };
-
-        COMPLETE_WITH_LIST(list_TABLEOPTIONS);
-    }
+        COMPLETE_WITH_LIST(table_storage_parameters);
     else if (Matches("ALTER", "TABLE", MatchAny, "REPLICA", "IDENTITY", "USING", "INDEX"))
     {
         completion_info_charp = prev5_wd;
@@ -2439,6 +2436,9 @@ psql_completion(const char *text, int start, int end)
     else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)"))
         COMPLETE_WITH("INHERITS (", "ON COMMIT", "PARTITION BY",
                       "TABLESPACE", "WITH (");
+    else if (TailMatches("CREATE", "TABLE", MatchAny, "(*)", "WITH", "(") ||
+             TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny, "(*)", "WITH", "("))
+        COMPLETE_WITH_LIST(table_storage_parameters);
     /* Complete CREATE TABLE ON COMMIT with actions */
     else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)", "ON", "COMMIT"))
         COMPLETE_WITH("DELETE ROWS", "DROP", "PRESERVE ROWS");
-- 
2.20.1


Re: [PATCH] Improve tab completion for CREATE TABLE

From
Michael Paquier
Date:
On Sat, Dec 22, 2018 at 01:33:23PM +0000, Dagfinn Ilmari Mannsåker wrote:
> The CREATE and ALTER TABLE documentation calls them storage parameters,
> so I've gone for table_storage_parameters in the attached v2 patch.

Sold.  And committed.

>> Reordering them is a good idea, log_autovacuum_min_duration being the
>> bad entry.
>
> toast_tuple_target was also on the wrong side of the toast.*
> options.

Indeed.
--
Michael

Attachment

Re: [PATCH] Improve tab completion for CREATE TABLE

From
ilmari@ilmari.org (Dagfinn Ilmari Mannsåker)
Date:
Michael Paquier <michael@paquier.xyz> writes:

> On Sat, Dec 22, 2018 at 01:33:23PM +0000, Dagfinn Ilmari Mannsåker wrote:
>> The CREATE and ALTER TABLE documentation calls them storage parameters,
>> so I've gone for table_storage_parameters in the attached v2 patch.
>
> Sold.  And committed.

Thanks again!

- ilmari
-- 
"The surreality of the universe tends towards a maximum" -- Skud's Law
"Never formulate a law or axiom that you're not prepared to live with
 the consequences of."                              -- Skud's Meta-Law