Hi all!

Attached is the third version of my patch that adds/fixes several things to/in the psql-tabcomplete code.
This diff includes the still missing tab-complete support for TABLESPACE I already sent earlier.
New in this version of the patch is a small adaption of the tab-complete code to support the adjusted SAVEPOINT-Syntax commited by Tom, as well as completion of the only half working (and I think only by accident) tabcomplete-suppport for "BEGIN [ TRANSACTION | WORK ]".


As I already stated earlier I'm by no means a programmer, and I would love to get at least some feedback if there is even the slightest interest in(or since some of the changes may qualify as feature enhancements, most of this is not 8.0 material).

below is a complete list of the things I have changed with this patch:


*) add tablespace support for CREATE/DROP/ALTER and \db
*) sync the list of possible commands following ALTER with the docs (by adding AGGREGATE,CONVERSATION,DOMAIN,FUNCTION,LANGUAGE,OPERATOR,SEQUENCE,TABLESPACE and TYPE)
*) provide a list of valid users after "OWNER TO"
*) tab-complete support for ALTER (AGGREGATE|CONVERSION|FUNCTION)
*) basic tab-complete support for ALTER DOMAIN
*) provide a list of suitable indexes following ALTER TABLE <sth> CLUSTER ON(?)
*) add "CLUSTER ON" and "SET" to the ALTER TABLE <sth> - tab-complete list(fixes incorrect/wrong tab-complete with ALTER TABLE <sth> SET +<TAB> too)
*) provide a list of possible indexes following ALTER TABLE <sth> CLUSTER ON
*) provide list of possible commands(WITHOUT CLUSTER,WITHOUT OIDS, TABLESPACE) following ALTER TABLE <sth> SET
*) sync "COMMENT ON" with docs by adding "CAST","CONVERSION","FUNCTION"
*) add ABSOLUT to the list of possible commands after FETCH
*) "END" was missing from the sql-commands overview (though it had completion support!) - i know it's depreciated but we have ABORT and others still in ...
*) fixes small buglet with ALTER (TRIGGER|CLUSTER) ON autocomplete (CLUSTER ON +<TAB> would produce CLUSTER ON ON - same for TRIGGER ON)
*) adapt to new SAVEPOINT syntax
*) fix incomplete Support for BEGIN [ TRANSACTION | WORK ]


and some random things I noticed that are either still missing or need some thought:

*) provide a list of conversions after ALTER CONVERSION (?)
*) tabcomplete-support for ALTER SEQUENCE
*) add RENAME TO to ALTER TRIGGER
*) tab-completesupport for ALTER USER
*) fix ALTER (GROUP|DOMAIN|...) <sth> DROP - autocomplete
*) RENAME TO support for ALTER LANGUAGE <sth>
*) more complete support for COPY
*) more complete ALTER TABLE - support



Stefan
Index: src/bin/psql/tab-complete.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/tab-complete.c,v
retrieving revision 1.109
diff -u -r1.109 tab-complete.c
--- src/bin/psql/tab-complete.c 28 Jul 2004 14:23:30 -0000      1.109
+++ src/bin/psql/tab-complete.c 14 Aug 2004 18:42:11 -0000
@@ -328,6 +328,10 @@
 "SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database "\
 " WHERE substring(pg_catalog.quote_ident(datname),1,%d)='%s'"
 
+#define Query_for_list_of_tablespaces \
+"SELECT pg_catalog.quote_ident(spcname) FROM pg_catalog.pg_tablespace "\
+" WHERE substring(pg_catalog.quote_ident(spcname),1,%d)='%s'"
+
 #define Query_for_list_of_encodings \
 " SELECT DISTINCT pg_catalog.pg_encoding_to_char(conforencoding) "\
 "   FROM pg_catalog.pg_conversion "\
@@ -365,6 +369,15 @@
 "       and pg_catalog.quote_ident(c2.relname)='%s'"\
 "       and pg_catalog.pg_table_is_visible(c2.oid)"
 
