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

From 74c935bf55ea697461725aab2e52520f9752b337 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 v4] 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                 | 58 +++++++++++++++------
 src/test/regress/expected/psql_crosstab.out | 28 ++++++++++
 src/test/regress/sql/psql_crosstab.sql      | 22 ++++++++
 3 files changed, 92 insertions(+), 16 deletions(-)

diff --git a/src/bin/psql/crosstabview.c b/src/bin/psql/crosstabview.c
index 111e8823bdb..2a2820cdeda 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"
@@ -82,6 +83,8 @@ static bool printCrosstab(const PGresult *result,
 						  int num_columns, pivot_field *piv_columns, int field_for_columns,
 						  int num_rows, pivot_field *piv_rows, int field_for_rows,
 						  int field_for_data);
+static char *printDisplayValue(char *value, Oid ftype, const printQueryOpt *opt,
+							   const char *default_null);
 static void avlInit(avl_tree *tree);
 static void avlMergeValue(avl_tree *tree, char *name, char *sort_value);
 static int	avlCollectFields(avl_tree *tree, avl_node *node,
@@ -292,6 +295,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 +308,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 +322,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 = printDisplayValue(piv_columns[horiz_map[i]].name,
+									col_ftype, &popt, "");
 
 		printTableAddHeader(&cont, colname, false, col_align);
 	}
@@ -335,10 +339,10 @@ 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] =
+			printDisplayValue(piv_rows[i].name, row_ftype, &popt, "");
 	}
 	cont.cellsadded = num_rows * (num_columns + 1);
 
@@ -384,6 +388,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 +399,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)"));
+							 printDisplayValue(rp->name, row_ftype, &popt,
+											   "(null)"),
+							 printDisplayValue(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] =
+				printDisplayValue(value, data_ftype, &popt, "");
 		}
 	}
 
@@ -426,6 +432,26 @@ error:
 	return retval;
 }
 
+/*
+ * Return the display representation of a crosstab value, following pset
+ * substitutions.  The returned pointer should not be freed.
+ */
+static char *
+printDisplayValue(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;
+}
+
 /*
  * The avl* functions below provide a minimalistic implementation of AVL binary
  * trees, to efficiently collect the distinct values that will form the horizontal
diff --git a/src/test/regress/expected/psql_crosstab.out b/src/test/regress/expected/psql_crosstab.out
index e09e3310165..5d368e251f7 100644
--- a/src/test/regress/expected/psql_crosstab.out
+++ b/src/test/regress/expected/psql_crosstab.out
@@ -136,6 +136,34 @@ GROUP BY v, h ORDER BY h,v
     |        |    |    | -3 | 
 (3 rows)
 
+\pset null ''
+-- boolean display
+\pset display_true 'Aye'
+\pset display_false 'Nay'
+\pset null 'Wut'
+SELECT false as display_bools, false as col_key, false as val
+UNION ALL
+SELECT true, true, true
+UNION ALL
+SELECT null, true, false
+UNION ALL
+SELECT null, false, true
+UNION ALL
+SELECT true, false, null
+UNION ALL
+SELECT true, null, false
+UNION ALL
+SELECT false, null, true
+ \crosstabview display_bools col_key val
+ display_bools | Nay | Aye | Wut 
+---------------+-----+-----+-----
+ Nay           | Nay |     | Aye
+ Aye           | Wut | Aye | Nay
+ Wut           | Aye | Nay | 
+(3 rows)
+
+\pset display_true 't'
+\pset display_false 'f'
 \pset null ''
 -- refer to columns by position
 SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n')
diff --git a/src/test/regress/sql/psql_crosstab.sql b/src/test/regress/sql/psql_crosstab.sql
index 5a4511389de..998bc3a4c4a 100644
--- a/src/test/regress/sql/psql_crosstab.sql
+++ b/src/test/regress/sql/psql_crosstab.sql
@@ -69,6 +69,28 @@ GROUP BY v, h ORDER BY h,v
  \crosstabview v h i
 \pset null ''
 
+-- boolean display
+\pset display_true 'Aye'
+\pset display_false 'Nay'
+\pset null 'Wut'
+SELECT false as display_bools, false as col_key, false as val
+UNION ALL
+SELECT true, true, true
+UNION ALL
+SELECT null, true, false
+UNION ALL
+SELECT null, false, true
+UNION ALL
+SELECT true, false, null
+UNION ALL
+SELECT true, null, false
+UNION ALL
+SELECT false, null, true
+ \crosstabview display_bools col_key val
+\pset display_true 't'
+\pset display_false 'f'
+\pset null ''
+
 -- 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)