Re: Tab completion for ALTER INDEX|TABLE ALTER COLUMN SET STATISTICS

[Tatsuro Yamada]

Hi Peter,

On 2019/01/25 20:09, Peter Eisentraut wrote:

On 26/12/2018 07:07, Tatsuro Yamada wrote:

+#define Query_for_list_of_attribute_numbers \
+"SELECT attnum "\
+"  FROM pg_catalog.pg_attribute a, "\
+"       pg_catalog.pg_class c "\
+" WHERE c.oid = a.attrelid "\
+"   AND a.attnum > 0 "\
+"   AND NOT a.attisdropped "\
+"   /* %d %s */" \
+"   AND a.attrelid = (select oid from pg_catalog.pg_class where relname = '%s') 
"\
+"   AND pg_catalog.pg_table_is_visible(c.oid) "\
+"order by a.attnum asc "

This needs a bit of refinement.  You need to handle quoted index names
(see nearby Query_for_list_of_attributes), and you should also complete
partial numbers (e.g., if I type 1 then complete 10, 11, ... if
appropriate).

Thanks for the comments.
I modified the patch to handle the both:
  - quoted index names
  - complete partial numbers

e.g.
-----
# create table hoge (a integer, b integer, c integer);
# create index ind_hoge on hoge(a, b, c, (c*1), (c*2), (c*3), (c*4), (c*5), 
(c*6), (c*7), (c*8), (c*9));
# create index "ind hoge2" on hoge(c, b, a, (c*1), (c*2), (c*3), (c*4), (c*5), 
(c*6), (c*7), (c*8), (c*9));

# alter index "ind hoge2" alter column
1   10  11  12  2   3   4   5   6   7   8   9
# alter index "ind hoge2" alter column 1
1   10  11  12

# alter index ind_hoge alter column
1   10  11  12  2   3   4   5   6   7   8   9
# alter index ind_hoge alter column 1
1   10  11  12
-----

Please find attached file. :)

Regards,
Tatsuro Yamada

diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 292b1f483a..faee44393c 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -583,6 +583,17 @@ static const SchemaQuery Query_for_list_of_statistics = {
 "        OR '\"' || relname || '\"'='%s') "\
 "   AND pg_catalog.pg_table_is_visible(c.oid)"
 
+#define Query_for_list_of_attribute_numbers \
+"SELECT attnum "\
+"  FROM pg_catalog.pg_attribute a, pg_catalog.pg_class c "\
+" WHERE c.oid = a.attrelid "\
+"   AND a.attnum > 0 "\
+"   AND NOT a.attisdropped "\
+"   AND pg_catalog.substring(attnum::text,1,%d)='%s' "\
+"   AND (pg_catalog.quote_ident(relname)='%s' "\
+"        OR '\"' || relname || '\"'='%s') "\
+"   AND pg_catalog.pg_table_is_visible(c.oid)"
+
 #define Query_for_list_of_attributes_with_schema \
 "SELECT pg_catalog.quote_ident(attname) "\
 "  FROM pg_catalog.pg_attribute a, pg_catalog.pg_class c, pg_catalog.pg_namespace n "\
@@ -1604,6 +1615,12 @@ psql_completion(const char *text, int start, int end)
 	/* ALTER INDEX <name> ALTER */
 	else if (Matches("ALTER", "INDEX", MatchAny, "ALTER"))
 		COMPLETE_WITH("COLUMN");
+	/* ALTER INDEX <name> ALTER COLUMN */
+	else if (Matches("ALTER", "INDEX", MatchAny, "ALTER", "COLUMN"))
+	{
+		completion_info_charp = prev3_wd;
+		COMPLETE_WITH_QUERY(Query_for_list_of_attribute_numbers);
+	}
 	/* ALTER INDEX <name> ALTER COLUMN <colnum> */
 	else if (Matches("ALTER", "INDEX", MatchAny, "ALTER", "COLUMN", MatchAny))
 		COMPLETE_WITH("SET STATISTICS");