+/* the silly-looking length condition is just to eat up the current word */
+#define Query_for_index_of_table \
+"SELECT pg_catalog.quote_ident(c2.relname) "\
+"  FROM pg_catalog.pg_class c1, pg_catalog.pg_class c2, pg_catalog.pg_index i"\
+" WHERE c1.oid=i.indrelid and i.indexrelid=c2.oid"\
+"       and (%d = length('%s'))"\
+"       and pg_catalog.quote_ident(c1.relname)='%s'"\
+"       and pg_catalog.pg_table_is_visible(c2.oid)"
+
 /*
  * This is a list of all "things" in Pgsql, which can show up after CREATE or
  * DROP; and there is also a query to get a list of them.
@@ -394,6 +407,7 @@
        {"SCHEMA", Query_for_list_of_schemas},
        {"SEQUENCE", NULL, &Query_for_list_of_sequences},
        {"TABLE", NULL, &Query_for_list_of_tables},
+       {"TABLESPACE", Query_for_list_of_tablespaces},
        {"TEMP", NULL, NULL},   /* for CREATE TEMP TABLE ... */
        {"TRIGGER", "SELECT pg_catalog.quote_ident(tgname) FROM pg_catalog.pg_trigger 
WHERE substring(pg_catalog.quote_ident(tgname),1,%d)='%s'"},
        {"TYPE", NULL, &Query_for_list_of_datatypes},
@@ -461,7 +475,7 @@
 
        static const char * const sql_commands[] = {
                "ABORT", "ALTER", "ANALYZE", "BEGIN", "CHECKPOINT", "CLOSE", 
"CLUSTER", "COMMENT",
-               "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE", "DELETE", "DROP", 
"EXECUTE",
+               "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE", "DELETE", "DROP", 
"END", "EXECUTE",
                "EXPLAIN", "FETCH", "GRANT", "INSERT", "LISTEN", "LOAD", "LOCK", 
"MOVE", "NOTIFY",
                "PREPARE", "REINDEX", "RELEASE", "RESET", "REVOKE", "ROLLBACK", 
"SAVEPOINT", 
                 "SELECT", "SET", "SHOW", "START", "TRUNCATE", "UNLISTEN", "UPDATE", 
"VACUUM", NULL
@@ -575,9 +589,9 @@
 
        static const char * const backslash_commands[] = {
                "\\a", "\\connect", "\\C", "\\cd", "\\copy", "\\copyright",
-               "\\d", "\\da", "\\dc", "\\dC", "\\dd", "\\dD", "\\df", "\\dg", "\\di",
-               "\\dl", "\\dn", "\\do", "\\dp", "\\ds", "\\dS", "\\dt", "\\dT",
-               "\\dv", "\\du",
+               "\\d", "\\da", "\\db", "\\dc", "\\dC", "\\dd", "\\dD", "\\df",
+               "\\dg", "\\di", "\\dl", "\\dn", "\\do", "\\dp", "\\ds", "\\dS",
+               "\\dt", "\\dT", "\\dv", "\\du",
                "\\e", "\\echo", "\\encoding",
                "\\f", "\\g", "\\h", "\\help", "\\H", "\\i", "\\l",
                "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
@@ -632,10 +646,25 @@
                         pg_strcasecmp(prev3_wd, "TABLE") != 0)
        {
                static const char *const list_ALTER[] =
-               {"DATABASE", "GROUP", "SCHEMA", "TABLE", "TRIGGER", "USER", NULL};
+               {"AGGREGATE", "CONVERSATION", "DATABASE","DOMAIN", "FUNCTION",
+               "GROUP", "LANGUAGE", "OPERATOR", "SCHEMA", "SEQUENCE", "TABLE",
+               "TABLESPACE", "TRIGGER", "TYPE", "USER", NULL};
 
                COMPLETE_WITH_LIST(list_ALTER);
        }
+       
+       /* ALTER AGGREGATE,CONVERSION,FUNCTION,SCHEMA <name> */
+       else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
+                        (pg_strcasecmp(prev2_wd, "AGGREGATE") == 0 ||
+                       pg_strcasecmp(prev2_wd, "CONVERSION") == 0 ||
+                       pg_strcasecmp(prev2_wd, "FUNCTION") == 0 ||
+                       pg_strcasecmp(prev2_wd, "SCHEMA") == 0 ))
+       {
+               static const char *const list_ALTERGEN[] =
+               {"OWNER TO", "RENAME TO", NULL};
+
+               COMPLETE_WITH_LIST(list_ALTERGEN);
+       }
 
        /* ALTER DATABASE <name> */
        else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
@@ -646,9 +675,39 @@
 
                COMPLETE_WITH_LIST(list_ALTERDATABASE);
        }
