Don't show unusable collations in psql's \dO command.

tglsfdc · tglsfdc · commit 7c76906b7e24 · 2011-04-09T14:08:41.000-04:00
"Unusable" collations are those not matching the current database's
encoding.  The former behavior inconsistently showed such collations
some of the time, depending on the details of the pattern argument.
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
@@ -1264,7 +1264,7 @@ testdb=&gt;
         <term><literal>\dL[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
         <listitem>
         <para>
-        Lists all procedural languages. If <replaceable
+        Lists procedural languages. If <replaceable
         class="parameter">pattern</replaceable>
         is specified, only languages whose names match the pattern are listed.
         By default, only user-created languages
@@ -1311,7 +1311,6 @@ testdb=&gt;
 
       <varlistentry>
         <term><literal>\dO[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
-
         <listitem>
         <para>
         Lists collations.
@@ -1320,8 +1319,11 @@ testdb=&gt;
         listed.  By default, only user-created objects are shown;
         supply a pattern or the <literal>S</literal> modifier to
         include system objects.  If <literal>+</literal> is appended
-        to the command name, each object is listed with its associated
+        to the command name, each collation is listed with its associated
         description, if any.
+        Note that only collations usable with the current database's encoding
+        are shown, so the results may vary in different databases of the
+        same installation.
         </para>
         </listitem>
       </varlistentry>
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
@@ -2883,7 +2883,7 @@ listCasts(const char *pattern)
 /*
  * \dO
  *
- * Describes collations
+ * Describes collations.
  */
 bool
 listCollations(const char *pattern, bool verbose, bool showSystem)
@@ -2907,17 +2907,25 @@ listCollations(const char *pattern, bool verbose, bool showSystem)
 
 	if (verbose)
 		appendPQExpBuffer(&buf,
-		  ",\n  pg_catalog.obj_description(c.oid, 'pg_collation') AS \"%s\"",
+		  ",\n       pg_catalog.obj_description(c.oid, 'pg_collation') AS \"%s\"",
 						  gettext_noop("Description"));
 
 	appendPQExpBuffer(&buf,
-					  "FROM pg_catalog.pg_collation c, pg_catalog.pg_namespace n\n"
+					  "\nFROM pg_catalog.pg_collation c, pg_catalog.pg_namespace n\n"
 					  "WHERE n.oid = c.collnamespace\n");
 
 	if (!showSystem && !pattern)
 		appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n"
 						  "      AND n.nspname <> 'information_schema'\n");
 
+	/*
+	 * Hide collations that aren't usable in the current database's encoding.
+	 * If you think to change this, note that pg_collation_is_visible rejects
+	 * unusable collations, so you will need to hack name pattern processing
+	 * somehow to avoid inconsistent behavior.
+	 */
+	appendPQExpBuffer(&buf, "      AND c.collencoding IN (-1, pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding()))\n");
+
 	processSQLNamePattern(pset.db, &buf, pattern, true, false,
 						  "n.nspname", "c.collname", NULL,
 						  "pg_catalog.pg_collation_is_visible(c.oid)");
