​Attached is a patch that provides a link to the catalog page for
pg_db_role_settings from config.sgml. It also updates said catalog page to
mention that the data from this catalog is available via psql \drds and
also provides a query that will explode the setconfig column into its
component parts - which \drds does not do.
I am imagine there might be a reason that the catalog pages do not
reference the relevant psql meta-commands, or provide human-friendly
examples, but I cannot think of what that might be and so here I provide.
David J.
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 9ceb96b..43d2774 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -2759,6 +2759,40 @@
</tbody>
</tgroup>
</table>
+
+ <para>
+ The following query provides a more user-friendly interface for browsing
+ this catalog table. A similar view, without the explosion of the
+ <structfield>pg_db_role_setting.setconfig</structfield> field,
+ can be executed in <application>psql</> via the
+ <literal>\drds</literal> meta-command.
+<programlisting>
+SELECT
+ affected_db,
+ affected_role,
+ config_parts[1] AS name, --matches pg_settings
+ config_parts[2] AS db_role_val,
+ setdatabase,
+ setrole,
+ setconfig
+FROM (
+ --key=value pairs need to be split on the first equal-sign
+ SELECT *, (SELECT regexp_matches(config_item, '^([^=]+)=(.*)')) AS
config_parts
+ FROM (
+ -- resolve the oid values to names
+ -- and also unnest the array of configuration key=value pairs
+ SELECT *
+ , COALESCE(datname, 'All Databases') || ' (' || setdatabase || ')' AS
affected_db
+ , COALESCE(rolname, 'All Roles') || ' (' || setrole || ')' AS
affected_role
+ , unnest(setconfig) AS config_item
+ FROM pg_db_role_setting
+ LEFT JOIN pg_database ON (setdatabase = pg_database.oid)
+ LEFT JOIN pg_authid ON (setrole = pg_authid.oid)
+ ) itemize_config
+) match_config_parts
+</programlisting>
+ </para>
+
</sect1>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 6bcb106..e4519a3 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -213,6 +213,9 @@ shared_buffers = 128MB
command line, and constitute defaults for the rest of the session.
Note that some settings cannot be changed after server start, and
so cannot be set with these commands (or the ones listed below).
+ To see the current inventory of combinations you can query the
+ <link linkend="catalog-pg-db-role-setting">
+ <structname>pg_db_role_setting</structname></link> catalog table.
</para>
<para>
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
