v3-0001-Make-crosstabview-honor-boolean-display-settings.patch

From e0ea9c46847be406895ea4a6cb14cdd34565b4e3 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <lic@highgo.com>
Date: Wed, 3 Jun 2026 14:50:09 +0800
Subject: [PATCH v3] Make crosstabview honor boolean display settings
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

psql's \pset display_true and display_false settings were applied by
normal query output, but not by \crosstabview. As a result, boolean
values used as row labels, column labels, or data values in crosstab
output were always shown as "t" or "f".

Add a small crosstabview-local helper to apply the query display
substitutions for NULL and boolean values. This makes \crosstabview
respect the same display settings without changing the general print
interface.

Add a regression test covering boolean row keys, column keys, and data
values in \crosstabview.

Reported-by: Chao Li <lic@highgo.com>
Author: Chao Li <lic@highgo.com>
Reviewed-by: David G. Johnston <david.g.johnston@gmail.com>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
Discussion: https://postgr.es/m/B5E6F0A5-4B48-46D0-B5EB-CF8F8CC7D07D@gmail.com
---
 src/bin/psql/crosstabview.c                 | 60 +++++++++++++++------
 src/test/regress/expected/psql_crosstab.out | 15 ++++++
 src/test/regress/sql/psql_crosstab.sql      | 10 ++++
 3 files changed, 69 insertions(+), 16 deletions(-)

diff --git a/src/bin/psql/crosstabview.c b/src/bin/psql/crosstabview.c
index 111e8823bdb..aaa9b8a2451 100644
--- a/src/bin/psql/crosstabview.c
+++ b/src/bin/psql/crosstabview.c
@@ -7,6 +7,7 @@
  */
 #include "postgres_fe.h"
 
+#include "catalog/pg_type_d.h"
 #include "common.h"
 #include "common/int.h"
 #include "common/logging.h"
@@ -46,6 +47,29 @@ typedef struct _pivot_field
 	int			rank;
 } pivot_field;
 
