Tom Lane escribió:
> Alvaro Herrera <alvhe...@commandprompt.com> writes:
> > 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.
>
> Why not two pattern arguments?
>
> \drds [ role-pattern [ db-pattern ]]
Hmm, interesting idea, patch attached. This required changing the API
of processSQLNamePattern to return a bool indicating whether a clause
was added; otherwise, when processing the second pattern it was
impossible to figure out if we needed a WHERE or not.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
*** src/bin/pg_dump/dumputils.c 4 Aug 2009 21:56:08 -0000 1.48
--- src/bin/pg_dump/dumputils.c 30 Sep 2009 20:58:27 -0000
***************
*** 894,900 ****
*
* Scan a wildcard-pattern string and generate appropriate WHERE clauses
* to limit the set of objects returned. The WHERE clauses are appended
! * to the already-partially-constructed query in buf.
*
* conn: connection query will be sent to (consulted for escaping rules).
* buf: output parameter.
--- 894,901 ----
*
* Scan a wildcard-pattern string and generate appropriate WHERE clauses
* to limit the set of objects returned. The WHERE clauses are appended
! * to the already-partially-constructed query in buf. Returns whether
! * any clause was added.
*
* conn: connection query will be sent to (consulted for escaping rules).
* buf: output parameter.
***************
*** 913,919 ****
* Formatting note: the text already present in buf should end with a newline.
* The appended text, if any, will end with one too.
*/
! void
processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
bool have_where, bool force_escape,
const char *schemavar, const char *namevar,
--- 914,920 ----
* Formatting note: the text already present in buf should end with a newline.
* The appended text, if any, will end with one too.
*/
! bool
processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
bool have_where, bool force_escape,
const char *schemavar, const char *namevar,
***************
*** 925,933 ****
bool inquotes;
const char *cp;
int i;
#define WHEREAND() \
! (appendPQExpBufferStr(buf, have_where ? " AND " : "WHERE "), have_where = true)
if (pattern == NULL)
{
--- 926,936 ----
bool inquotes;
const char *cp;
int i;
+ bool added_clause = false;
#define WHEREAND() \
! (appendPQExpBufferStr(buf, have_where ? " AND " : "WHERE "), \
! have_where = true, added_clause = true)
if (pattern == NULL)
{
***************
*** 937,943 ****
WHEREAND();
appendPQExpBuffer(buf, "%s\n", visibilityrule);
}
! return;
}
initPQExpBuffer(&schemabuf);
--- 940,946 ----
WHEREAND();
appendPQExpBuffer(buf, "%s\n", visibilityrule);
}
! return added_clause;
}
initPQExpBuffer(&schemabuf);
***************
*** 1094,1098 ****
--- 1097,1102 ----
termPQExpBuffer(&schemabuf);
termPQExpBuffer(&namebuf);
+ return added_clause;
#undef WHEREAND
}
*** src/bin/pg_dump/dumputils.h 4 Aug 2009 21:56:08 -0000 1.25
--- src/bin/pg_dump/dumputils.h 30 Sep 2009 20:57:42 -0000
***************
*** 36,42 ****
const char *type, const char *acls, const char *owner,
int remoteVersion,
PQExpBuffer sql);
! extern void processSQLNamePattern(PGconn *conn, PQExpBuffer buf,
const char *pattern,
bool have_where, bool force_escape,
const char *schemavar, const char *namevar,
--- 36,42 ----
const char *type, const char *acls, const char *owner,
int remoteVersion,
PQExpBuffer sql);
! extern bool processSQLNamePattern(PGconn *conn, PQExpBuffer buf,
const char *pattern,
bool have_where, bool force_escape,
const char *schemavar, const char *namevar,
*** src/bin/psql/command.c 13 Sep 2009 22:18:22 -0000 1.207
--- src/bin/psql/command.c 30 Sep 2009 20:47:53 -0000
***************
*** 408,413 ****
--- 408,426 ----
case 's':
success = listTables(&cmd[1], pattern, show_verbose, show_system);
break;
+ case 'r':
+ if (cmd[2] == 'd' && cmd[3] == 's')
+ {
+ char *pattern2 = NULL;
+
+ if (pattern)
+ pattern2 = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, true);
+ success = listDbRoleSettings(pattern, pattern2);
+ }
+ else
+ success = PSQL_CMD_UNKNOWN;
+ break;
case 'u':
success = describeRoles(pattern, show_verbose);
break;
*** src/bin/psql/describe.c 29 Jul 2009 20:56:19 -0000 1.226
--- src/bin/psql/describe.c 30 Sep 2009 20:59:13 -0000
***************
*** 2176,2181 ****
--- 2176,2240 ----
appendPQExpBufferStr(buf, str);
}
+ /*
+ * \drds
+ */
+ bool
+ listDbRoleSettings(const char *pattern, const char *pattern2)
+ {
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ initPQExpBuffer(&buf);
+
+ if (pset.sversion >= 80500)
+ {
+ bool havewhere;
+
+ 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\n");
+ havewhere = processSQLNamePattern(pset.db, &buf, pattern, false, false,
+ NULL, "pg_roles.rolname", NULL, NULL);
+ processSQLNamePattern(pset.db, &buf, pattern2, havewhere, false,
+ NULL, "pg_database.datname", NULL, NULL);
+ appendPQExpBufferStr(&buf, "ORDER BY role, database");
+ }
+ else
+ {
+ fprintf(pset.queryFout,
+ _("No per-database role settings support in this server version.\n"));
+ return false;
+ }
+
+ res = PSQLexec(buf.data, false);
+ if (!res)
+ return false;
+
+ if (PQntuples(res) == 0 && !pset.quiet)
+ {
+ if (pattern)
+ fprintf(pset.queryFout, _("No matching settings 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 20:34:54 -0000
***************
*** 27,32 ****
--- 27,35 ----
/* \du, \dg */
extern bool describeRoles(const char *pattern, bool verbose);
+ /* \drds */
+ extern bool listDbRoleSettings(const char *pattern1, const char *pattern2);
+
/* \z (or \dp) */
extern bool permissionsList(const char *pattern);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
