Thread: First attempt: support for "\dg" in psql
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)
This is already in: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/bin/psql/describe.c.diff?r1=text&tr1=1.89&r2=text&tr2=1.90&f=h The - in front of that item on the todo list[1] means that it's already done. This information probably needs to appear more prominently on the todo page. [1] http://developer.postgresql.org/todo.php Thanks. -- Markus Bertheau <twanger@bluetwanger.de>
I have moved the mention of the dash higher in the TODO file. --------------------------------------------------------------------------- Markus Bertheau wrote: > This is already in: > > http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/bin/psql/describe.c.diff?r1=text&tr1=1.89&r2=text&tr2=1.90&f=h > > The - in front of that item on the todo list[1] means that it's already > done. This information probably needs to appear more prominently on the > todo page. > > [1] http://developer.postgresql.org/todo.php > > Thanks. > > -- > Markus Bertheau <twanger@bluetwanger.de> > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > !DSPAM:40e56844212136178011369! > > -- 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
Bruce Momjian said: > > I have moved the mention of the dash higher in the TODO file. Maybe you could put the dash in a different color (red?) so it stands out more. Or maybe the whole item could go in a different color if done. cheers andrew
В Втр, 06.07.2004, в 19:09, Andrew Dunstan пишет:
Yes, what about stroke through:
done TODO item
Bruce Momjian said: > > I have moved the mention of the dash higher in the TODO file. Maybe you could put the dash in a different color (red?) so it stands out more. Or maybe the whole item could go in a different color if done.
Yes, what about stroke through:
|
-- Markus Bertheau <twanger@bluetwanger.de> |
>>> I have moved the mention of the dash higher in the TODO file. >> >> >>Maybe you could put the dash in a different color (red?) so it stands out >>more. Or maybe the whole item could go in a different color if done./ >> > > Yes, what about stroke through: Because neither works in the plain text version. Chris
Christopher Kings-Lynne wrote:
> >>> I have moved the mention of the dash higher in the TODO file.
> >>
> >>
> >>Maybe you could put the dash in a different color (red?) so it stands out
> >>more. Or maybe the whole item could go in a different color if done./
> >>
> >
> > Yes, what about stroke through:
>
> Because neither works in the plain text version.
OK, I updated to the newest version of txt2html and got the line to
appear in bold.
I also removed some blank lines and reordered the descriptions:
http://developer.postgresql.org/todo.php
Any other suggestions on how to improve this?
--
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