+       /* ALTER DOMAIN <name> */
+       else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
+                        pg_strcasecmp(prev2_wd, "DOMAIN") == 0)
+       {
+               static const char *const list_ALTERDOMAIN[] =
+               {"ADD", "DROP", "OWNER TO", "SET", NULL};
+
+               COMPLETE_WITH_LIST(list_ALTERDOMAIN);
+       }
+       /* ALTER DOMAIN <sth> DROP */
+       else if (pg_strcasecmp(prev4_wd, "ALTER") == 0 &&
+                        pg_strcasecmp(prev3_wd, "DOMAIN") == 0 &&
+                        pg_strcasecmp(prev_wd, "DROP") == 0)
+       {
+               static const char *const list_ALTERDOMAIN2[] =
+               {"CONSTRAINT", "DEFAULT", "NOT NULL", "OWNER TO", NULL};
+
+               COMPLETE_WITH_LIST(list_ALTERDOMAIN2);
+       }
+       /* ALTER DOMAIN <sth> SET */
+       else if (pg_strcasecmp(prev4_wd, "ALTER") == 0 &&
+                        pg_strcasecmp(prev3_wd, "DOMAIN") == 0 &&
+                        pg_strcasecmp(prev_wd, "SET") == 0)
+       {
+               static const char *const list_ALTERDOMAIN3[] =
+               {"DEFAULT", "NOT NULL", NULL};
+
+               COMPLETE_WITH_LIST(list_ALTERDOMAIN3);
+       }
        /* ALTER TRIGGER <name>, add ON */
        else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
-                        pg_strcasecmp(prev2_wd, "TRIGGER") == 0)
+                        pg_strcasecmp(prev2_wd, "TRIGGER") == 0 &&
+                        pg_strcasecmp(prev_wd, "ON") != 0)
                COMPLETE_WITH_CONST("ON");
 
        /*
@@ -661,13 +720,14 @@
 
        /*
         * If we detect ALTER TABLE <name>, suggest either ADD, DROP, ALTER,
-        * RENAME, or OWNER
+        * RENAME, CLUSTER ON or OWNER
         */
        else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
                         pg_strcasecmp(prev2_wd, "TABLE") == 0)
        {
                static const char *const list_ALTER2[] =
-               {"ADD", "ALTER", "DROP", "RENAME", "OWNER TO", NULL};
+               {"ADD", "ALTER", "CLUSTER ON", "DROP", "RENAME", "OWNER TO",
+               "SET", NULL};
 
                COMPLETE_WITH_LIST(list_ALTER2);
        }
@@ -691,7 +751,53 @@
                         pg_strcasecmp(prev2_wd, "DROP") == 0 &&
                         pg_strcasecmp(prev_wd, "COLUMN") == 0)
                COMPLETE_WITH_ATTR(prev3_wd);
