I've attached a patch, against current 8.4 cvs, which optionally sets a
maximum width for psql output:
# \pset format aligned-wrapped
# \pset border 2
# select * from distributors order by did;
+------+--------------------+---------------------+---------------+
| did | name | descr | long_col_name |
+------+--------------------+---------------------+---------------+
| 1 | Food fish and wine | default | |
| 2 | Cat Food Heaven 2 | abcdefghijklmnopqrs ! |
| | | tuvwxyz | |
| 3 | Cat Food Heaven 3 | default | |
| 10 | Lah | default | |
| 12 | name | line one | |
| 2892 ! short name | short | |
| 8732 | | | |
+------+--------------------+---------------------+---------------+
(8 rows)
The interactive terminal column width comes from
char * temp = getenv("COLUMNS");
Which has the strong advantage of great simplicity and portability. But
it may not be 1000% universal. If $COLUMNS is not defined, the code
bails to assuming an infinitely wide terminal.
I will also backport this to Postgres 8.1, for my own use. Though the
code is almost totally different in structure.
Bryce Nesbitt
City CarShare San Francisco
? psql
? psql_wrapping.patch
Index: command.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.186
diff -c -r1.186 command.c
*** command.c 1 Jan 2008 19:45:55 -0000 1.186
--- command.c 5 Mar 2008 20:57:05 -0000
***************
*** 1526,1531 ****
--- 1526,1534 ----
case PRINT_ALIGNED:
return "aligned";
break;
+ case PRINT_ALIGNEDWRAP:
+ return "aligned-wrapped";
+ break;
case PRINT_HTML:
return "html";
break;
***************
*** 1559,1564 ****
--- 1562,1569 ----
popt->topt.format = PRINT_UNALIGNED;
else if (pg_strncasecmp("aligned", value, vallen) == 0)
popt->topt.format = PRINT_ALIGNED;
+ else if (pg_strncasecmp("aligned-wrapped", value, vallen) == 0)
+ popt->topt.format = PRINT_ALIGNEDWRAP;
else if (pg_strncasecmp("html", value, vallen) == 0)
popt->topt.format = PRINT_HTML;
else if (pg_strncasecmp("latex", value, vallen) == 0)
***************
*** 1567,1573 ****
popt->topt.format = PRINT_TROFF_MS;
else
{
! psql_error("\\pset: allowed formats are unaligned, aligned, html, latex, troff-ms\n");
return false;
}
--- 1572,1578 ----
popt->topt.format = PRINT_TROFF_MS;
else
{
! psql_error("\\pset: allowed formats are unaligned, aligned, aligned-wrapped, html, latex, troff-ms\n");
return false;
}
Index: mbprint.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/mbprint.c,v
retrieving revision 1.29
diff -c -r1.29 mbprint.c
*** mbprint.c 1 Jan 2008 19:45:56 -0000 1.29
--- mbprint.c 5 Mar 2008 20:57:06 -0000
***************
*** 205,211 ****
* pg_wcssize takes the given string in the given encoding and returns three
* values:
* result_width: Width in display character of longest line in string
! * result_hieght: Number of lines in display output
* result_format_size: Number of bytes required to store formatted representation of string
*/
int
--- 205,211 ----
* pg_wcssize takes the given string in the given encoding and returns three
* values:
* result_width: Width in display character of longest line in string
! * result_height: Number of lines in display output
* result_format_size: Number of bytes required to store formatted representation of string
*/
int
***************
*** 279,284 ****
--- 279,288 ----
return width;
}
+ /*
+ ** Filter out unprintable characters, companion to wcs_size.
+ ** Break input into lines (based on \n or \r).
+ */
void
pg_wcsformat(unsigned char *pwcs, size_t len, int encoding,
struct lineptr * lines, int count)
***************
*** 353,364 ****
}
len -= chlen;
}
! *ptr++ = '\0';
lines->width = linewidth;
! lines++;
! count--;
! if (count > 0)
! lines->ptr = NULL;
}
unsigned char *
--- 357,370 ----
}
len -= chlen;
}
! *ptr++ = '\0'; // Terminate formatted string
lines->width = linewidth;
! // Fill remaining array slots with null
! while(--count) {
! lines++;
! lines->ptr = NULL;
! lines->width = 0;
! }
}
unsigned char *
Index: print.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/print.c,v
retrieving revision 1.96
diff -c -r1.96 print.c
*** print.c 1 Jan 2008 19:45:56 -0000 1.96
--- print.c 5 Mar 2008 20:57:07 -0000
***************
*** 398,403 ****
--- 398,406 ----
}
+ //
+ // Prety pretty boxes around cells.
+ //
static void
print_aligned_text(const char *title, const char *const * headers,
const char *const * cells, const char *const * footers,
***************
*** 406,431 ****
{
bool opt_tuples_only = opt->tuples_only;
bool opt_numeric_locale = opt->numericLocale;
- unsigned short int opt_border = opt->border;
int encoding = opt->encoding;
unsigned int col_count = 0;
! unsigned int cell_count = 0;
! unsigned int i;
int tmp;
! unsigned int *widths,
! total_w;
! unsigned int *heights;
! unsigned int *format_space;
unsigned char **format_buf;
const char *const * ptr;
! struct lineptr **col_lineptrs; /* pointers to line pointer for each
! * column */
! struct lineptr *lineptr_list; /* complete list of linepointers */
int *complete; /* Array remembering which columns have
* completed output */
if (cancel_pressed)
return;
--- 409,436 ----
{
bool opt_tuples_only = opt->tuples_only;
bool opt_numeric_locale = opt->numericLocale;
int encoding = opt->encoding;
+ unsigned short int opt_border = opt->border;
+
unsigned int col_count = 0;
!
! unsigned int i,j;
int tmp;
!
! unsigned int *width_header, *width_max, *width_wrap, *width_average;
! unsigned int *heights, *format_space;
unsigned char **format_buf;
+ unsigned int total_w;
const char *const * ptr;
! struct lineptr **col_lineptrs; /* pointers to line pointer per column */
! struct lineptr *lineptr_list; /* complete list of linepointers */
int *complete; /* Array remembering which columns have
* completed output */
+ int tcolumns = 0; // Width of interactive console
+ int rows=0; // SQL rows in result (calculated)
if (cancel_pressed)
return;
***************
*** 439,446 ****
if (col_count > 0)
{
! widths = pg_local_calloc(col_count, sizeof(*widths));
! heights = pg_local_calloc(col_count, sizeof(*heights));
col_lineptrs = pg_local_calloc(col_count, sizeof(*col_lineptrs));
format_space = pg_local_calloc(col_count, sizeof(*format_space));
format_buf = pg_local_calloc(col_count, sizeof(*format_buf));
--- 444,454 ----
if (col_count > 0)
{
! width_header = pg_local_calloc(col_count, sizeof(*width_header));
! width_average = pg_local_calloc(col_count, sizeof(*width_average));
! width_max = pg_local_calloc(col_count, sizeof(*width_max));
! width_wrap = pg_local_calloc(col_count, sizeof(*width_wrap));
! heights = pg_local_calloc(col_count, sizeof(*heights));
col_lineptrs = pg_local_calloc(col_count, sizeof(*col_lineptrs));
format_space = pg_local_calloc(col_count, sizeof(*format_space));
format_buf = pg_local_calloc(col_count, sizeof(*format_buf));
***************
*** 448,466 ****
}
else
{
! widths = NULL;
! heights = NULL;
col_lineptrs = NULL;
format_space = NULL;
format_buf = NULL;
complete = NULL;
}
! /* count cells (rows * cols) */
! for (ptr = cells; *ptr; ptr++)
! cell_count++;
!
! /* calc column widths */
for (i = 0; i < col_count; i++)
{
/* Get width & height */
--- 456,473 ----
}
else
{
! width_header = NULL;
! width_average = NULL;
! width_max = NULL;
! width_wrap = NULL;
! heights = NULL;
col_lineptrs = NULL;
format_space = NULL;
format_buf = NULL;
complete = NULL;
}
! /* scan all column headers, find maximum width */
for (i = 0; i < col_count; i++)
{
/* Get width & height */
***************
*** 468,503 ****
space;
pg_wcssize((unsigned char *) headers[i], strlen(headers[i]), encoding, &tmp, &height, &space);
! if (tmp > widths[i])
! widths[i] = tmp;
if (height > heights[i])
heights[i] = height;
if (space > format_space[i])
format_space[i] = space;
}
for (i = 0, ptr = cells; *ptr; ptr++, i++)
{
! int numeric_locale_len;
! int height,
! space;
!
! if (opt_align[i % col_count] == 'r' && opt_numeric_locale)
! numeric_locale_len = additional_numeric_locale_len(*ptr);
! else
! numeric_locale_len = 0;
/* Get width, ignore height */
pg_wcssize((unsigned char *) *ptr, strlen(*ptr), encoding, &tmp, &height, &space);
! tmp += numeric_locale_len;
! if (tmp > widths[i % col_count])
! widths[i % col_count] = tmp;
if (height > heights[i % col_count])
heights[i % col_count] = height;
if (space > format_space[i % col_count])
format_space[i % col_count] = space;
}
if (opt_border == 0)
total_w = col_count - 1;
else if (opt_border == 1)
--- 475,518 ----
space;
pg_wcssize((unsigned char *) headers[i], strlen(headers[i]), encoding, &tmp, &height, &space);
! if (tmp > width_max[i])
! width_max[i] = tmp;
if (height > heights[i])
heights[i] = height;
if (space > format_space[i])
format_space[i] = space;
+
+ width_header[i] = tmp;
}
+ /* scan all rows, find maximum width */
for (i = 0, ptr = cells; *ptr; ptr++, i++)
{
! int height, space;
/* Get width, ignore height */
pg_wcssize((unsigned char *) *ptr, strlen(*ptr), encoding, &tmp, &height, &space);
! if (opt_numeric_locale && opt_align[i % col_count] == 'r') {
! tmp += additional_numeric_locale_len(*ptr);
! space += additional_numeric_locale_len(*ptr);
! }
!
! if (tmp > width_max[i % col_count])
! width_max[i % col_count] = tmp;
if (height > heights[i % col_count])
heights[i % col_count] = height;
if (space > format_space[i % col_count])
format_space[i % col_count] = space;
+
+ width_average[i % col_count] += tmp;
}
+ rows=(i / col_count);
+ for (i = 0; i < col_count; i++) {
+ if( rows )
+ width_average[i % col_count] /= rows;
+ }
+ /* fiddle total display width based on border style */
if (opt_border == 0)
total_w = col_count - 1;
else if (opt_border == 1)
***************
*** 505,518 ****
else
total_w = col_count * 3 + 1;
! for (i = 0; i < col_count; i++)
! total_w += widths[i];
/*
! * At this point: widths contains the max width of each column heights
! * contains the max height of a cell of each column format_space contains
! * maximum space required to store formatted string so we prepare the
! * formatting structures
*/
if (col_count > 0)
{
--- 520,536 ----
else
total_w = col_count * 3 + 1;
! for (i = 0; i < col_count; i++) {
! total_w += width_max[i];
! }
/*
! * At this point:
! * width_max[] contains the max width of each column
! * heights[] contains the max number of lines in each column
! * format_space[] contains the maximum storage space for formatting strings
! * total_w contains the giant width sum
! * Now we allocate some memory...
*/
if (col_count > 0)
{
***************
*** 529,535 ****
col_lineptrs[i] = lineptr;
lineptr += heights[i];
! format_buf[i] = pg_local_malloc(format_space[i]);
col_lineptrs[i]->ptr = format_buf[i];
}
--- 547,553 ----
col_lineptrs[i] = lineptr;
lineptr += heights[i];
! format_buf[i] = pg_local_malloc(format_space[i]+1);
col_lineptrs[i]->ptr = format_buf[i];
}
***************
*** 537,555 ****
else
lineptr_list = NULL;
if (opt->start_table)
{
/* print title */
if (title && !opt_tuples_only)
{
- /* Get width & height */
int height;
-
pg_wcssize((unsigned char *) title, strlen(title), encoding, &tmp, &height, NULL);
if (tmp >= total_w)
! fprintf(fout, "%s\n", title);
else
! fprintf(fout, "%-*s%s\n", (total_w - tmp) / 2, "", title);
}
/* print headers */
--- 555,635 ----
else
lineptr_list = NULL;
+
+ /*
+ ** Default the word wrap to the full width (e.g. no word wrap)
+ */
+ for (i = 0; i < col_count; i++)
+ {
+ width_wrap[i] = width_max[i];
+ }
+
+ /*
+ ** Optional optimized word wrap.
+ ** Shrink columns with a high max/avg ratio.
+ ** Slighly bias against wider columns (increasnig chance
+ ** a narrow column will fit in its cell)
+ **
+ ** Created March 2008, Bryce Nesbitt
+ */
+ if( opt->format == PRINT_ALIGNEDWRAP )
+ {
+ // If we can get the terminal width
+ char * temp = getenv("COLUMNS");
+ if( temp != NULL )
+ {
+ tcolumns = atoi(temp);
+
+ // Shink high ratio columns
+ while( total_w > tcolumns)
+ {
+ double ratio = 0;
+ double temp = 0;
+ int worstcol = -1;
+ for (i = 0; i < col_count; i++)
+ if( width_average[i] )
+ if( width_wrap[i] > width_header[i] )
+ {
+ temp =(double)width_wrap[i]/width_average[i];
+ temp +=width_max[i] * .01; // Penalize wide columns
+ if( temp > ratio )
+ ratio = temp, worstcol=i;
+ }
+
+ // Debugging -- please leave in source!
+ if( true )
+ {
+ fprintf(fout, "Wrap ratio=");
+ for (i = 0; i < col_count; i++)
+ fprintf(fout, "%f %f ",(double)width_wrap[i]/width_average[i] + width_max[i] * .01, width_max[i] * .01);
+ fprintf(fout, "\n");
+ }
+
+ // Exit loop if we can't squeeze any more.
+ if( worstcol < 0 )
+ break;
+
+ // Squeeze the worst column. Lather, rinse, repeat
+ width_wrap[worstcol]--;
+ total_w--;
+ }
+ }
+ }
+
+ /*
+ ** Woo, hoo, time to output
+ */
if (opt->start_table)
{
/* print title */
if (title && !opt_tuples_only)
{
int height;
pg_wcssize((unsigned char *) title, strlen(title), encoding, &tmp, &height, NULL);
if (tmp >= total_w)
! fprintf(fout, "%s\n", title); // Aligned
else
! fprintf(fout, "%-*s%s\n", (total_w - tmp) / 2, "", title); // Centered
}
/* print headers */
***************
*** 559,565 ****
int line_count;
if (opt_border == 2)
! _print_horizontal_line(col_count, widths, opt_border, fout);
for (i = 0; i < col_count; i++)
pg_wcsformat((unsigned char *) headers[i], strlen(headers[i]), encoding, col_lineptrs[i], heights[i]);
--- 639,645 ----
int line_count;
if (opt_border == 2)
! _print_horizontal_line(col_count, width_wrap, opt_border, fout);
for (i = 0; i < col_count; i++)
pg_wcsformat((unsigned char *) headers[i], strlen(headers[i]), encoding, col_lineptrs[i], heights[i]);
***************
*** 582,588 ****
if (!complete[i])
{
! nbspace = widths[i] - this_line->width;
/* centered */
fprintf(fout, "%-*s%s%-*s",
--- 662,668 ----
if (!complete[i])
{
! nbspace = width_wrap[i] - this_line->width;
/* centered */
fprintf(fout, "%-*s%s%-*s",
***************
*** 595,601 ****
}
}
else
! fprintf(fout, "%*s", widths[i], "");
if (i < col_count - 1)
{
if (opt_border == 0)
--- 675,681 ----
}
}
else
! fprintf(fout, "%*s", width_wrap[i], "");
if (i < col_count - 1)
{
if (opt_border == 0)
***************
*** 613,713 ****
fputc('\n', fout);
}
! _print_horizontal_line(col_count, widths, opt_border, fout);
}
}
/* print cells */
for (i = 0, ptr = cells; *ptr; i += col_count, ptr += col_count)
{
! int j;
! int cols_todo = col_count;
! int line_count; /* Number of lines output so far in row */
if (cancel_pressed)
break;
for (j = 0; j < col_count; j++)
pg_wcsformat((unsigned char *) ptr[j], strlen(ptr[j]), encoding, col_lineptrs[j], heights[j]);
! line_count = 0;
memset(complete, 0, col_count * sizeof(int));
! while (cols_todo)
! {
! /* beginning of line */
! if (opt_border == 2)
! fputs("| ", fout);
! else if (opt_border == 1)
! fputc(' ', fout);
!
! for (j = 0; j < col_count; j++)
! {
! struct lineptr *this_line = col_lineptrs[j] + line_count;
! bool finalspaces = (opt_border == 2 || j != col_count - 1);
!
! if (complete[j]) /* Just print spaces... */
! {
! if (finalspaces)
! fprintf(fout, "%*s", widths[j], "");
! }
! else
! {
! /* content */
! if (opt_align[j] == 'r')
! {
! if (opt_numeric_locale)
! {
! /*
! * Assumption: This code used only on strings
! * without multibyte characters, otherwise
! * this_line->width < strlen(this_ptr) and we get
! * an overflow
! */
! char *my_cell = format_numeric_locale((char *) this_line->ptr);
!
! fprintf(fout, "%*s%s",
! (int) (widths[i % col_count] - strlen(my_cell)), "",
! my_cell);
! free(my_cell);
! }
! else
! fprintf(fout, "%*s%s",
! widths[j] - this_line->width, "",
! this_line->ptr);
! }
! else
! fprintf(fout, "%-s%*s", this_line->ptr,
! finalspaces ? (widths[j] - this_line->width) : 0, "");
! /* If at the right height, done this col */
! if (line_count == heights[j] - 1 || !this_line[1].ptr)
! {
! complete[j] = 1;
! cols_todo--;
! }
! }
!
! /* divider */
! if ((j + 1) % col_count)
! {
! if (opt_border == 0)
! fputc(' ', fout);
! else if (line_count == 0)
! fputs(" | ", fout);
! else
! fprintf(fout, " %c ", complete[j + 1] ? ' ' : ':');
! }
! }
! if (opt_border == 2)
! fputs(" |", fout);
! fputc('\n', fout);
! line_count++;
! }
! }
if (opt->stop_table)
{
if (opt_border == 2 && !cancel_pressed)
! _print_horizontal_line(col_count, widths, opt_border, fout);
/* print footers */
if (footers && !opt_tuples_only && !cancel_pressed)
--- 693,821 ----
fputc('\n', fout);
}
! _print_horizontal_line(col_count, width_wrap, opt_border, fout);
}
}
/* print cells */
for (i = 0, ptr = cells; *ptr; i += col_count, ptr += col_count)
{
! bool line_todo,cols_todo;
! int line_count;
if (cancel_pressed)
break;
+ // Format each cell. Format again, it is a numeric formatting locale (e.g. 123,456 vs. 123456)
for (j = 0; j < col_count; j++)
+ {
pg_wcsformat((unsigned char *) ptr[j], strlen(ptr[j]), encoding, col_lineptrs[j], heights[j]);
+ if (opt_numeric_locale && opt_align[j % col_count] == 'r')
+ {
+ char *my_cell;
+ my_cell = format_numeric_locale((char *)col_lineptrs[j]->ptr);
+ strcpy((char *)col_lineptrs[j]->ptr, my_cell); // Buffer IS large enough... now
+ free(my_cell);
+ }
+ }
! // Print rows to console
memset(complete, 0, col_count * sizeof(int));
! line_count = 0;
! line_todo = true;
! while (line_todo)
! {
! cols_todo = true;
! while (cols_todo)
! {
! char border_cell = '*';
! cols_todo = false;
!
! /* left border */
! if (opt_border == 2)
! fputs("| ", fout);
! else if (opt_border == 1)
! fputc(' ', fout);
!
! /* for each column */
! for (j = 0; j < col_count; j++)
! {
! struct lineptr *this_line = &col_lineptrs[j][line_count];
!
! if( heights[j] <= line_count ) { // Blank column content
! fprintf(fout, "%*s", width_wrap[j], "");
! border_cell = '|';
! }
! else if (opt_align[j] == 'r') // Right aligned cell
! {
! int strlen_remaining = strlen((char *)this_line->ptr+complete[j]);
! if( strlen_remaining > width_wrap[j] )
! {
! fprintf(fout, "%.*s", width_wrap[j], this_line->ptr+complete[j] );
! complete[j] += width_wrap[j]; // We've done THIS much
! cols_todo = true; // And there is more to do...
! border_cell = ':';
! } else
! {
! fprintf(fout, "%*s", width_wrap[j] - strlen_remaining, "");
! fprintf(fout, "%-s", this_line->ptr + complete[j] );
! complete[j] += strlen_remaining;
! border_cell = '|';
! }
! }
! else // Left aligned cell
! {
! int strlen_remaining = strlen((char *)this_line->ptr+complete[j]);
! if( strlen_remaining > width_wrap[j] )
! {
! fprintf(fout, "%.*s", width_wrap[j], this_line->ptr+complete[j] );
! complete[j] += width_wrap[j]; // We've done THIS much
! cols_todo = true; // And there is more to do...
! border_cell = ':';
! } else
! {
! fprintf(fout, "%-s", this_line->ptr + complete[j] );
! fprintf(fout, "%*s", width_wrap[j] - strlen_remaining, "");
! complete[j] += strlen_remaining;
! border_cell = '|';
! }
! }
!
! /* print a divider, middle of columns only */
! if ((j + 1) % col_count)
! {
! if (opt_border == 0)
! fputc(' ', fout);
! else
! fprintf(fout, " %c ", border_cell);
! }
! }
!
! /* end of row border */
! if (opt_border == 2)
! fprintf(fout, " %c", border_cell);
! fputc('\n', fout);
! }
!
! // Check if any columns have line continuations (due to \n in the cell)
! line_count++;
! line_todo = false;
! for (j = 0; j < col_count; j++)
! {
! if( line_count < heights[j]) {
! if( col_lineptrs[j][line_count].ptr ) {
! line_todo = true;
! complete[j]=0;
! }
! }
! }
! }
! }
if (opt->stop_table)
{
if (opt_border == 2 && !cancel_pressed)
! _print_horizontal_line(col_count, width_wrap, opt_border, fout);
/* print footers */
if (footers && !opt_tuples_only && !cancel_pressed)
***************
*** 724,730 ****
}
/* clean up */
! free(widths);
free(heights);
free(col_lineptrs);
free(format_space);
--- 832,841 ----
}
/* clean up */
! free(width_header);
! free(width_average);
! free(width_max);
! free(width_wrap);
free(heights);
free(col_lineptrs);
free(format_space);
***************
*** 1881,1886 ****
--- 1992,1998 ----
opt, output);
break;
case PRINT_ALIGNED:
+ case PRINT_ALIGNEDWRAP:
if (opt->expanded)
print_aligned_vertical(title, headers, cells, footers, align,
opt, output);
Index: print.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/print.h,v
retrieving revision 1.35
diff -c -r1.35 print.h
*** print.h 1 Jan 2008 19:45:56 -0000 1.35
--- print.h 5 Mar 2008 20:57:07 -0000
***************
*** 21,26 ****
--- 21,27 ----
PRINT_NOTHING = 0, /* to make sure someone initializes this */
PRINT_UNALIGNED,
PRINT_ALIGNED,
+ PRINT_ALIGNEDWRAP,
PRINT_HTML,
PRINT_LATEX,
PRINT_TROFF_MS
--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-patches
