PostgreSQL Source Code  git master
 All Data Structures Namespaces Files Functions Variables Typedefs Enumerations Enumerator Macros
crosstabview.h File Reference
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 PrintResultsInCrosstab (const PGresult *res)
 

Macro Definition Documentation

#define CROSSTABVIEW_MAX_COLUMNS   1600

Definition at line 22 of file crosstabview.h.

Referenced by PrintResultsInCrosstab().

Function Documentation

bool PrintResultsInCrosstab ( const PGresult res)

Definition at line 103 of file crosstabview.c.

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

Referenced by PrintQueryResults().

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 
121  if (PQresultStatus(res) != PGRES_TUPLES_OK)
122  {
123  psql_error("\\crosstabview: statement did not return a result set\n");
124  goto error_return;
125  }
126 
127  if (PQnfields(res) < 3)
128  {
129  psql_error("\\crosstabview: query must return at least three columns\n");
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  psql_error("\\crosstabview: vertical and horizontal headers must be different columns\n");
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  psql_error("\\crosstabview: data column must be specified when query returns more than three columns\n");
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  psql_error("\\crosstabview: maximum number of columns (%d) exceeded\n",
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 results.
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 }
int PQnfields(const PGresult *res)
Definition: fe-exec.c:2681
static void avlFree(avl_tree *tree, avl_node *node)
Definition: crosstabview.c:447
PsqlSettings pset
Definition: startup.c:33
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3067
void * pg_malloc(size_t size)
Definition: fe_memutils.c:47
int PQntuples(const PGresult *res)
Definition: fe-exec.c:2673
static void avlInit(avl_tree *tree)
Definition: crosstabview.c:437
ExecStatusType PQresultStatus(const PGresult *res)
Definition: fe-exec.c:2596
char * ctv_args[4]
Definition: settings.h:98
static int indexOfColumn(char *arg, const PGresult *res)
Definition: crosstabview.c:635
#define CROSSTABVIEW_MAX_COLUMNS
Definition: crosstabview.h:22
static void rankSort(int num_columns, pivot_field *piv_columns)
Definition: crosstabview.c:587
static bool printCrosstab(const PGresult *results, 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
void psql_error(const char *fmt,...)
Definition: common.c:220
avl_node * root
Definition: crosstabview.c:75
static int avlCollectFields(avl_tree *tree, avl_node *node, pivot_field *fields, int idx)
Definition: crosstabview.c:576
static void avlMergeValue(avl_tree *tree, char *name, char *sort_value)
Definition: crosstabview.c:559
#define NULL
Definition: c.h:229
#define Assert(condition)
Definition: c.h:675
void pg_free(void *ptr)
Definition: fe_memutils.c:105
int i
int PQgetisnull(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3092
long val
Definition: informix.c:689