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

From 7b89c1a0e296e1d1ac1fb659d5fc706c4b14bd22 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 v2] Make crosstabview honor boolean display settings

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 helper to apply the query display substitutions for NULL and
boolean values, and use it from both printQuery() and crosstabview
printing. This keeps the existing query-output behavior while making
\crosstabview respect the same display settings.

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

Author: Chao Li <lic@highgo.com>
Reviewed-by: David G. Johnston <david.g.johnston@gmail.com>
Discussion: https://postgr.es/m/B5E6F0A5-4B48-46D0-B5EB-CF8F8CC7D07D@gmail.com
---
 src/bin/psql/crosstabview.c                 | 44 ++++++++++------
 src/fe_utils/print.c                        | 58 +++++++++++++++------
 src/test/regress/expected/psql_crosstab.out | 15 ++++++
 src/test/regress/sql/psql_crosstab.sql      | 10 ++++
 4 files changed, 96 insertions(+), 31 deletions(-)

diff --git a/src/bin/psql/crosstabview.c b/src/bin/psql/crosstabview.c
index 111e8823bdb..66284971a13 100644
--- a/src/bin/psql/crosstabview.c
+++ b/src/bin/psql/crosstabview.c
@@ -15,6 +15,14 @@
 #include "psqlscanslash.h"
 #include "settings.h"
 
+/*
+ * TODO: Decide in v20 whether this should become part of the public print.h
+ * interface, and with what name.
+ */
+extern char *printQueryOptDisplayValue(char *value, Oid ftype,
+									   const printQueryOpt *opt,
+									   const char *default_null);
+
 /*
  * Value/position from the resultset that goes into the horizontal or vertical
  * crosstabview header.
@@ -292,6 +300,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 +313,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 +327,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 +344,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 +394,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 +405,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/fe_utils/print.c b/src/fe_utils/print.c
index bfbaf094d7e..be49a5923fa 100644
--- a/src/fe_utils/print.c
+++ b/src/fe_utils/print.c
@@ -269,7 +269,7 @@ static const unicodeStyleFormat unicode_style = {
 };
 
 
-/* Local functions */
+/* Function prototypes */
 static int	strlen_max_width(unsigned char *str, int *target_width, int encoding);
 static FILE *PageOutputInternal(int lines, const printTableOpt *topt,
 								const printTableContent *cont,
@@ -288,6 +288,13 @@ static int	count_table_lines(const printTableContent *cont,
 static void print_aligned_vertical(const printTableContent *cont,
 								   FILE *fout, bool is_pager);
 
+/*
+ * TODO: Decide in v20 whether this should become part of the public print.h
+ * interface, and with what name.
+ */
+extern char *printQueryOptDisplayValue(char *value, Oid ftype,
+									   const printQueryOpt *opt,
+									   const char *default_null);
 
 /* Count number of digits in integral part of number */
 static int
@@ -3730,6 +3737,31 @@ printTable(const printTableContent *cont,
 		print_aligned_text(cont, flog, false);
 }
 
+/*
+ * Return the display representation of a query value, following pset
+ * substitutions.  The returned pointer should not be freed.
+ *
+ * value: value to display, or NULL for a null value
+ * ftype: field type of value
+ * opt: formatting options
+ * default_null: default display for a null value if opt->nullPrint isn't set
+ */
+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;
+}
+
 /*
  * Use this to print query results
  *
@@ -3770,25 +3802,21 @@ printQuery(const PGresult *result, const printQueryOpt *opt,
 	{
 		for (c = 0; c < cont.ncolumns; c++)
 		{
-			char	   *cell;
+			char	   *cell = PQgetvalue(result, r, c);
+			bool		isnull = PQgetisnull(result, r, c);
 			bool		mustfree = false;
 			bool		translate;
+			Oid			ftype = PQftype(result, c);
 
-			if (PQgetisnull(result, r, c))
-				cell = opt->nullPrint ? opt->nullPrint : "";
-			else if (PQftype(result, c) == BOOLOID)
-				cell = (PQgetvalue(result, r, c)[0] == 't' ?
-						(opt->truePrint ? opt->truePrint : "t") :
-						(opt->falsePrint ? opt->falsePrint : "f"));
-			else
+			if (!isnull && ftype != BOOLOID && cont.aligns[c] == 'r' &&
+				opt->topt.numericLocale)
 			{
-				cell = PQgetvalue(result, r, c);
-				if (cont.aligns[c] == 'r' && opt->topt.numericLocale)
-				{
-					cell = format_numeric_locale(cell);
-					mustfree = true;
-				}
+				cell = format_numeric_locale(cell);
+				mustfree = true;
 			}
+			else
+				cell = printQueryOptDisplayValue(isnull ? NULL : cell,
+												 ftype, opt, "");
 
 			translate = (opt->translate_columns && opt->translate_columns[c]);
 			printTableAddCell(&cont, cell, translate, mustfree);
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)