PostgreSQL Source Code  git master
All Data Structures Namespaces Files Functions Variables Typedefs Enumerations Enumerator Macros Pages
crosstabview.h File Reference
#include "libpq-fe.h"
Include dependency graph for crosstabview.h:
This graph shows which files directly or indirectly include this file:

Go to the source code of this file.

Macros

#define CROSSTABVIEW_MAX_COLUMNS   1600
 

Functions

bool PrintResultInCrosstab (const PGresult *res)
 

Macro Definition Documentation

◆ CROSSTABVIEW_MAX_COLUMNS

#define CROSSTABVIEW_MAX_COLUMNS   1600

Definition at line 24 of file crosstabview.h.

Function Documentation

◆ PrintResultInCrosstab()

bool PrintResultInCrosstab ( const PGresult res)

Definition at line 104 of file crosstabview.c.

105 {
106  bool retval = false;
107  avl_tree piv_columns;
108  avl_tree piv_rows;
109  pivot_field *array_columns = NULL;
110  pivot_field *array_rows = NULL;
111  int num_columns = 0;
112  int num_rows = 0;
113  int field_for_rows;
114  int field_for_columns;
115  int field_for_data;
116  int sort_field_for_columns;
117  int rn;
118 
119  avlInit(&piv_rows);
120  avlInit(&piv_columns);
121 
123  {
124  pg_log_error("\\crosstabview: statement did not return a result set");
125  goto error_return;
126  }
127 
128  if (PQnfields(res) < 3)
129  {
130  pg_log_error("\\crosstabview: query must return at least three columns");
131  goto error_return;
132  }
133 
134  /* Process first optional arg (vertical header column) */
135  if (pset.ctv_args[0] == NULL)
136  field_for_rows = 0;
137  else
138  {
139  field_for_rows = indexOfColumn(pset.ctv_args[0], res);
140  if (field_for_rows < 0)
141  goto error_return;
142  }
143 
144  /* Process second optional arg (horizontal header column) */
145  if (pset.ctv_args[1] == NULL)
146  field_for_columns = 1;
147  else
148  {
149  field_for_columns = indexOfColumn(pset.ctv_args[1], res);
150  if (field_for_columns < 0)
151  goto error_return;
152  }
153 
154  /* Insist that header columns be distinct */
155  if (field_for_columns == field_for_rows)
156  {
157  pg_log_error("\\crosstabview: vertical and horizontal headers must be different columns");
158  goto error_return;
159  }
160 
161  /* Process third optional arg (data column) */
162  if (pset.ctv_args[2] == NULL)
163  {
164  int i;
165 
166  /*
167  * If the data column was not specified, we search for the one not
168  * used as either vertical or horizontal headers. Must be exactly
169  * three columns, or this won't be unique.
170  */
171  if (PQnfields(res) != 3)
172  {
173  pg_log_error("\\crosstabview: data column must be specified when query returns more than three columns");
174  goto error_return;
175  }
176 
177  field_for_data = -1;
178  for (i = 0; i < PQnfields(res); i++)
179  {
180  if (i != field_for_rows && i != field_for_columns)
181  {
182  field_for_data = i;
183  break;
184  }
185  }
186  Assert(field_for_data >= 0);
187  }
188  else
189  {
190  field_for_data = indexOfColumn(pset.ctv_args[2], res);
191  if (field_for_data < 0)
192  goto error_return;
193  }
194 
195  /* Process fourth optional arg (horizontal header sort column) */
196  if (pset.ctv_args[3] == NULL)
197  sort_field_for_columns = -1; /* no sort column */
198  else
199  {
200  sort_field_for_columns = indexOfColumn(pset.ctv_args[3], res);
201  if (sort_field_for_columns < 0)
202  goto error_return;
203  }
204 
205  /*
206  * First part: accumulate the names that go into the vertical and
207  * horizontal headers, each into an AVL binary tree to build the set of
208  * DISTINCT values.
209  */
210 
211  for (rn = 0; rn < PQntuples(res); rn++)
212  {
213  char *val;
214  char *val1;
215 
216  /* horizontal */
217  val = PQgetisnull(res, rn, field_for_columns) ? NULL :
218  PQgetvalue(res, rn, field_for_columns);
219  val1 = NULL;
220 
221  if (sort_field_for_columns >= 0 &&
222  !PQgetisnull(res, rn, sort_field_for_columns))
223  val1 = PQgetvalue(res, rn, sort_field_for_columns);
224 
225  avlMergeValue(&piv_columns, val, val1);
226 
227  if (piv_columns.count > CROSSTABVIEW_MAX_COLUMNS)
228  {
229  pg_log_error("\\crosstabview: maximum number of columns (%d) exceeded",
231  goto error_return;
232  }
233 
234  /* vertical */
235  val = PQgetisnull(res, rn, field_for_rows) ? NULL :
236  PQgetvalue(res, rn, field_for_rows);
237 
238  avlMergeValue(&piv_rows, val, NULL);
239  }
240 
241  /*
242  * Second part: Generate sorted arrays from the AVL trees.
243  */
244 
245  num_columns = piv_columns.count;
246  num_rows = piv_rows.count;
247 
248  array_columns = (pivot_field *)
249  pg_malloc(sizeof(pivot_field) * num_columns);
250 
251  array_rows = (pivot_field *)
252  pg_malloc(sizeof(pivot_field) * num_rows);
253 
254  avlCollectFields(&piv_columns, piv_columns.root, array_columns, 0);
255  avlCollectFields(&piv_rows, piv_rows.root, array_rows, 0);
256 
257  /*
258  * Third part: optionally, process the ranking data for the horizontal
259  * header
260  */
261  if (sort_field_for_columns >= 0)
262  rankSort(num_columns, array_columns);
263 
264  /*
265  * Fourth part: print the crosstab'ed result.
266  */
267  retval = printCrosstab(res,
268  num_columns, array_columns, field_for_columns,
269  num_rows, array_rows, field_for_rows,
270  field_for_data);
271 
272 error_return:
273  avlFree(&piv_columns, piv_columns.root);
274  avlFree(&piv_rows, piv_rows.root);
275  pg_free(array_columns);
276  pg_free(array_rows);
277 
278  return retval;
279 }
#define Assert(condition)
Definition: c.h:863
static int avlCollectFields(avl_tree *tree, avl_node *node, pivot_field *fields, int idx)
Definition: crosstabview.c:577
static void avlFree(avl_tree *tree, avl_node *node)
Definition: crosstabview.c:448
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)
Definition: crosstabview.c:286
static void rankSort(int num_columns, pivot_field *piv_columns)
Definition: crosstabview.c:588
static int indexOfColumn(char *arg, const PGresult *res)
Definition: crosstabview.c:636
static void avlInit(avl_tree *tree)
Definition: crosstabview.c:438
static void avlMergeValue(avl_tree *tree, char *name, char *sort_value)
Definition: crosstabview.c:560
#define CROSSTABVIEW_MAX_COLUMNS
Definition: crosstabview.h:24
ExecStatusType PQresultStatus(const PGresult *res)
Definition: fe-exec.c:3411
int PQntuples(const PGresult *res)
Definition: fe-exec.c:3481
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3876
int PQgetisnull(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3901
int PQnfields(const PGresult *res)
Definition: fe-exec.c:3489
void pg_free(void *ptr)
Definition: fe_memutils.c:105
void * pg_malloc(size_t size)
Definition: fe_memutils.c:47
long val
Definition: informix.c:689
int i
Definition: isn.c:72
@ PGRES_TUPLES_OK
Definition: libpq-fe.h:123
#define pg_log_error(...)
Definition: logging.h:106
PsqlSettings pset
Definition: startup.c:32
avl_node * root
Definition: crosstabview.c:76
char * ctv_args[4]
Definition: settings.h:115

References Assert, avlCollectFields(), avlFree(), avlInit(), avlMergeValue(), _avl_tree::count, CROSSTABVIEW_MAX_COLUMNS, _psqlSettings::ctv_args, i, indexOfColumn(), pg_free(), pg_log_error, pg_malloc(), PGRES_TUPLES_OK, PQgetisnull(), PQgetvalue(), PQnfields(), PQntuples(), PQresultStatus(), printCrosstab(), pset, rankSort(), res, _avl_tree::root, and val.

Referenced by PrintQueryResult().