PostgreSQL Source Code  git master
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 103 of file crosstabview.c.

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

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().