Example #1
0
/*
 * Recursively extract node values into the names array, in sorted order with a
 * left-to-right tree traversal.
 * Return the next candidate offset to write into the names array.
 * fields[] must be preallocated to hold tree->count entries
 */
static int
avlCollectFields(avl_tree *tree, avl_node *node, pivot_field *fields, int idx)
{
	if (node == tree->end)
		return idx;

	idx = avlCollectFields(tree, node->children[0], fields, idx);
	fields[idx] = node->field;
	return avlCollectFields(tree, node->children[1], fields, idx + 1);
}
Example #2
0
/*
 * 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;
}
Example #3
0
/*
 * 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;
}