+       else if (pg_strcasecmp(prev3_wd, "TABLE") == 0 &&
+                       pg_strcasecmp(prev_wd, "CLUSTER") == 0)
+               COMPLETE_WITH_CONST("ON");
+       else if (pg_strcasecmp(prev4_wd, "TABLE") == 0 &&
+                       pg_strcasecmp(prev2_wd, "CLUSTER") == 0 &&
+                       pg_strcasecmp(prev_wd, "ON") == 0) 
+       {
+               completion_info_charp = prev3_wd;
+               COMPLETE_WITH_QUERY(Query_for_index_of_table);
+       }
+       /* If we have TABLE <sth> SET, provide WITHOUT or TABLESPACE */
+       else if (pg_strcasecmp(prev3_wd, "TABLE") == 0 &&
+                        pg_strcasecmp(prev_wd, "SET") == 0)
+       {
+               static const char *const list_TABLESET[] =
+               {"WITHOUT", "TABLESPACE", NULL};
 
+               COMPLETE_WITH_LIST(list_TABLESET);
+       }
+       /* If we have TABLE <sth> SET TABLESPACE provide a list of tablespaces*/
+       else if (pg_strcasecmp(prev4_wd, "TABLE") == 0 &&
+                        pg_strcasecmp(prev2_wd, "SET") == 0 &&
+                        pg_strcasecmp(prev_wd, "TABLESPACE") == 0)
+               COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
+       /* If we have TABLE <sth> SET WITHOUT provide CLUSTER or OIDS*/
+       else if (pg_strcasecmp(prev4_wd, "TABLE") == 0 &&
+                        pg_strcasecmp(prev2_wd, "SET") == 0 &&
+                        pg_strcasecmp(prev_wd, "WITHOUT") == 0)
+       {               
+               static const char *const list_TABLESET2[] =
+               {"CLUSTER", "OIDS", NULL};
+
+               COMPLETE_WITH_LIST(list_TABLESET2);
+       }
+       /* we have ALTER TABLESPACE, so suggest RENAME TO, OWNER TO */
+       else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
+                        pg_strcasecmp(prev2_wd, "TABLESPACE") == 0)
+       {
+               static const char *const list_ALTERTSPC[] =
+               {"RENAME TO", "OWNER TO", NULL};
+
+               COMPLETE_WITH_LIST(list_ALTERTSPC);
+       }
+       /* complete ALTER TYPE <foo> with OWNER TO */
+       else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
+                        pg_strcasecmp(prev2_wd, "TYPE") == 0)
+               COMPLETE_WITH_CONST("OWNER TO");
        /* complete ALTER GROUP <foo> with ADD or DROP */
        else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
                         pg_strcasecmp(prev2_wd, "GROUP") == 0)
@@ -733,20 +839,26 @@
 
                COMPLETE_WITH_LIST(list_TRANS);
        }
+/* RELEASE SAVEPOINT */
+       else if ( pg_strcasecmp(prev_wd, "RELEASE") == 0 )
+               COMPLETE_WITH_CONST("SAVEPOINT");
 /* ROLLBACK*/
        else if ( pg_strcasecmp(prev_wd, "ROLLBACK") == 0 )
        {
                static const char * const list_TRANS[] =
-               {"WORK", "TRANSACTION", "TO", NULL};
+               {"WORK", "TRANSACTION", "TO SAVEPOINT", NULL};
 
                COMPLETE_WITH_LIST(list_TRANS);
        }
 /* CLUSTER */
-       /* If the previous word is CLUSTER, produce list of indexes. */
-       else if (pg_strcasecmp(prev_wd, "CLUSTER") == 0)
+       /* If the previous word is CLUSTER and not without produce list
+        * of indexes. */
+       else if (pg_strcasecmp(prev_wd, "CLUSTER") == 0 &&
+                       pg_strcasecmp(prev2_wd, "WITHOUT") != 0)
                COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL);
        /* If we have CLUSTER <sth>, then add "ON" */
