Re: \du in psql patchp - Mailing list pgsql-patches

Patch applied.  I removed the reference to asprintf() and changed it to
a string constant.  I have also added documentation changes.  Patch
attached.

The new feature looks like:

    test=> \du
                    List of Users
     User Name | User ID |       Attributes
    -----------+---------+------------------------
     demouser2 | 140     | create DB
     postgres  | 139     | create user, create DB

Very nice.


> Hi everyone,
>
> I quickly put together a patch to psql that allows one to view current
> system users. The code is very simple, but it seems to work.
>
> The usage is as follows:
>
> database=# \du [username]
>
> I apologize in advance for my lack of C skills, and I will gladly
> accept any criticism or advice. I'm hoping I can figure out a way
> to write an equivalent for groups, once I determine how to
> get around the grolist integer[] column in pg_group.
>
> Please take a look at the patch and see if it's worth anything.
>
> Thanks
>
> Alex
>
>
>       Alex G. Perel  -=-  AP5081
> veers@disturbed.net  -=-  aperel@verticalscope.com
>                play  -=-  work
>
> Disturbed Networks - Powered exclusively by FreeBSD
> == The Power to Serve -=- http://www.freebsd.org/

Content-Description:

[ Attachment, skipping... ]

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.50
diff -c -r1.50 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml    2001/05/07 19:31:33    1.50
--- doc/src/sgml/ref/psql-ref.sgml    2001/05/09 16:54:45
***************
*** 495,500 ****
--- 495,510 ----


        <varlistentry>
+         <term><literal>\du [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
+         <listitem>
+         <para>
+         Lists all configured users or only those that match <replaceable class="parameter">pattern</replaceable>.
+         </para>
+         </listitem>
+       </varlistentry>
+
+
+       <varlistentry>
          <term><literal>\edit</literal> (or <literal>\e</literal>) [ <replaceable
class="parameter">filename</replaceable>]</term> 

          <listitem>
Index: src/bin/psql/command.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.51
diff -c -r1.51 command.c
*** src/bin/psql/command.c    2001/05/07 19:31:33    1.51
--- src/bin/psql/command.c    2001/05/09 16:54:56
***************
*** 357,362 ****
--- 357,365 ----
              case 'S':
                  success = listTables(&cmd[1], name, show_verbose);
                  break;
+             case 'u':
+                 success = describeUsers(name);
+                   break;
              default:
                  status = CMD_UNKNOWN;
          }
Index: src/bin/psql/describe.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.28
diff -c -r1.28 describe.c
*** src/bin/psql/describe.c    2001/03/22 04:00:19    1.28
--- src/bin/psql/describe.c    2001/05/09 16:54:59
***************
*** 890,895 ****
--- 890,992 ----
  }


+ /*
+  * describeUsers()
+  *
+  * \du [user]
+  *
+  * Describes users, possibly based on a simplistic prefix search on the
+  * argument.
+  */
+
+ bool
+ describeUsers (const char *name)
+ {
+     char        buf[384 + REGEXP_CUTOFF];
+     PGresult   *res;
+     printTableOpt myopt = pset.popt.topt;
+     int         i;
+     char        *title;
+     const char *headers[4];
+     char      **cells    = NULL;
+     unsigned int cols;
+
+     /*
+      * All we want to know is the user names and permissions
+      * for the system.
+      */
+
+     title = "List of Users";
+
+     cols = 0;
+     headers[cols++] = "User Name";
+     headers[cols++] = "User ID";
+     headers[cols++] = "Attributes";
+     headers[cols] = NULL;
+
+     strcpy(buf,
+            "SELECT u.usename AS \"User Name\"\n
+                  , u.usesysid AS \"User ID\"\n
+                  , u.usesuper AS \"Super User\"\n
+                  , u.usecreatedb AS \"Create DB\"\n
+               FROM pg_user u\n");
+     if (name)
+     {
+         strcat(buf, "   WHERE u.usename ~ '^");
+         strncat(buf, name, REGEXP_CUTOFF);
+         strcat(buf, "'\n");
+     }
+     strcat(buf, "ORDER BY \"User Name\"\n");
+
+     res = PSQLexec(buf);
+     if (!res)
+         return false;
+
+     cells = xmalloc((PQntuples(res) * cols + 1) * sizeof(*cells));
+     cells[PQntuples(res) * cols] = NULL;
+
+     for (i = 0; i < PQntuples(res); i++)
+     {
+         char createuser[2] = "";
+         char createdb[2]  = "";
+
+         /* Name */
+         cells[i * cols + 0] = PQgetvalue(res, i, 0);
+
+         /* ID */
+         cells[i * cols + 1] = PQgetvalue(res, i, 1);
+
+         /* Super */
+         strcpy(createuser, PQgetvalue(res, i, 2));
+
+         /* Create DB */
+         strcpy(createdb, PQgetvalue(res, i, 3));
+
+         cells[i * cols + 2] = xmalloc((strlen("create user, create DB") * sizeof(char)) + 1);
+         strcpy(cells[i * cols + 2], "");
+
+          if (strcmp(createuser, "t") == 0)
+              strcat(cells[i * cols + 2], "create user");
+
+          if (strcmp(createdb, "t") == 0) {
+             if (strcmp(createuser, "t") == 0)
+                 strcat(cells[i * cols + 2], ", ");
+              strcat(cells[i * cols + 2], "create DB");
+         }
+     }
+
+     printTable(title, headers,
+                (const char **) cells,
+                NULL,
+                "lll", &myopt, pset.queryFout);
+
+     /* clean up */
+     free(cells);
+
+     PQclear(res);
+     return true;
+ }
+

  /*
   * listTables()
Index: src/bin/psql/describe.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/bin/psql/describe.h,v
retrieving revision 1.9
diff -c -r1.9 describe.h
*** src/bin/psql/describe.h    2000/04/12 17:16:22    1.9
--- src/bin/psql/describe.h    2001/05/09 16:54:59
***************
*** 22,27 ****
--- 22,30 ----
  /* \do */
  bool        describeOperators(const char *name);

+ /* \du */
+ bool         describeUsers(const char *name);
+
  /* \z (or \dp) */
  bool        permissionsList(const char *name);