+/*
+ * Return the display representation of a crosstab value, following pset
+ * substitutions.  The returned pointer should not be freed.
+ *
+ * TODO: In v20, consider whether this should become part of the public print.h
+ * interface, and with what name.
+ */
+static char *
+printQueryOptDisplayValue(char *value, Oid ftype, const printQueryOpt *opt,
+						  const char *default_null)
+{
+	if (value == NULL)
+		return opt->nullPrint ? opt->nullPrint :
+			unconstify(char *, default_null);
+
+	if (ftype == BOOLOID)
+		return value[0] == 't' ?
+			(opt->truePrint ? opt->truePrint : "t") :
+			(opt->falsePrint ? opt->falsePrint : "f");
+
+	return value;
+}
+
 /* Node in avl_tree */
 typedef struct _avl_node
 {
@@ -292,6 +316,9 @@ printCrosstab(const PGresult *result,
 				rn;
 	char		col_align;
 	int		   *horiz_map;
+	Oid			col_ftype = PQftype(result, field_for_columns);
+	Oid			row_ftype = PQftype(result, field_for_rows);
+	Oid			data_ftype = PQftype(result, field_for_data);
 	bool		retval = false;
 
 	printTableInit(&cont, &popt.topt, popt.title, num_columns + 1, num_rows);
@@ -302,8 +329,7 @@ printCrosstab(const PGresult *result,
 	printTableAddHeader(&cont,
 						PQfname(result, field_for_rows),
 						false,
-						column_type_alignment(PQftype(result,
-													  field_for_rows)));
+						column_type_alignment(row_ftype));
 
 	/*
 	 * To iterate over piv_columns[] by piv_columns[].rank, create a reverse
@@ -317,15 +343,14 @@ printCrosstab(const PGresult *result,
 	/*
 	 * The display alignment depends on its PQftype().
 	 */
-	col_align = column_type_alignment(PQftype(result, field_for_data));
+	col_align = column_type_alignment(data_ftype);
 
 	for (i = 0; i < num_columns; i++)
 	{
 		char	   *colname;
 
-		colname = piv_columns[horiz_map[i]].name ?
-			piv_columns[horiz_map[i]].name :
-			(popt.nullPrint ? popt.nullPrint : "");
+		colname = printQueryOptDisplayValue(piv_columns[horiz_map[i]].name,
+											col_ftype, &popt, "");
 
 		printTableAddHeader(&cont, colname, false, col_align);
 	}
@@ -335,10 +360,11 @@ printCrosstab(const PGresult *result,
 	for (i = 0; i < num_rows; i++)
 	{
 		int			k = piv_rows[i].rank;
+		int			idx = k * (num_columns + 1);
 
-		cont.cells[k * (num_columns + 1)] = piv_rows[i].name ?
-			piv_rows[i].name :
-			(popt.nullPrint ? popt.nullPrint : "");
+		cont.cells[idx] =
+			printQueryOptDisplayValue(piv_rows[i].name, row_ftype, &popt,
+									  "");
 	}
 	cont.cellsadded = num_rows * (num_columns + 1);
 
@@ -384,6 +410,7 @@ printCrosstab(const PGresult *result,
 		if (col_number >= 0 && row_number >= 0)
 		{
 			int			idx;
+			char	   *value = NULL;
 
 			/* index into the cont.cells array */
 			idx = 1 + col_number + row_number * (num_columns + 1);
@@ -394,16 +421,17 @@ printCrosstab(const PGresult *result,
 			if (cont.cells[idx] != NULL)
 			{
 				pg_log_error("\\crosstabview: query result contains multiple data values for row \"%s\", column \"%s\"",
-							 rp->name ? rp->name :
-							 (popt.nullPrint ? popt.nullPrint : "(null)"),
-							 cp->name ? cp->name :
-							 (popt.nullPrint ? popt.nullPrint : "(null)"));
+							 printQueryOptDisplayValue(rp->name, row_ftype,
+													   &popt, "(null)"),
+							 printQueryOptDisplayValue(cp->name, col_ftype,
+													   &popt, "(null)"));
 				goto error;
 			}
 
-			cont.cells[idx] = !PQgetisnull(result, rn, field_for_data) ?
-				PQgetvalue(result, rn, field_for_data) :
-				(popt.nullPrint ? popt.nullPrint : "");
+			if (!PQgetisnull(result, rn, field_for_data))
+				value = PQgetvalue(result, rn, field_for_data);
+			cont.cells[idx] =
+				printQueryOptDisplayValue(value, data_ftype, &popt, "");
 		}
 	}
 
diff --git a/src/test/regress/expected/psql_crosstab.out b/src/test/regress/expected/psql_crosstab.out
index e09e3310165..4660bfbf7f5 100644
--- a/src/test/regress/expected/psql_crosstab.out
+++ b/src/test/regress/expected/psql_crosstab.out
@@ -137,6 +137,21 @@ GROUP BY v, h ORDER BY h,v
 (3 rows)
 
 \pset null ''
+-- boolean display
+\pset display_true 'true'
+\pset display_false 'false'
+SELECT false as row_key, true as col_key, true as val
+UNION ALL
+SELECT true, false, false
+ \crosstabview row_key col_key val
+ row_key | true | false 
+---------+------+-------
+ false   | true | 
+ true    |      | false
+(2 rows)
+
+\pset display_true 't'
+\pset display_false 'f'
 -- refer to columns by position
 SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n')
 FROM ctv_data GROUP BY v, h ORDER BY h,v
diff --git a/src/test/regress/sql/psql_crosstab.sql b/src/test/regress/sql/psql_crosstab.sql
index 5a4511389de..00b395d7168 100644
--- a/src/test/regress/sql/psql_crosstab.sql
+++ b/src/test/regress/sql/psql_crosstab.sql
@@ -69,6 +69,16 @@ GROUP BY v, h ORDER BY h,v
  \crosstabview v h i
 \pset null ''
 
+-- boolean display
+\pset display_true 'true'
+\pset display_false 'false'
+SELECT false as row_key, true as col_key, true as val
+UNION ALL
+SELECT true, false, false
+ \crosstabview row_key col_key val
+\pset display_true 't'
+\pset display_false 'f'
+
 -- refer to columns by position
 SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n')
 FROM ctv_data GROUP BY v, h ORDER BY h,v
-- 
2.50.1 (Apple Git-155)