-       else if (pg_strcasecmp(prev2_wd, "CLUSTER") == 0)
+       else if (pg_strcasecmp(prev2_wd, "CLUSTER") == 0 && 
+                       pg_strcasecmp(prev_wd,"ON") != 0)
                COMPLETE_WITH_CONST("ON");
 
        /*
@@ -767,9 +879,9 @@
                         pg_strcasecmp(prev_wd, "ON") == 0)
        {
                static const char *const list_COMMENT[] =
-               {"DATABASE", "INDEX", "RULE", "SCHEMA", "SEQUENCE", "TABLE",
-                "TYPE", "VIEW", "COLUMN", "AGGREGATE", "FUNCTION", "OPERATOR",
-                "TRIGGER", "CONSTRAINT", "DOMAIN", NULL};
+               {"CAST", "CONVERSION", "DATABASE", "INDEX", "LANGUAGE", "RULE", 
"SCHEMA",
+                "SEQUENCE", "TABLE", "TYPE", "VIEW", "COLUMN", "AGGREGATE", 
"FUNCTION",
+                "OPERATOR", "TRIGGER", "CONSTRAINT", "DOMAIN", NULL};
 
                COMPLETE_WITH_LIST(list_COMMENT);
        }
@@ -924,7 +1036,7 @@
                         pg_strcasecmp(prev_wd, "MOVE") == 0)
        {
                static const char * const list_FETCH1[] =
-               {"FORWARD", "BACKWARD", "RELATIVE", NULL};
+               {"ABSOLUT", "BACKWARD", "FORWARD", "RELATIVE", NULL};
 
                COMPLETE_WITH_LIST(list_FETCH1);
        }
@@ -985,7 +1097,8 @@
                                                                   " UNION SELECT 
'DATABASE'"
                                                                   " UNION SELECT 
'FUNCTION'"
                                                                   " UNION SELECT 
'LANGUAGE'"
-                                                                  " UNION SELECT 
'SCHEMA'");
+                                                                  " UNION SELECT 
'SCHEMA'"
+                                                                  " UNION SELECT 
'TABLESPACE'");
 
        /* Complete "GRANT/REVOKE * ON * " with "TO" */
        else if ((pg_strcasecmp(prev4_wd, "GRANT") == 0 ||
@@ -1000,6 +1113,8 @@
                        COMPLETE_WITH_QUERY(Query_for_list_of_languages);
                else if (pg_strcasecmp(prev_wd, "SCHEMA") == 0)
                        COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
+               else if (pg_strcasecmp(prev_wd, "TABLESPACE") == 0)
+                       COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
                else
                        COMPLETE_WITH_CONST("TO");
        }
@@ -1007,7 +1122,7 @@
        /*
         * TODO: to complete with user name we need prev5_wd -- wait for a
         * more general solution there same for GRANT <sth> ON { DATABASE |
-        * FUNCTION | LANGUAGE | SCHEMA } xxx TO
+        * FUNCTION | LANGUAGE | SCHEMA | TABLESPACE } xxx TO
         */
 
 /* INSERT */
@@ -1087,7 +1202,10 @@
 /* NOTIFY */
        else if (pg_strcasecmp(prev_wd, "NOTIFY") == 0)
                COMPLETE_WITH_QUERY("SELECT pg_catalog.quote_ident(relname) FROM 
pg_catalog.pg_listener WHERE substring(pg_catalog.quote_ident(relname),1,%d)='%s'");
-
+/* OWNER TO  - complete with available users*/
+       else if (pg_strcasecmp(prev2_wd, "OWNER") == 0 &&
+                       pg_strcasecmp(prev_wd, "TO") == 0)
+               COMPLETE_WITH_QUERY(Query_for_list_of_users);
 /* REINDEX */
        else if (pg_strcasecmp(prev_wd, "REINDEX") == 0)
        {
@@ -1136,16 +1254,20 @@
                COMPLETE_WITH_LIST(my_list);
        }
        else if ((pg_strcasecmp(prev3_wd, "SET") == 0
+                         || pg_strcasecmp(prev3_wd, "BEGIN") == 0
                          || pg_strcasecmp(prev3_wd, "START") == 0
                          || (pg_strcasecmp(prev4_wd, "CHARACTERISTICS") == 0
                                  && pg_strcasecmp(prev3_wd, "AS") == 0))
-                        && pg_strcasecmp(prev2_wd, "TRANSACTION") == 0
+                        && (pg_strcasecmp(prev2_wd, "TRANSACTION") == 0
+                                 || pg_strcasecmp(prev2_wd, "WORK") == 0)
                         && pg_strcasecmp(prev_wd, "ISOLATION") == 0)
                COMPLETE_WITH_CONST("LEVEL");
        else if ((pg_strcasecmp(prev4_wd, "SET") == 0
+                         || pg_strcasecmp(prev4_wd, "BEGIN") == 0
                          || pg_strcasecmp(prev4_wd, "START") == 0
                          || pg_strcasecmp(prev4_wd, "AS") == 0)
-                        && pg_strcasecmp(prev3_wd, "TRANSACTION") == 0
+                        && (pg_strcasecmp(prev3_wd, "TRANSACTION") == 0
+                                 || pg_strcasecmp(prev3_wd, "WORK") == 0) 
                         && pg_strcasecmp(prev2_wd, "ISOLATION") == 0
                         && pg_strcasecmp(prev_wd, "LEVEL") == 0)
        {
@@ -1154,7 +1276,8 @@
 
                COMPLETE_WITH_LIST(my_list);
        }
-       else if (pg_strcasecmp(prev4_wd, "TRANSACTION") == 0 &&
+       else if ((pg_strcasecmp(prev4_wd, "TRANSACTION") == 0 ||
+                               pg_strcasecmp(prev4_wd, "WORK") == 0) &&
                         pg_strcasecmp(prev3_wd, "ISOLATION") == 0 &&
                         pg_strcasecmp(prev2_wd, "LEVEL") == 0 &&
                         pg_strcasecmp(prev_wd, "READ") == 0)
@@ -1164,14 +1287,18 @@
 
                COMPLETE_WITH_LIST(my_list);
        }
-       else if (pg_strcasecmp(prev4_wd, "TRANSACTION") == 0 &&
+       else if ((pg_strcasecmp(prev4_wd, "TRANSACTION") == 0 || 
+                               pg_strcasecmp(prev4_wd, "WORK") == 0) &&
                         pg_strcasecmp(prev3_wd, "ISOLATION") == 0 &&
                         pg_strcasecmp(prev2_wd, "LEVEL") == 0 &&
                         pg_strcasecmp(prev_wd, "REPEATABLE") == 0)
                COMPLETE_WITH_CONST("READ");
        else if ((pg_strcasecmp(prev3_wd, "SET") == 0 ||
-                         pg_strcasecmp(prev3_wd, "AS") == 0) &&
-                        pg_strcasecmp(prev2_wd, "TRANSACTION") == 0 &&
+                        pg_strcasecmp(prev3_wd, "BEGIN") == 0 ||
+                        pg_strcasecmp(prev3_wd, "START") == 0 ||
+                        pg_strcasecmp(prev3_wd, "AS") == 0) &&
+                        (pg_strcasecmp(prev2_wd, "TRANSACTION") == 0 ||
+                               pg_strcasecmp(prev2_wd, "WORK") == 0) &&
                         pg_strcasecmp(prev_wd, "READ") == 0)
        {
                static const char * const my_list[] =
@@ -1295,6 +1422,8 @@
                COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tisv, NULL);
        else if (strcmp(prev_wd, "\\da") == 0)
                COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL);
+       else if (strcmp(prev_wd, "\\db") == 0)
+               COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
        else if (strcmp(prev_wd, "\\dD") == 0)
                COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_domains, NULL);
        else if (strcmp(prev_wd, "\\df") == 0 || strcmp(prev_wd, "\\df+") == 0)
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Reply via email to