First attempt: support for "\dg" in psql - Mailing list pgsql-patches

G'day folks,

Attached is my first attempt at providing "\du"-like functionality for
groups (i.e. a basic "\dg").  It's really just a copy of describeUsers().

It has one major limitation - members are listed by numeric id as I can't
think of a "cheap" way of turning the list into the corresponding list of
usernames.

I thought about having describeGroups() do a lookup on pg_user, but that
could become quite expensive if there is a large number of groups and/or
users (have I missed an obvious facility for caching this information?).

Also, the TODO mentions that the intention is to have \du list groups - I
assume that means output along the lines of the following is wanted?

              List of database users
 User name | User ID |         Attributes         | Groups
-----------+---------+----------------------------+---------------
 test      |     100 | superuser, create database | testg1, testg2

Getting the group names for \du or the usernames for \dg are both
relatively easy with the appropriate JOIN, but the result is always a set
of rows.  How to turn that into a single array?  What would be handy is a
a library function returning an array, which takes an array, a table and two
two columns from that table:
    F(A,T,C1,C2) returning an array of T.C2 values, one for each
    value of T.C1 found in A.

With regard to criticism, please be gentle - this is only my second
contribution (the first being a very minor update to to_char).  ;-)

Ciao.

--
-------------------------------------------------------+---------------------
Daniel Baldoni BAppSc, PGradDipCompSci                 |  Technical Director
require 'std/disclaimer.pl'                            |  LcdS Pty. Ltd.
-------------------------------------------------------+  856B Canning Hwy
Phone/FAX:  +61-8-9364-8171                            |  Applecross
Mobile:     041-888-9794                               |  WA 6153
URL:        http://www.lcds.com.au/                    |  Australia
-------------------------------------------------------+---------------------
"Any time there's something so ridiculous that no rational systems programmer
 would even consider trying it, they send for me."; paraphrased from "King Of
 The Murgos" by David Eddings.  (I'm not good, just crazy)diff -r -c -w -i -B postgresql-7.4.3/src/bin/psql/command.c
postgresql-7.4.3-01/src/bin/psql/command.c
*** postgresql-7.4.3/src/bin/psql/command.c    2003-10-12 02:04:26.000000000 +0800
--- postgresql-7.4.3-01/src/bin/psql/command.c    2004-06-30 19:17:29.000000000 +0800
***************
*** 363,368 ****
--- 363,371 ----
              case 'f':
                  success = describeFunctions(pattern, show_verbose);
                  break;
+             case 'g':
+                 success = describeGroups(pattern);
+                 break;
              case 'l':
                  success = do_lo_list();
                  break;
diff -r -c -w -i -B postgresql-7.4.3/src/bin/psql/describe.c postgresql-7.4.3-01/src/bin/psql/describe.c
*** postgresql-7.4.3/src/bin/psql/describe.c    2004-01-12 03:25:44.000000000 +0800
--- postgresql-7.4.3-01/src/bin/psql/describe.c    2004-06-30 23:33:50.000000000 +0800
***************
*** 1252,1257 ****
--- 1252,1300 ----


  /*
+  * \dg
+  *
+  * Describes groups.  Any schema portion of the pattern is ignored.
+  */
+ bool
+ describeGroups(const char *pattern)
+ {
+     PQExpBufferData buf;
+     PGresult   *res;
+     printQueryOpt myopt = pset.popt;
+
+     initPQExpBuffer(&buf);
+
+     printfPQExpBuffer(&buf,
+                       "SELECT g.groname AS \"%s\",\n"
+                       "  g.grosysid AS \"%s\",\n"
+                       "  CASE WHEN g.grolist IS NULL"
+                       " THEN CAST('none' AS pg_catalog.text)\n"
+                       "       ELSE CAST(array_to_string(g.grolist, ',') AS pg_catalog.text)\n"
+                       "  END AS \"%s\"\n"
+                       "FROM pg_catalog.pg_group g\n",
+                       _("Group name"), _("Group ID"), _("Members"));
+
+     processNamePattern(&buf, pattern, false, false,
+                        NULL, "g.groname", NULL, NULL);
+
+     appendPQExpBuffer(&buf, "ORDER BY 1;");
+
+     res = PSQLexec(buf.data, false);
+     termPQExpBuffer(&buf);
+     if (!res)
+         return false;
+
+     myopt.nullPrint = NULL;
+     myopt.title = _("List of database groups");
+
+     printQuery(res, &myopt, pset.queryFout);
+
+     PQclear(res);
+     return true;
+ }
+
+ /*
   * \du
   *
   * Describes users.  Any schema portion of the pattern is ignored.
diff -r -c -w -i -B postgresql-7.4.3/src/bin/psql/describe.h postgresql-7.4.3-01/src/bin/psql/describe.h
*** postgresql-7.4.3/src/bin/psql/describe.h    2003-08-05 07:59:40.000000000 +0800
--- postgresql-7.4.3-01/src/bin/psql/describe.h    2004-06-30 23:33:43.000000000 +0800
***************
*** 16,21 ****
--- 16,24 ----
  /* \df */
  bool        describeFunctions(const char *pattern, bool verbose);

