Re: [PATCH] Improve tab completion for CREATE TABLE - Mailing list pgsql-hackers

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