Re: psql tab completion enhancements - Mailing list pgsql-patches

Modified patch attached and applied.

Rather than create the lists in psql, I used UNION SELECT 'KEYWORD' to
pass the keyword to the backend to be added to the query list.

This was already being done for schema names, and was easy and efficient
to add.  My addition is even simpler because it just concatenates two
adjacent strings.  If you have more modifications, please use this
system.

If you find a macro that needs an 'addon', you can add it as a macro
parameter to all calls and just use "" if you don't need it.  If any
macro call uses a non-constant string, you have to make a new *_ADDON
macro version.

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

Joachim Wieland wrote:
> Hi,
>
> psql's tab completion has the following problem:
>
> If we have the following syntax for example:
>
> SET SESSION AUTHORIZATION <user>;
> SET SESSION AUTHORIZATION DEFAULT;
>
> After "SET SESSION AUTHORIZATION", the tab completion can offer a list of
> roles or the string constant "DEFAULT". However it can't offer both because
> it can't get a list of roles and add a string constant to this list.
>
> The appended patch adds the functionality of lists that can be extended with
> constants.
>
> Then you get:
>
> template1=# SET session AUTHORIZATION <tab>
> DEFAULT  fred     joe      john
>
> I did proof-of-concept examples to add a constant to a
>
>  - list from a query
>  - list from a schema query
>  - list of table attributes
>
>
> Joachim
>

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
  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
Index: src/bin/psql/tab-complete.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/tab-complete.c,v
retrieving revision 1.146
diff -c -c -r1.146 tab-complete.c
*** src/bin/psql/tab-complete.c    12 Feb 2006 03:22:19 -0000    1.146
--- src/bin/psql/tab-complete.c    12 Feb 2006 07:18:33 -0000
***************
*** 140,153 ****
  */
  #define COMPLETE_WITH_QUERY(query) \
  do { completion_charp = query; matches = completion_matches(text, complete_from_query); } while(0)
! #define COMPLETE_WITH_SCHEMA_QUERY(query,addon) \
  do { completion_squery = &(query); completion_charp = addon; matches = completion_matches(text,
complete_from_schema_query);} while(0) 
  #define COMPLETE_WITH_LIST(list) \
  do { completion_charpp = list; matches = completion_matches(text, complete_from_list); } while(0)
  #define COMPLETE_WITH_CONST(string) \
  do { completion_charp = string; matches = completion_matches(text, complete_from_const); } while(0)
! #define COMPLETE_WITH_ATTR(table) \
! do {completion_charp = Query_for_list_of_attributes; completion_info_charp = table; matches =
completion_matches(text,complete_from_query); } while(0) 

  /*
   * Assembly instructions for schema queries
--- 140,155 ----
  */
  #define COMPLETE_WITH_QUERY(query) \
  do { completion_charp = query; matches = completion_matches(text, complete_from_query); } while(0)
! #define COMPLETE_WITH_QUERY_ADDON(query, addon) \
! do { completion_charp = query addon; matches = completion_matches(text, complete_from_query); } while(0)
! #define COMPLETE_WITH_SCHEMA_QUERY(query, addon) \
  do { completion_squery = &(query); completion_charp = addon; matches = completion_matches(text,
complete_from_schema_query);} while(0) 
  #define COMPLETE_WITH_LIST(list) \
  do { completion_charpp = list; matches = completion_matches(text, complete_from_list); } while(0)
  #define COMPLETE_WITH_CONST(string) \
  do { completion_charp = string; matches = completion_matches(text, complete_from_const); } while(0)
! #define COMPLETE_WITH_ATTR(table, addon) \
! do {completion_charp = Query_for_list_of_attributes addon; completion_info_charp = table; matches =
completion_matches(text,complete_from_query); } while(0) 

  /*
   * Assembly instructions for schema queries
***************
*** 754,767 ****
      else if (pg_strcasecmp(prev3_wd, "TABLE") == 0 &&
               (pg_strcasecmp(prev_wd, "ALTER") == 0 ||
                pg_strcasecmp(prev_wd, "RENAME") == 0))
!         COMPLETE_WITH_ATTR(prev2_wd);

      /* ALTER TABLE xxx RENAME yyy */
      else if (pg_strcasecmp(prev4_wd, "TABLE") == 0 &&
               pg_strcasecmp(prev2_wd, "RENAME") == 0 &&
               pg_strcasecmp(prev_wd, "TO") != 0)
          COMPLETE_WITH_CONST("TO");

      /* If we have TABLE <sth> DROP, provide COLUMN or CONSTRAINT */
      else if (pg_strcasecmp(prev3_wd, "TABLE") == 0 &&
               pg_strcasecmp(prev_wd, "DROP") == 0)
--- 756,783 ----
      else if (pg_strcasecmp(prev3_wd, "TABLE") == 0 &&
               (pg_strcasecmp(prev_wd, "ALTER") == 0 ||
                pg_strcasecmp(prev_wd, "RENAME") == 0))
