Tom Lane escribió:
> BTW, have we thought much about the simplest possible solution,
> which is to not have the view? How badly do we need it? Seems
> like dropping the functionality into a psql \d command might be
> a viable alternative.
FWIW I came up with a preliminary patch for a new psql command \dus that
shows settings. It takes a pattern that's used to constrain on roles.
Thus there is no way to view settings for a database. If there's a need
for that we could use another command, say \dls.
Sample output
alvherre=# \dus fo*
List of settings
role | database | settings
------+----------+-----------------------
fob | | log_duration=true
foo | alvherre | work_mem=256MB
: statement_timeout=10s
foo | | work_mem=512MB
: statement_timeout=1s
(3 rows)
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
*** src/bin/psql/command.c 13 Sep 2009 22:18:22 -0000 1.207
--- src/bin/psql/command.c 30 Sep 2009 19:28:07 -0000
***************
*** 409,415 ****
success = listTables(&cmd[1], pattern, show_verbose, show_system);
break;
case 'u':
! success = describeRoles(pattern, show_verbose);
break;
case 'F': /* text search subsystem */
switch (cmd[2])
--- 409,418 ----
success = listTables(&cmd[1], pattern, show_verbose, show_system);
break;
case 'u':
! if (cmd[2] && cmd[2] == 's')
! success = listRoleSettings(pattern);
! else
! success = describeRoles(pattern, show_verbose);
break;
case 'F': /* text search subsystem */
switch (cmd[2])
*** src/bin/psql/describe.c 29 Jul 2009 20:56:19 -0000 1.226
--- src/bin/psql/describe.c 30 Sep 2009 19:54:42 -0000
***************
*** 2176,2181 ****
--- 2176,2232 ----
appendPQExpBufferStr(buf, str);
}
+ /*
+ * \dus
+ */
+ bool
+ listRoleSettings(const char *pattern)
+ {
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ initPQExpBuffer(&buf);
+
+ if (pset.sversion >= 80500)
+ {
+ printfPQExpBuffer(&buf, "SELECT rolname AS role, datname AS database,\n"
+ "pg_catalog.array_to_string(setconfig, E'\\n') AS settings\n"
+ "FROM pg_db_role_setting AS s\n"
+ "LEFT JOIN pg_database ON pg_database.oid = setdatabase\n"
+ "LEFT JOIN pg_roles ON pg_roles.oid = setrole ");
+ processSQLNamePattern(pset.db, &buf, pattern, false, false,
+ NULL, "pg_roles.rolname", NULL, NULL);
+ appendPQExpBufferStr(&buf, " ORDER BY role, database");
+ }
+ else
+ return false;
+
+ res = PSQLexec(buf.data, false);
+ if (!res)
+ return false;
+
+ if (PQntuples(res) == 0 && !pset.quiet)
+ {
+ if (pattern)
+ fprintf(pset.queryFout, _("No matching roles found.\n"));
+ else
+ fprintf(pset.queryFout, _("No settings found.\n"));
+ }
+ else
+ {
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of settings");
+ myopt.translate_header = true;
+
+ printQuery(res, &myopt, pset.queryFout, pset.logfile);
+ }
+
+ PQclear(res);
+ resetPQExpBuffer(&buf);
+ return true;
+ }
+
/*
* listTables()
*** src/bin/psql/describe.h 21 Apr 2009 15:49:06 -0000 1.40
--- src/bin/psql/describe.h 30 Sep 2009 19:29:20 -0000
***************
*** 27,32 ****
--- 27,35 ----
/* \du, \dg */
extern bool describeRoles(const char *pattern, bool verbose);
+ /* \dus */
+ extern bool listRoleSettings(const char *pattern);
+
/* \z (or \dp) */
extern bool permissionsList(const char *pattern);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