+ /* \dg */
+ bool        describeGroups(const char *pattern);
+
  /* \dT */
  bool        describeTypes(const char *pattern, bool verbose);

diff -r -c -w -i -B postgresql-7.4.3/src/bin/psql/help.c postgresql-7.4.3-01/src/bin/psql/help.c
*** postgresql-7.4.3/src/bin/psql/help.c    2003-10-02 14:39:31.000000000 +0800
--- postgresql-7.4.3-01/src/bin/psql/help.c    2004-06-30 19:16:52.000000000 +0800
***************
*** 216,221 ****
--- 216,222 ----
      fprintf(output, _("  \\dd [PATTERN]  show comment for object\n"));
      fprintf(output, _("  \\dD [PATTERN]  list domains\n"));
      fprintf(output, _("  \\df [PATTERN]  list functions (add \"+\" for more detail)\n"));
+     fprintf(output, _("  \\dg [PATTERN]  list groups\n"));
      fprintf(output, _("  \\dn [PATTERN]  list schemas\n"));
      fprintf(output, _("  \\do [NAME]     list operators\n"));
      fprintf(output, _("  \\dl            list large objects, same as \\lo_list\n"));
diff -r -c -w -i -B postgresql-7.4.3/src/bin/psql/tab-complete.c postgresql-7.4.3-01/src/bin/psql/tab-complete.c
*** postgresql-7.4.3/src/bin/psql/tab-complete.c    2003-11-09 04:54:37.000000000 +0800
--- postgresql-7.4.3-01/src/bin/psql/tab-complete.c    2004-06-30 19:16:29.000000000 +0800
***************
*** 359,364 ****
--- 359,369 ----
  "   FROM pg_catalog.pg_user "\
  "  WHERE substring(pg_catalog.quote_ident(usename),1,%d)='%s'"

+ #define Query_for_list_of_groups \
+ " SELECT pg_catalog.quote_ident(groname) "\
+ "   FROM pg_catalog.pg_group "\
+ "  WHERE substring(pg_catalog.quote_ident(groname),1,%d)='%s'"
+
  /* the silly-looking length condition is just to eat up the current word */
  #define Query_for_table_owning_index \
  "SELECT pg_catalog.quote_ident(c1.relname) "\
***************
*** 580,586 ****
          "\\a", "\\connect", "\\C", "\\cd", "\\copy", "\\copyright",
          "\\d", "\\da", "\\dc", "\\dC", "\\dd", "\\dD", "\\df", "\\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",
--- 585,591 ----
          "\\a", "\\connect", "\\C", "\\cd", "\\copy", "\\copyright",
          "\\d", "\\da", "\\dc", "\\dC", "\\dd", "\\dD", "\\df", "\\di",
          "\\dl", "\\dn", "\\do", "\\dp", "\\ds", "\\dS", "\\dt", "\\dT",
!         "\\dv", "\\du", "\\dg",
          "\\e", "\\echo", "\\encoding",
          "\\f", "\\g", "\\h", "\\help", "\\H", "\\i", "\\l",
          "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
***************
*** 1275,1280 ****
--- 1280,1287 ----
          COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL);
      else if (strcmp(prev_wd, "\\du") == 0)
          COMPLETE_WITH_QUERY(Query_for_list_of_users);
+     else if (strcmp(prev_wd, "\\dg") == 0)
+         COMPLETE_WITH_QUERY(Query_for_list_of_groups);
      else if (strcmp(prev_wd, "\\dv") == 0 || strcmp(prev_wd, "\\dv+") == 0)
          COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);
      else if (strcmp(prev_wd, "\\encoding") == 0)