!         COMPLETE_WITH_ATTR(prev2_wd, " UNION SELECT 'COLUMN'");

+     /* If we have TABLE <sth> ALTER COLUMN|RENAME COLUMN, provide list of columns */
+     else if (pg_strcasecmp(prev4_wd, "TABLE") == 0 &&
+              (pg_strcasecmp(prev2_wd, "ALTER") == 0 ||
+               pg_strcasecmp(prev2_wd, "RENAME") == 0) &&
+              pg_strcasecmp(prev_wd, "COLUMN") == 0)
+         COMPLETE_WITH_ATTR(prev3_wd, "");
+
      /* ALTER TABLE xxx RENAME yyy */
      else if (pg_strcasecmp(prev4_wd, "TABLE") == 0 &&
               pg_strcasecmp(prev2_wd, "RENAME") == 0 &&
               pg_strcasecmp(prev_wd, "TO") != 0)
          COMPLETE_WITH_CONST("TO");

+     /* ALTER TABLE xxx RENAME COLUMN yyy */
+     else if (pg_strcasecmp(prev5_wd, "TABLE") == 0 &&
+              pg_strcasecmp(prev3_wd, "RENAME") == 0 &&
+              pg_strcasecmp(prev2_wd, "COLUMN") == 0 &&
+              pg_strcasecmp(prev_wd, "TO") != 0)
+         COMPLETE_WITH_CONST("TO");
+
      /* If we have TABLE <sth> DROP, provide COLUMN or CONSTRAINT */
      else if (pg_strcasecmp(prev3_wd, "TABLE") == 0 &&
               pg_strcasecmp(prev_wd, "DROP") == 0)
***************
*** 775,781 ****
      else if (pg_strcasecmp(prev4_wd, "TABLE") == 0 &&
               pg_strcasecmp(prev2_wd, "DROP") == 0 &&
               pg_strcasecmp(prev_wd, "COLUMN") == 0)
!         COMPLETE_WITH_ATTR(prev3_wd);
      /* ALTER TABLE ALTER [COLUMN] <foo> */
      else if ((pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
                pg_strcasecmp(prev2_wd, "COLUMN") == 0) ||
--- 791,797 ----
      else if (pg_strcasecmp(prev4_wd, "TABLE") == 0 &&
               pg_strcasecmp(prev2_wd, "DROP") == 0 &&
               pg_strcasecmp(prev_wd, "COLUMN") == 0)
!         COMPLETE_WITH_ATTR(prev3_wd, "");
      /* ALTER TABLE ALTER [COLUMN] <foo> */
      else if ((pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
                pg_strcasecmp(prev2_wd, "COLUMN") == 0) ||
***************
*** 1021,1038 ****
               pg_strcasecmp(prev2_wd, "ON") == 0)
      {
          if (find_open_parenthesis(end))
!             COMPLETE_WITH_ATTR(prev_wd);
          else
              COMPLETE_WITH_CONST("(");
      }
      else if (pg_strcasecmp(prev5_wd, "INDEX") == 0 &&
              pg_strcasecmp(prev3_wd, "ON") == 0 &&
              pg_strcasecmp(prev_wd, "(") == 0)
!         COMPLETE_WITH_ATTR(prev2_wd);
      /* same if you put in USING */
      else if (pg_strcasecmp(prev4_wd, "ON") == 0 &&
               pg_strcasecmp(prev2_wd, "USING") == 0)
!         COMPLETE_WITH_ATTR(prev3_wd);
      /* Complete USING with an index method */
      else if (pg_strcasecmp(prev_wd, "USING") == 0)
      {
--- 1037,1054 ----
               pg_strcasecmp(prev2_wd, "ON") == 0)
      {
          if (find_open_parenthesis(end))
!             COMPLETE_WITH_ATTR(prev_wd, "");
          else
              COMPLETE_WITH_CONST("(");
      }
      else if (pg_strcasecmp(prev5_wd, "INDEX") == 0 &&
              pg_strcasecmp(prev3_wd, "ON") == 0 &&
              pg_strcasecmp(prev_wd, "(") == 0)
!         COMPLETE_WITH_ATTR(prev2_wd, "");
      /* same if you put in USING */
      else if (pg_strcasecmp(prev4_wd, "ON") == 0 &&
               pg_strcasecmp(prev2_wd, "USING") == 0)
!         COMPLETE_WITH_ATTR(prev3_wd, "");
      /* Complete USING with an index method */
      else if (pg_strcasecmp(prev_wd, "USING") == 0)
      {
***************
*** 1420,1426 ****
      else if (rl_line_buffer[start - 1] == '(' &&
               pg_strcasecmp(prev3_wd, "INSERT") == 0 &&
               pg_strcasecmp(prev2_wd, "INTO") == 0)
!         COMPLETE_WITH_ATTR(prev_wd);

      /*
       * Complete INSERT INTO <table> with "VALUES" or "SELECT" or "DEFAULT
--- 1436,1442 ----
      else if (rl_line_buffer[start - 1] == '(' &&
               pg_strcasecmp(prev3_wd, "INSERT") == 0 &&
               pg_strcasecmp(prev2_wd, "INTO") == 0)
!         COMPLETE_WITH_ATTR(prev_wd, "");

      /*
       * Complete INSERT INTO <table> with "VALUES" or "SELECT" or "DEFAULT
***************
*** 1452,1461 ****

  /* LOCK */
      /* Complete LOCK [TABLE] with a list of tables */
!     else if (pg_strcasecmp(prev_wd, "LOCK") == 0 ||
!              (pg_strcasecmp(prev_wd, "TABLE") == 0 &&
!               pg_strcasecmp(prev2_wd, "LOCK") == 0))
!         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);

      /* For the following, handle the case of a single table only for now */

--- 1468,1479 ----

  /* LOCK */
      /* Complete LOCK [TABLE] with a list of tables */
!     else if (pg_strcasecmp(prev_wd, "LOCK") == 0)
!         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables,
!                                    " UNION SELECT 'TABLE'");
!     else if (pg_strcasecmp(prev_wd, "TABLE") == 0 &&
!              pg_strcasecmp(prev2_wd, "LOCK") == 0)
!         COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, "");

      /* For the following, handle the case of a single table only for now */

