psql - missing tab-completion support for tablespaces

Yes, I just noticed this myself.

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------

Stefan Kaltenbrunner wrote:

[sorry if you get this mail twice, i think my first post didn't made it
passt the moderator queue]

Hi!

While playing around with 8.0devel I found it somewhat irritating that
psql had no tab-complete support for all tablespace related commands.
Attached is my own poor attempt that adds at least basic support for
CREATE/ALTER/DROP and \db.

When looking through the code I found that there seem to be much more
places where the tabcomplete-code is not 100% in sync with what the
doc's show as possible syntax.
Is there interest in fixing those up (ie qualifing as BUGS that can get
fixed during BETA) ? If so I could take a look at those in the next days ...

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	6 Aug 2004 19:52:52 -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 "\
@@ -394,6 +398,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},
@@ -575,9 +580,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,7 +637,7 @@
pg_strcasecmp(prev3_wd, "TABLE") != 0)
{
static const char *const list_ALTER[] =
-		{"DATABASE", "GROUP", "SCHEMA", "TABLE", "TRIGGER", "USER", NULL};
+		{"DATABASE", "GROUP", "SCHEMA", "TABLE", "TABLESPACE", "TRIGGER", "USER", NULL};

COMPLETE_WITH_LIST(list_ALTER);
}
@@ -691,6 +696,16 @@
pg_strcasecmp(prev2_wd, "DROP") == 0 &&
pg_strcasecmp(prev_wd, "COLUMN") == 0)
COMPLETE_WITH_ATTR(prev3_wd);
+	
+	/* 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 GROUP <foo> with ADD or DROP */
else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
@@ -985,7 +1000,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 +1016,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 +1025,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 */
@@ -1295,6 +1313,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 4: Don't 'kill -9' the postmaster

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Stefan Kaltenbrunner wrote:

When looking through the code I found that there seem to be much more
places where the tabcomplete-code is not 100% in sync with what the
doc's show as possible syntax.
Is there interest in fixing those up (ie qualifing as BUGS that can get
fixed during BETA) ? If so I could take a look at those in the next days ...

Yes, please send in any tab completion improvements you can make.
Thanks.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

Stefan Kaltenbrunner wrote:

When looking through the code I found that there seem to be much more
places where the tabcomplete-code is not 100% in sync with what the
doc's show as possible syntax.
Is there interest in fixing those up (ie qualifing as BUGS that can get
fixed during BETA) ? If so I could take a look at those in the next days ...

Yes, please send in any tab completion improvements you can make.
Thanks.

About this I usefull have in the "rollback to <tab> <tab>" the list of save
point, I did the autocomplete patch for the nested transaction but I was not
able to retrieve the "active" savepoints, is it really impossible to hack
that part ?

Regards
Gaetano Mendola

Gaetano Mendola <mendola@bigfoot.com> writes:

About this I usefull have in the "rollback to <tab> <tab>" the list of save
point, I did the autocomplete patch for the nested transaction but I was not
able to retrieve the "active" savepoints, is it really impossible to hack
that part ?

There isn't any way to query the server about the set of open savepoint
names. I thought of adding a function for this, but gave up when I
realized that the time you'd really want to know is when your
transaction is aborted ... and the server will refuse to execute any
query in that case :-(

regards, tom lane

Bruce Momjian wrote:

Stefan Kaltenbrunner wrote:

When looking through the code I found that there seem to be much more
places where the tabcomplete-code is not 100% in sync with what the
doc's show as possible syntax.
Is there interest in fixing those up (ie qualifing as BUGS that can get
fixed during BETA) ? If so I could take a look at those in the next days ...

Yes, please send in any tab completion improvements you can make.

Hi!

attached is a patch that adds/fixes several smaller things in the
psql-tabcomplete code. This diff inculdes the TABLESPACE tab-complete
patches I sent earlier.
Since we are in Beta now and I'm by no means a programmer, I want to
know if this is something that is needed - or if I'm completely off-way
with what I'm doing here and wasting your and my time ...

below is a 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 in every occurence of "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)

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
*) add OWNER TO to ALTER TYPE
*) 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

Removed. Superceeded by later patch.

---------------------------------------------------------------------------

Stefan Kaltenbrunner wrote:

[sorry if you get this mail twice, i think my first post didn't made it
passt the moderator queue]

Hi!

While playing around with 8.0devel I found it somewhat irritating that
psql had no tab-complete support for all tablespace related commands.
Attached is my own poor attempt that adds at least basic support for
CREATE/ALTER/DROP and \db.

When looking through the code I found that there seem to be much more
places where the tabcomplete-code is not 100% in sync with what the
doc's show as possible syntax.
Is there interest in fixing those up (ie qualifing as BUGS that can get
fixed during BETA) ? If so I could take a look at those in the next days ...

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	6 Aug 2004 19:52:52 -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 "\
@@ -394,6 +398,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},
@@ -575,9 +580,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,7 +637,7 @@
pg_strcasecmp(prev3_wd, "TABLE") != 0)
{
static const char *const list_ALTER[] =
-		{"DATABASE", "GROUP", "SCHEMA", "TABLE", "TRIGGER", "USER", NULL};
+		{"DATABASE", "GROUP", "SCHEMA", "TABLE", "TABLESPACE", "TRIGGER", "USER", NULL};

COMPLETE_WITH_LIST(list_ALTER);
}
@@ -691,6 +696,16 @@
pg_strcasecmp(prev2_wd, "DROP") == 0 &&
pg_strcasecmp(prev_wd, "COLUMN") == 0)
COMPLETE_WITH_ATTR(prev3_wd);
+	
+	/* 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 GROUP <foo> with ADD or DROP */
else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
@@ -985,7 +1000,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 +1016,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 +1025,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 */
@@ -1295,6 +1313,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 4: Don't 'kill -9' the postmaster

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073