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

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