/*
* Main entry point to this module.
*
* Process the data from *res according to the options in pset (global),
* to generate the horizontal and vertical headers contents,
* then call printCrosstab() for the actual output.
*/
bool
PrintResultsInCrosstab(const PGresult *res)
{
bool retval = false;
avl_tree piv_columns;
avl_tree piv_rows;
pivot_field *array_columns = NULL;
pivot_field *array_rows = NULL;
int num_columns = 0;
int num_rows = 0;
int field_for_rows;
int field_for_columns;
int field_for_data;
int sort_field_for_columns;
int rn;
avlInit(&piv_rows);
avlInit(&piv_columns);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
psql_error(_("\\crosstabview: query must return results to be shown in crosstab\n"));
goto error_return;
}
if (PQnfields(res) < 3)
{
psql_error(_("\\crosstabview: query must return at least three columns\n"));
goto error_return;
}
/* Process first optional arg (vertical header column) */
if (pset.ctv_args[0] == NULL)
field_for_rows = 0;
else
{
field_for_rows = indexOfColumn(pset.ctv_args[0], res);
if (field_for_rows < 0)
goto error_return;
}
/* Process second optional arg (horizontal header column) */
if (pset.ctv_args[1] == NULL)
field_for_columns = 1;
else
{
field_for_columns = indexOfColumn(pset.ctv_args[1], res);
if (field_for_columns < 0)
goto error_return;
}
/* Insist that header columns be distinct */
if (field_for_columns == field_for_rows)
{
psql_error(_("\\crosstabview: vertical and horizontal headers must be different columns\n"));
goto error_return;
}
/* Process third optional arg (data column) */
if (pset.ctv_args[2] == NULL)
{
int i;
/*
* If the data column was not specified, we search for the one not
* used as either vertical or horizontal headers. Must be exactly
* three columns, or this won't be unique.
*/
if (PQnfields(res) != 3)
{
psql_error(_("\\crosstabview: data column must be specified when query returns more than three columns\n"));
goto error_return;
}
field_for_data = -1;
for (i = 0; i < PQnfields(res); i++)
{
if (i != field_for_rows && i != field_for_columns)
{
field_for_data = i;
break;
}
}
Assert(field_for_data >= 0);
}
else
{
field_for_data = indexOfColumn(pset.ctv_args[2], res);
if (field_for_data < 0)
goto error_return;
}
/* Process fourth optional arg (horizontal header sort column) */
if (pset.ctv_args[3] == NULL)
sort_field_for_columns = -1; /* no sort column */
else
{
sort_field_for_columns = indexOfColumn(pset.ctv_args[3], res);
if (sort_field_for_columns < 0)
goto error_return;
}
/*
* First part: accumulate the names that go into the vertical and
* horizontal headers, each into an AVL binary tree to build the set of
* DISTINCT values.
*/
for (rn = 0; rn < PQntuples(res); rn++)
{
char *val;
char *val1;
/* horizontal */
val = PQgetisnull(res, rn, field_for_columns) ? NULL :
PQgetvalue(res, rn, field_for_columns);
val1 = NULL;
if (sort_field_for_columns >= 0 &&
!PQgetisnull(res, rn, sort_field_for_columns))
val1 = PQgetvalue(res, rn, sort_field_for_columns);
avlMergeValue(&piv_columns, val, val1);
if (piv_columns.count > CROSSTABVIEW_MAX_COLUMNS)
{
psql_error(_("\\crosstabview: maximum number of columns (%d) exceeded\n"),
CROSSTABVIEW_MAX_COLUMNS);
goto error_return;
}
/* vertical */
val = PQgetisnull(res, rn, field_for_rows) ? NULL :
PQgetvalue(res, rn, field_for_rows);
avlMergeValue(&piv_rows, val, NULL);
}
/*
* Second part: Generate sorted arrays from the AVL trees.
*/
num_columns = piv_columns.count;
num_rows = piv_rows.count;
array_columns = (pivot_field *)
pg_malloc(sizeof(pivot_field) * num_columns);
array_rows = (pivot_field *)
pg_malloc(sizeof(pivot_field) * num_rows);
avlCollectFields(&piv_columns, piv_columns.root, array_columns, 0);
avlCollectFields(&piv_rows, piv_rows.root, array_rows, 0);
/*
* Third part: optionally, process the ranking data for the horizontal
* header
*/
if (sort_field_for_columns >= 0)
rankSort(num_columns, array_columns);
/*
* Fourth part: print the crosstab'ed results.
*/
retval = printCrosstab(res,
num_columns, array_columns, field_for_columns,
num_rows, array_rows, field_for_rows,
field_for_data);
error_return:
avlFree(&piv_columns, piv_columns.root);
avlFree(&piv_rows, piv_rows.root);
pg_free(array_columns);
pg_free(array_rows);
return retval;
}
/*
* Main entry point to this module.
*
* Process the data from *res according the display options in pset (global),
* to generate the horizontal and vertical headers contents,
* then call printCrosstab() for the actual output.
*/
bool
PrintResultsInCrosstab(const PGresult *res)
{
char *opt_field_for_rows = pset.ctv_col_V;
char *opt_field_for_columns = pset.ctv_col_H;
char *opt_field_for_data = pset.ctv_col_D;
int rn;
avl_tree piv_columns;
avl_tree piv_rows;
pivot_field *array_columns = NULL;
pivot_field *array_rows = NULL;
int num_columns = 0;
int num_rows = 0;
int *colsV = NULL,
*colsH = NULL,
*colsD = NULL;
int n;
int field_for_columns;
int sort_field_for_columns = -1;
int field_for_rows;
int field_for_data = -1;
bool retval = false;
avlInit(&piv_rows);
avlInit(&piv_columns);
if (res == NULL)
{
psql_error(_("No result\n"));
goto error_return;
}
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
psql_error(_("The query must return results to be shown in crosstab\n"));
goto error_return;
}
if (opt_field_for_rows && !opt_field_for_columns)
{
psql_error(_("A second column must be specified for the horizontal header\n"));
goto error_return;
}
if (PQnfields(res) <= 2)
{
psql_error(_("The query must return at least two columns to be shown in crosstab\n"));
goto error_return;
}
/*
* Arguments processing for the vertical header (1st arg) displayed in the
* left-most column. Only a reference to a field is accepted (no sort
* column).
*/
if (opt_field_for_rows == NULL)
{
field_for_rows = 0;
}
else
{
n = parseColumnRefs(opt_field_for_rows, res, &colsV, 1, ':');
if (n != 1)
goto error_return;
field_for_rows = colsV[0];
}
if (field_for_rows < 0)
goto error_return;
/*----------
* Arguments processing for the horizontal header (2nd arg)
* (pivoted column that gets displayed as the first row).
* Determine:
* - the field number for the horizontal header column
* - the field number of the associated sort column, if any
*/
if (opt_field_for_columns == NULL)
field_for_columns = 1;
else
{
n = parseColumnRefs(opt_field_for_columns, res, &colsH, 2, ':');
if (n <= 0)
goto error_return;
if (n == 1)
field_for_columns = colsH[0];
else
{
field_for_columns = colsH[0];
sort_field_for_columns = colsH[1];
}
if (field_for_columns < 0)
goto error_return;
}
if (field_for_columns == field_for_rows)
{
psql_error(_("The same column cannot be used for both vertical and horizontal headers\n"));
goto error_return;
}
/*
* Arguments processing for the data columns (3rd arg). Determine the
* column to display in the grid.
*/
if (opt_field_for_data == NULL)
{
int i;
/*
* If the data column was not specified, we search for the one not
* used as either vertical or horizontal headers. If the result has
* more than three columns, raise an error.
*/
if (PQnfields(res) > 3)
{
psql_error(_("Data column must be specified when the result set has more than three columns\n"));
goto error_return;
}
for (i = 0; i < PQnfields(res); i++)
{
if (i != field_for_rows && i != field_for_columns)
{
field_for_data = i;
break;
}
}
Assert(field_for_data >= 0);
}
else
{
int num_fields;
/* If a field was given, find out what it is. Only one is allowed. */
num_fields = parseColumnRefs(opt_field_for_data, res, &colsD, 1, ',');
if (num_fields < 1)
goto error_return;
field_for_data = colsD[0];
}
/*
* First part: accumulate the names that go into the vertical and
* horizontal headers, each into an AVL binary tree to build the set of
* DISTINCT values.
*/
for (rn = 0; rn < PQntuples(res); rn++)
{
char *val;
char *val1;
/* horizontal */
val = PQgetisnull(res, rn, field_for_columns) ? NULL :
PQgetvalue(res, rn, field_for_columns);
val1 = NULL;
if (sort_field_for_columns >= 0 &&
!PQgetisnull(res, rn, sort_field_for_columns))
val1 = PQgetvalue(res, rn, sort_field_for_columns);
avlMergeValue(&piv_columns, val, val1);
if (piv_columns.count > CROSSTABVIEW_MAX_COLUMNS)
{
psql_error(_("Maximum number of columns (%d) exceeded\n"),
CROSSTABVIEW_MAX_COLUMNS);
goto error_return;
}
/* vertical */
val = PQgetisnull(res, rn, field_for_rows) ? NULL :
PQgetvalue(res, rn, field_for_rows);
avlMergeValue(&piv_rows, val, NULL);
}
/*
* Second part: Generate sorted arrays from the AVL trees.
*/
num_columns = piv_columns.count;
num_rows = piv_rows.count;
array_columns = (pivot_field *)
pg_malloc(sizeof(pivot_field) * num_columns);
array_rows = (pivot_field *)
pg_malloc(sizeof(pivot_field) * num_rows);
avlCollectFields(&piv_columns, piv_columns.root, array_columns, 0);
avlCollectFields(&piv_rows, piv_rows.root, array_rows, 0);
/*
* Third part: optionally, process the ranking data for the horizontal
* header
*/
if (sort_field_for_columns >= 0)
rankSort(num_columns, array_columns);
/*
* Fourth part: print the crosstab'ed results.
*/
retval = printCrosstab(res,
num_columns, array_columns, field_for_columns,
num_rows, array_rows, field_for_rows,
field_for_data);
error_return:
avlFree(&piv_columns, piv_columns.root);
avlFree(&piv_rows, piv_rows.root);
pg_free(array_columns);
pg_free(array_rows);
pg_free(colsV);
pg_free(colsH);
pg_free(colsD);
return retval;
}