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)
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