***************
*** 1498,1504 ****
      else if (pg_strcasecmp(prev4_wd, "FROM") == 0 &&
               pg_strcasecmp(prev2_wd, "ORDER") == 0 &&
               pg_strcasecmp(prev_wd, "BY") == 0)
!         COMPLETE_WITH_ATTR(prev3_wd);

  /* PREPARE xx AS */
      else if (pg_strcasecmp(prev_wd, "AS") == 0 &&
--- 1516,1522 ----
      else if (pg_strcasecmp(prev4_wd, "FROM") == 0 &&
               pg_strcasecmp(prev2_wd, "ORDER") == 0 &&
               pg_strcasecmp(prev_wd, "BY") == 0)
!         COMPLETE_WITH_ATTR(prev3_wd, "");

  /* PREPARE xx AS */
      else if (pg_strcasecmp(prev_wd, "AS") == 0 &&
***************
*** 1639,1645 ****
      else if (pg_strcasecmp(prev3_wd, "SET") == 0
               && pg_strcasecmp(prev2_wd, "SESSION") == 0
               && pg_strcasecmp(prev_wd, "AUTHORIZATION") == 0)
!         COMPLETE_WITH_QUERY(Query_for_list_of_roles);
      /* Complete RESET SESSION with AUTHORIZATION */
      else if (pg_strcasecmp(prev2_wd, "RESET") == 0 &&
               pg_strcasecmp(prev_wd, "SESSION") == 0)
--- 1657,1663 ----
      else if (pg_strcasecmp(prev3_wd, "SET") == 0
               && pg_strcasecmp(prev2_wd, "SESSION") == 0
               && pg_strcasecmp(prev_wd, "AUTHORIZATION") == 0)
!         COMPLETE_WITH_QUERY_ADDON(Query_for_list_of_roles, " UNION SELECT 'DEFAULT'");
      /* Complete RESET SESSION with AUTHORIZATION */
      else if (pg_strcasecmp(prev2_wd, "RESET") == 0 &&
               pg_strcasecmp(prev_wd, "SESSION") == 0)
***************
*** 1706,1712 ****
       * make a list of attributes.
       */
      else if (pg_strcasecmp(prev_wd, "SET") == 0)
!         COMPLETE_WITH_ATTR(prev2_wd);

  /* UPDATE xx SET yy = */
      else if (pg_strcasecmp(prev2_wd, "SET") == 0 &&
--- 1724,1730 ----
       * make a list of attributes.
       */
      else if (pg_strcasecmp(prev_wd, "SET") == 0)
!         COMPLETE_WITH_ATTR(prev2_wd, "");

  /* UPDATE xx SET yy = */
      else if (pg_strcasecmp(prev2_wd, "SET") == 0 &&
***************
*** 1763,1769 ****
  /* WHERE */
      /* Simple case of the word before the where being the table name */
      else if (pg_strcasecmp(prev_wd, "WHERE") == 0)
!         COMPLETE_WITH_ATTR(prev2_wd);

  /* ... FROM ... */
  /* TODO: also include SRF ? */
--- 1781,1787 ----
  /* WHERE */
      /* Simple case of the word before the where being the table name */
      else if (pg_strcasecmp(prev_wd, "WHERE") == 0)
!         COMPLETE_WITH_ATTR(prev2_wd, "");

  /* ... FROM ... */
  /* TODO: also include SRF ? */