Line data Source code
1 : /*
2 : * contrib/tablefunc/tablefunc.c
3 : *
4 : *
5 : * tablefunc
6 : *
7 : * Sample to demonstrate C functions which return setof scalar
8 : * and setof composite.
9 : * Joe Conway <mail@joeconway.com>
10 : * And contributors:
11 : * Nabil Sayegh <postgresql@e-trolley.de>
12 : *
13 : * Copyright (c) 2002-2026, PostgreSQL Global Development Group
14 : *
15 : * Permission to use, copy, modify, and distribute this software and its
16 : * documentation for any purpose, without fee, and without a written agreement
17 : * is hereby granted, provided that the above copyright notice and this
18 : * paragraph and the following two paragraphs appear in all copies.
19 : *
20 : * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
21 : * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
22 : * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
23 : * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
24 : * POSSIBILITY OF SUCH DAMAGE.
25 : *
26 : * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
27 : * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
28 : * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
29 : * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
30 : * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
31 : *
32 : */
33 : #include "postgres.h"
34 :
35 : #include <math.h>
36 :
37 : #include "access/htup_details.h"
38 : #include "catalog/pg_type.h"
39 : #include "common/pg_prng.h"
40 : #include "executor/spi.h"
41 : #include "fmgr.h"
42 : #include "funcapi.h"
43 : #include "lib/stringinfo.h"
44 : #include "miscadmin.h"
45 : #include "utils/builtins.h"
46 : #include "utils/tuplestore.h"
47 :
48 1 : PG_MODULE_MAGIC_EXT(
49 : .name = "tablefunc",
50 : .version = PG_VERSION
51 : );
52 :
53 : static HTAB *load_categories_hash(char *cats_sql, MemoryContext per_query_ctx);
54 : static Tuplestorestate *get_crosstab_tuplestore(char *sql,
55 : HTAB *crosstab_hash,
56 : TupleDesc tupdesc,
57 : bool randomAccess);
58 : static void validateConnectbyTupleDesc(TupleDesc td, bool show_branch, bool show_serial);
59 : static void compatCrosstabTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc);
60 : static void compatConnectbyTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc);
61 : static void get_normal_pair(float8 *x1, float8 *x2);
62 : static Tuplestorestate *connectby(char *relname,
63 : char *key_fld,
64 : char *parent_key_fld,
65 : char *orderby_fld,
66 : char *branch_delim,
67 : char *start_with,
68 : int max_depth,
69 : bool show_branch,
70 : bool show_serial,
71 : MemoryContext per_query_ctx,
72 : bool randomAccess,
73 : AttInMetadata *attinmeta);
74 : static void build_tuplestore_recursively(char *key_fld,
75 : char *parent_key_fld,
76 : char *relname,
77 : char *orderby_fld,
78 : char *branch_delim,
79 : char *start_with,
80 : char *branch,
81 : int level,
82 : int *serial,
83 : int max_depth,
84 : bool show_branch,
85 : bool show_serial,
86 : MemoryContext per_query_ctx,
87 : AttInMetadata *attinmeta,
88 : Tuplestorestate *tupstore);
89 :
90 : typedef struct
91 : {
92 : float8 mean; /* mean of the distribution */
93 : float8 stddev; /* stddev of the distribution */
94 : float8 carry_val; /* hold second generated value */
95 : bool use_carry; /* use second generated value */
96 : } normal_rand_fctx;
97 :
98 : #define xpfree(var_) \
99 : do { \
100 : if (var_ != NULL) \
101 : { \
102 : pfree(var_); \
103 : var_ = NULL; \
104 : } \
105 : } while (0)
106 :
107 : #define xpstrdup(tgtvar_, srcvar_) \
108 : do { \
109 : if (srcvar_) \
110 : tgtvar_ = pstrdup(srcvar_); \
111 : else \
112 : tgtvar_ = NULL; \
113 : } while (0)
114 :
115 : #define xstreq(tgtvar_, srcvar_) \
116 : (((tgtvar_ == NULL) && (srcvar_ == NULL)) || \
117 : ((tgtvar_ != NULL) && (srcvar_ != NULL) && (strcmp(tgtvar_, srcvar_) == 0)))
118 :
119 : /* sign, 10 digits, '\0' */
120 : #define INT32_STRLEN 12
121 :
122 : /* stored info for a crosstab category */
123 : typedef struct crosstab_cat_desc
124 : {
125 : char *catname; /* full category name */
126 : uint64 attidx; /* zero based */
127 : } crosstab_cat_desc;
128 :
129 : #define MAX_CATNAME_LEN NAMEDATALEN
130 : #define INIT_CATS 64
131 :
132 : #define crosstab_HashTableLookup(HASHTAB, CATNAME, CATDESC) \
133 : do { \
134 : crosstab_HashEnt *hentry; char key[MAX_CATNAME_LEN]; \
135 : \
136 : MemSet(key, 0, MAX_CATNAME_LEN); \
137 : snprintf(key, MAX_CATNAME_LEN - 1, "%s", CATNAME); \
138 : hentry = (crosstab_HashEnt*) hash_search(HASHTAB, \
139 : key, HASH_FIND, NULL); \
140 : if (hentry) \
141 : CATDESC = hentry->catdesc; \
142 : else \
143 : CATDESC = NULL; \
144 : } while(0)
145 :
146 : #define crosstab_HashTableInsert(HASHTAB, CATDESC) \
147 : do { \
148 : crosstab_HashEnt *hentry; bool found; char key[MAX_CATNAME_LEN]; \
149 : \
150 : MemSet(key, 0, MAX_CATNAME_LEN); \
151 : snprintf(key, MAX_CATNAME_LEN - 1, "%s", CATDESC->catname); \
152 : hentry = (crosstab_HashEnt*) hash_search(HASHTAB, \
153 : key, HASH_ENTER, &found); \
154 : if (found) \
155 : ereport(ERROR, \
156 : (errcode(ERRCODE_DUPLICATE_OBJECT), \
157 : errmsg("duplicate category name"))); \
158 : hentry->catdesc = CATDESC; \
159 : } while(0)
160 :
161 : /* hash table */
162 : typedef struct crosstab_hashent
163 : {
164 : char internal_catname[MAX_CATNAME_LEN];
165 : crosstab_cat_desc *catdesc;
166 : } crosstab_HashEnt;
167 :
168 : /*
169 : * normal_rand - return requested number of random values
170 : * with a Gaussian (Normal) distribution.
171 : *
172 : * inputs are int numvals, float8 mean, and float8 stddev
173 : * returns setof float8
174 : */
175 2 : PG_FUNCTION_INFO_V1(normal_rand);
176 : Datum
177 102 : normal_rand(PG_FUNCTION_ARGS)
178 : {
179 : FuncCallContext *funcctx;
180 : uint64 call_cntr;
181 : uint64 max_calls;
182 : normal_rand_fctx *fctx;
183 : float8 mean;
184 : float8 stddev;
185 : float8 carry_val;
186 : bool use_carry;
187 : MemoryContext oldcontext;
188 :
189 : /* stuff done only on the first call of the function */
190 102 : if (SRF_IS_FIRSTCALL())
191 : {
192 : int32 num_tuples;
193 :
194 : /* create a function context for cross-call persistence */
195 2 : funcctx = SRF_FIRSTCALL_INIT();
196 :
197 : /*
198 : * switch to memory context appropriate for multiple function calls
199 : */
200 2 : oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
201 :
202 : /* total number of tuples to be returned */
203 2 : num_tuples = PG_GETARG_INT32(0);
204 2 : if (num_tuples < 0)
205 1 : ereport(ERROR,
206 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
207 : errmsg("number of rows cannot be negative")));
208 1 : funcctx->max_calls = num_tuples;
209 :
210 : /* allocate memory for user context */
211 1 : fctx = palloc_object(normal_rand_fctx);
212 :
213 : /*
214 : * Use fctx to keep track of upper and lower bounds from call to call.
215 : * It will also be used to carry over the spare value we get from the
216 : * Box-Muller algorithm so that we only actually calculate a new value
217 : * every other call.
218 : */
219 1 : fctx->mean = PG_GETARG_FLOAT8(1);
220 1 : fctx->stddev = PG_GETARG_FLOAT8(2);
221 1 : fctx->carry_val = 0;
222 1 : fctx->use_carry = false;
223 :
224 1 : funcctx->user_fctx = fctx;
225 :
226 1 : MemoryContextSwitchTo(oldcontext);
227 : }
228 :
229 : /* stuff done on every call of the function */
230 101 : funcctx = SRF_PERCALL_SETUP();
231 :
232 101 : call_cntr = funcctx->call_cntr;
233 101 : max_calls = funcctx->max_calls;
234 101 : fctx = funcctx->user_fctx;
235 101 : mean = fctx->mean;
236 101 : stddev = fctx->stddev;
237 101 : carry_val = fctx->carry_val;
238 101 : use_carry = fctx->use_carry;
239 :
240 101 : if (call_cntr < max_calls) /* do when there is more left to send */
241 : {
242 : float8 result;
243 :
244 100 : if (use_carry)
245 : {
246 : /*
247 : * reset use_carry and use second value obtained on last pass
248 : */
249 50 : fctx->use_carry = false;
250 50 : result = carry_val;
251 : }
252 : else
253 : {
254 : float8 normval_1;
255 : float8 normval_2;
256 :
257 : /* Get the next two normal values */
258 50 : get_normal_pair(&normval_1, &normval_2);
259 :
260 : /* use the first */
261 50 : result = mean + (stddev * normval_1);
262 :
263 : /* and save the second */
264 50 : fctx->carry_val = mean + (stddev * normval_2);
265 50 : fctx->use_carry = true;
266 : }
267 :
268 : /* send the result */
269 100 : SRF_RETURN_NEXT(funcctx, Float8GetDatum(result));
270 : }
271 : else
272 : /* do when there is no more left */
273 1 : SRF_RETURN_DONE(funcctx);
274 : }
275 :
276 : /*
277 : * get_normal_pair()
278 : * Assigns normally distributed (Gaussian) values to a pair of provided
279 : * parameters, with mean 0, standard deviation 1.
280 : *
281 : * This routine implements Algorithm P (Polar method for normal deviates)
282 : * from Knuth's _The_Art_of_Computer_Programming_, Volume 2, 3rd ed., pages
283 : * 122-126. Knuth cites his source as "The polar method", G. E. P. Box, M. E.
284 : * Muller, and G. Marsaglia, _Annals_Math,_Stat._ 29 (1958), 610-611.
285 : *
286 : */
287 : static void
288 50 : get_normal_pair(float8 *x1, float8 *x2)
289 : {
290 : float8 u1,
291 : u2,
292 : v1,
293 : v2,
294 : s;
295 :
296 : do
297 : {
298 68 : u1 = pg_prng_double(&pg_global_prng_state);
299 68 : u2 = pg_prng_double(&pg_global_prng_state);
300 :
301 68 : v1 = (2.0 * u1) - 1.0;
302 68 : v2 = (2.0 * u2) - 1.0;
303 :
304 68 : s = v1 * v1 + v2 * v2;
305 68 : } while (s >= 1.0);
306 :
307 50 : if (s == 0)
308 : {
309 0 : *x1 = 0;
310 0 : *x2 = 0;
311 : }
312 : else
313 : {
314 50 : s = sqrt((-2.0 * log(s)) / s);
315 50 : *x1 = v1 * s;
316 50 : *x2 = v2 * s;
317 : }
318 50 : }
319 :
320 : /*
321 : * crosstab - create a crosstab of rowids and values columns from a
322 : * SQL statement returning one rowid column, one category column,
323 : * and one value column.
324 : *
325 : * e.g. given sql which produces:
326 : *
327 : * rowid cat value
328 : * ------+-------+-------
329 : * row1 cat1 val1
330 : * row1 cat2 val2
331 : * row1 cat3 val3
332 : * row1 cat4 val4
333 : * row2 cat1 val5
334 : * row2 cat2 val6
335 : * row2 cat3 val7
336 : * row2 cat4 val8
337 : *
338 : * crosstab returns:
339 : * <===== values columns =====>
340 : * rowid cat1 cat2 cat3 cat4
341 : * ------+-------+-------+-------+-------
342 : * row1 val1 val2 val3 val4
343 : * row2 val5 val6 val7 val8
344 : *
345 : * NOTES:
346 : * 1. SQL result must be ordered by 1,2.
347 : * 2. The number of values columns depends on the tuple description
348 : * of the function's declared return type. The return type's columns
349 : * must match the datatypes of the SQL query's result. The datatype
350 : * of the category column can be anything, however.
351 : * 3. Missing values (i.e. not enough adjacent rows of same rowid to
352 : * fill the number of result values columns) are filled in with nulls.
353 : * 4. Extra values (i.e. too many adjacent rows of same rowid to fill
354 : * the number of result values columns) are skipped.
355 : * 5. Rows with all nulls in the values columns are skipped.
356 : */
357 11 : PG_FUNCTION_INFO_V1(crosstab);
358 : Datum
359 20 : crosstab(PG_FUNCTION_ARGS)
360 : {
361 20 : char *sql = text_to_cstring(PG_GETARG_TEXT_PP(0));
362 20 : ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
363 : Tuplestorestate *tupstore;
364 : TupleDesc tupdesc;
365 : uint64 call_cntr;
366 : uint64 max_calls;
367 : AttInMetadata *attinmeta;
368 : SPITupleTable *spi_tuptable;
369 : TupleDesc spi_tupdesc;
370 : bool firstpass;
371 : char *lastrowid;
372 : int i;
373 : int num_categories;
374 : MemoryContext per_query_ctx;
375 : MemoryContext oldcontext;
376 : int ret;
377 : uint64 proc;
378 :
379 : /* check to see if caller supports us returning a tuplestore */
380 20 : if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
381 0 : ereport(ERROR,
382 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
383 : errmsg("set-valued function called in context that cannot accept a set")));
384 20 : if (!(rsinfo->allowedModes & SFRM_Materialize))
385 0 : ereport(ERROR,
386 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
387 : errmsg("materialize mode required, but it is not allowed in this context")));
388 :
389 20 : per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
390 :
391 : /* Connect to SPI manager */
392 20 : SPI_connect();
393 :
394 : /* Retrieve the desired rows */
395 20 : ret = SPI_execute(sql, true, 0);
396 20 : proc = SPI_processed;
397 :
398 : /* If no qualifying tuples, fall out early */
399 20 : if (ret != SPI_OK_SELECT || proc == 0)
400 : {
401 0 : SPI_finish();
402 0 : rsinfo->isDone = ExprEndResult;
403 0 : PG_RETURN_NULL();
404 : }
405 :
406 20 : spi_tuptable = SPI_tuptable;
407 20 : spi_tupdesc = spi_tuptable->tupdesc;
408 :
409 : /*----------
410 : * The provided SQL query must always return three columns.
411 : *
412 : * 1. rowname
413 : * the label or identifier for each row in the final result
414 : * 2. category
415 : * the label or identifier for each column in the final result
416 : * 3. values
417 : * the value for each column in the final result
418 : *----------
419 : */
420 20 : if (spi_tupdesc->natts != 3)
421 1 : ereport(ERROR,
422 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
423 : errmsg("invalid crosstab source data query"),
424 : errdetail("The query must return 3 columns: row_name, category, and value.")));
425 :
426 : /* get a tuple descriptor for our result type */
427 19 : switch (get_call_result_type(fcinfo, NULL, &tupdesc))
428 : {
429 19 : case TYPEFUNC_COMPOSITE:
430 : /* success */
431 19 : break;
432 0 : case TYPEFUNC_RECORD:
433 : /* failed to determine actual type of RECORD */
434 0 : ereport(ERROR,
435 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
436 : errmsg("function returning record called in context "
437 : "that cannot accept type record")));
438 : break;
439 0 : default:
440 : /* result type isn't composite */
441 0 : ereport(ERROR,
442 : (errcode(ERRCODE_DATATYPE_MISMATCH),
443 : errmsg("return type must be a row type")));
444 : break;
445 : }
446 :
447 : /*
448 : * Check that return tupdesc is compatible with the data we got from SPI,
449 : * at least based on number and type of attributes
450 : */
451 19 : compatCrosstabTupleDescs(tupdesc, spi_tupdesc);
452 :
453 : /*
454 : * switch to long-lived memory context
455 : */
456 16 : oldcontext = MemoryContextSwitchTo(per_query_ctx);
457 :
458 : /* make sure we have a persistent copy of the result tupdesc */
459 16 : tupdesc = CreateTupleDescCopy(tupdesc);
460 :
461 : /* initialize our tuplestore in long-lived context */
462 : tupstore =
463 16 : tuplestore_begin_heap(rsinfo->allowedModes & SFRM_Materialize_Random,
464 : false, work_mem);
465 :
466 16 : MemoryContextSwitchTo(oldcontext);
467 :
468 : /*
469 : * Generate attribute metadata needed later to produce tuples from raw C
470 : * strings
471 : */
472 16 : attinmeta = TupleDescGetAttInMetadata(tupdesc);
473 :
474 : /* total number of tuples to be examined */
475 16 : max_calls = proc;
476 :
477 : /* the return tuple always must have 1 rowid + num_categories columns */
478 16 : num_categories = tupdesc->natts - 1;
479 :
480 16 : firstpass = true;
481 16 : lastrowid = NULL;
482 :
483 81 : for (call_cntr = 0; call_cntr < max_calls; call_cntr++)
484 : {
485 65 : bool skip_tuple = false;
486 : char **values;
487 :
488 : /* allocate and zero space */
489 65 : values = (char **) palloc0((1 + num_categories) * sizeof(char *));
490 :
491 : /*
492 : * now loop through the sql results and assign each value in sequence
493 : * to the next category
494 : */
495 174 : for (i = 0; i < num_categories; i++)
496 : {
497 : HeapTuple spi_tuple;
498 : char *rowid;
499 :
500 : /* see if we've gone too far already */
501 144 : if (call_cntr >= max_calls)
502 5 : break;
503 :
504 : /* get the next sql result tuple */
505 139 : spi_tuple = spi_tuptable->vals[call_cntr];
506 :
507 : /* get the rowid from the current sql result tuple */
508 139 : rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
509 :
510 : /*
511 : * If this is the first pass through the values for this rowid,
512 : * set the first column to rowid
513 : */
514 139 : if (i == 0)
515 : {
516 65 : xpstrdup(values[0], rowid);
517 :
518 : /*
519 : * Check to see if the rowid is the same as that of the last
520 : * tuple sent -- if so, skip this tuple entirely
521 : */
522 65 : if (!firstpass && xstreq(lastrowid, rowid))
523 : {
524 23 : xpfree(rowid);
525 23 : skip_tuple = true;
526 23 : break;
527 : }
528 : }
529 :
530 : /*
531 : * If rowid hasn't changed on us, continue building the output
532 : * tuple.
533 : */
534 116 : if (xstreq(rowid, values[0]))
535 : {
536 : /*
537 : * Get the next category item value, which is always attribute
538 : * number three.
539 : *
540 : * Be careful to assign the value to the array index based on
541 : * which category we are presently processing.
542 : */
543 109 : values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3);
544 :
545 : /*
546 : * increment the counter since we consume a row for each
547 : * category, but not for last pass because the outer loop will
548 : * do that for us
549 : */
550 109 : if (i < (num_categories - 1))
551 79 : call_cntr++;
552 109 : xpfree(rowid);
553 : }
554 : else
555 : {
556 : /*
557 : * We'll fill in NULLs for the missing values, but we need to
558 : * decrement the counter since this sql result row doesn't
559 : * belong to the current output tuple.
560 : */
561 7 : call_cntr--;
562 7 : xpfree(rowid);
563 7 : break;
564 : }
565 : }
566 :
567 65 : if (!skip_tuple)
568 : {
569 : HeapTuple tuple;
570 :
571 : /* build the tuple and store it */
572 42 : tuple = BuildTupleFromCStrings(attinmeta, values);
573 42 : tuplestore_puttuple(tupstore, tuple);
574 42 : heap_freetuple(tuple);
575 : }
576 :
577 : /* Remember current rowid */
578 65 : xpfree(lastrowid);
579 65 : xpstrdup(lastrowid, values[0]);
580 65 : firstpass = false;
581 :
582 : /* Clean up */
583 311 : for (i = 0; i < num_categories + 1; i++)
584 246 : if (values[i] != NULL)
585 157 : pfree(values[i]);
586 65 : pfree(values);
587 : }
588 :
589 : /* let the caller know we're sending back a tuplestore */
590 16 : rsinfo->returnMode = SFRM_Materialize;
591 16 : rsinfo->setResult = tupstore;
592 16 : rsinfo->setDesc = tupdesc;
593 :
594 : /* release SPI related resources (and return to caller's context) */
595 16 : SPI_finish();
596 :
597 16 : return (Datum) 0;
598 : }
599 :
600 : /*
601 : * crosstab_hash - reimplement crosstab as materialized function and
602 : * properly deal with missing values (i.e. don't pack remaining
603 : * values to the left)
604 : *
605 : * crosstab - create a crosstab of rowids and values columns from a
606 : * SQL statement returning one rowid column, one category column,
607 : * and one value column.
608 : *
609 : * e.g. given sql which produces:
610 : *
611 : * rowid cat value
612 : * ------+-------+-------
613 : * row1 cat1 val1
614 : * row1 cat2 val2
615 : * row1 cat4 val4
616 : * row2 cat1 val5
617 : * row2 cat2 val6
618 : * row2 cat3 val7
619 : * row2 cat4 val8
620 : *
621 : * crosstab returns:
622 : * <===== values columns =====>
623 : * rowid cat1 cat2 cat3 cat4
624 : * ------+-------+-------+-------+-------
625 : * row1 val1 val2 null val4
626 : * row2 val5 val6 val7 val8
627 : *
628 : * NOTES:
629 : * 1. SQL result must be ordered by 1.
630 : * 2. The number of values columns depends on the tuple description
631 : * of the function's declared return type.
632 : * 3. Missing values (i.e. missing category) are filled in with nulls.
633 : * 4. Extra values (i.e. not in category results) are skipped.
634 : */
635 6 : PG_FUNCTION_INFO_V1(crosstab_hash);
636 : Datum
637 14 : crosstab_hash(PG_FUNCTION_ARGS)
638 : {
639 14 : char *sql = text_to_cstring(PG_GETARG_TEXT_PP(0));
640 14 : char *cats_sql = text_to_cstring(PG_GETARG_TEXT_PP(1));
641 14 : ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
642 : TupleDesc tupdesc;
643 : MemoryContext per_query_ctx;
644 : MemoryContext oldcontext;
645 : HTAB *crosstab_hash;
646 :
647 : /* check to see if caller supports us returning a tuplestore */
648 14 : if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
649 0 : ereport(ERROR,
650 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
651 : errmsg("set-valued function called in context that cannot accept a set")));
652 14 : if (!(rsinfo->allowedModes & SFRM_Materialize) ||
653 14 : rsinfo->expectedDesc == NULL)
654 0 : ereport(ERROR,
655 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
656 : errmsg("materialize mode required, but it is not allowed in this context")));
657 :
658 14 : per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
659 14 : oldcontext = MemoryContextSwitchTo(per_query_ctx);
660 :
661 : /* get the requested return tuple description */
662 14 : tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
663 :
664 : /*
665 : * Check to make sure we have a reasonable tuple descriptor
666 : *
667 : * Note we will attempt to coerce the values into whatever the return
668 : * attribute type is and depend on the "in" function to complain if
669 : * needed.
670 : */
671 14 : if (tupdesc->natts < 2)
672 1 : ereport(ERROR,
673 : (errcode(ERRCODE_DATATYPE_MISMATCH),
674 : errmsg("invalid crosstab return type"),
675 : errdetail("Return row must have at least two columns.")));
676 :
677 : /* load up the categories hash table */
678 13 : crosstab_hash = load_categories_hash(cats_sql, per_query_ctx);
679 :
680 : /* let the caller know we're sending back a tuplestore */
681 11 : rsinfo->returnMode = SFRM_Materialize;
682 :
683 : /* now go build it */
684 19 : rsinfo->setResult = get_crosstab_tuplestore(sql,
685 : crosstab_hash,
686 : tupdesc,
687 11 : rsinfo->allowedModes & SFRM_Materialize_Random);
688 :
689 : /*
690 : * SFRM_Materialize mode expects us to return a NULL Datum. The actual
691 : * tuples are in our tuplestore and passed back through rsinfo->setResult.
692 : * rsinfo->setDesc is set to the tuple description that we actually used
693 : * to build our tuples with, so the caller can verify we did what it was
694 : * expecting.
695 : */
696 8 : rsinfo->setDesc = tupdesc;
697 8 : MemoryContextSwitchTo(oldcontext);
698 :
699 8 : return (Datum) 0;
700 : }
701 :
702 : /*
703 : * load up the categories hash table
704 : */
705 : static HTAB *
706 13 : load_categories_hash(char *cats_sql, MemoryContext per_query_ctx)
707 : {
708 : HTAB *crosstab_hash;
709 : HASHCTL ctl;
710 : int ret;
711 : uint64 proc;
712 : MemoryContext SPIcontext;
713 :
714 : /* initialize the category hash table */
715 13 : ctl.keysize = MAX_CATNAME_LEN;
716 13 : ctl.entrysize = sizeof(crosstab_HashEnt);
717 13 : ctl.hcxt = per_query_ctx;
718 :
719 : /*
720 : * use INIT_CATS, defined above as a guess of how many hash table entries
721 : * to create, initially
722 : */
723 13 : crosstab_hash = hash_create("crosstab hash",
724 : INIT_CATS,
725 : &ctl,
726 : HASH_ELEM | HASH_STRINGS | HASH_CONTEXT);
727 :
728 : /* Connect to SPI manager */
729 13 : SPI_connect();
730 :
731 : /* Retrieve the category name rows */
732 13 : ret = SPI_execute(cats_sql, true, 0);
733 13 : proc = SPI_processed;
734 :
735 : /* Check for qualifying tuples */
736 13 : if ((ret == SPI_OK_SELECT) && (proc > 0))
737 : {
738 11 : SPITupleTable *spi_tuptable = SPI_tuptable;
739 11 : TupleDesc spi_tupdesc = spi_tuptable->tupdesc;
740 : uint64 i;
741 :
742 : /*
743 : * The provided categories SQL query must always return one column:
744 : * category - the label or identifier for each column
745 : */
746 11 : if (spi_tupdesc->natts != 1)
747 1 : ereport(ERROR,
748 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
749 : errmsg("invalid crosstab categories query"),
750 : errdetail("The query must return one column.")));
751 :
752 45 : for (i = 0; i < proc; i++)
753 : {
754 : crosstab_cat_desc *catdesc;
755 : char *catname;
756 : HeapTuple spi_tuple;
757 :
758 : /* get the next sql result tuple */
759 36 : spi_tuple = spi_tuptable->vals[i];
760 :
761 : /* get the category from the current sql result tuple */
762 36 : catname = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
763 36 : if (catname == NULL)
764 1 : ereport(ERROR,
765 : (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
766 : errmsg("crosstab category value must not be null")));
767 :
768 35 : SPIcontext = MemoryContextSwitchTo(per_query_ctx);
769 :
770 35 : catdesc = palloc_object(crosstab_cat_desc);
771 35 : catdesc->catname = catname;
772 35 : catdesc->attidx = i;
773 :
774 : /* Add the proc description block to the hashtable */
775 315 : crosstab_HashTableInsert(crosstab_hash, catdesc);
776 :
777 35 : MemoryContextSwitchTo(SPIcontext);
778 : }
779 : }
780 :
781 11 : if (SPI_finish() != SPI_OK_FINISH)
782 : /* internal error */
783 0 : elog(ERROR, "load_categories_hash: SPI_finish() failed");
784 :
785 11 : return crosstab_hash;
786 : }
787 :
788 : /*
789 : * create and populate the crosstab tuplestore using the provided source query
790 : */
791 : static Tuplestorestate *
792 11 : get_crosstab_tuplestore(char *sql,
793 : HTAB *crosstab_hash,
794 : TupleDesc tupdesc,
795 : bool randomAccess)
796 : {
797 : Tuplestorestate *tupstore;
798 11 : int num_categories = hash_get_num_entries(crosstab_hash);
799 11 : AttInMetadata *attinmeta = TupleDescGetAttInMetadata(tupdesc);
800 : char **values;
801 : HeapTuple tuple;
802 : int ret;
803 : uint64 proc;
804 :
805 : /* initialize our tuplestore (while still in query context!) */
806 11 : tupstore = tuplestore_begin_heap(randomAccess, false, work_mem);
807 :
808 : /* Connect to SPI manager */
809 11 : SPI_connect();
810 :
811 : /* Now retrieve the crosstab source rows */
812 11 : ret = SPI_execute(sql, true, 0);
813 11 : proc = SPI_processed;
814 :
815 : /* Check for qualifying tuples */
816 11 : if ((ret == SPI_OK_SELECT) && (proc > 0))
817 : {
818 9 : SPITupleTable *spi_tuptable = SPI_tuptable;
819 9 : TupleDesc spi_tupdesc = spi_tuptable->tupdesc;
820 9 : int ncols = spi_tupdesc->natts;
821 : char *rowid;
822 9 : char *lastrowid = NULL;
823 9 : bool firstpass = true;
824 : uint64 i;
825 : int j;
826 : int result_ncols;
827 :
828 9 : if (num_categories == 0)
829 : {
830 : /* no qualifying category tuples */
831 1 : ereport(ERROR,
832 : (errcode(ERRCODE_CARDINALITY_VIOLATION),
833 : errmsg("crosstab categories query must return at least one row")));
834 : }
835 :
836 : /*
837 : * The provided SQL query must always return at least three columns:
838 : *
839 : * 1. rowname the label for each row - column 1 in the final result
840 : * 2. category the label for each value-column in the final result 3.
841 : * value the values used to populate the value-columns
842 : *
843 : * If there are more than three columns, the last two are taken as
844 : * "category" and "values". The first column is taken as "rowname".
845 : * Additional columns (2 thru N-2) are assumed the same for the same
846 : * "rowname", and are copied into the result tuple from the first time
847 : * we encounter a particular rowname.
848 : */
849 8 : if (ncols < 3)
850 1 : ereport(ERROR,
851 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
852 : errmsg("invalid crosstab source data query"),
853 : errdetail("The query must return at least 3 columns: row_name, category, and value.")));
854 :
855 7 : result_ncols = (ncols - 2) + num_categories;
856 :
857 : /* Recheck to make sure output tuple descriptor looks reasonable */
858 7 : if (tupdesc->natts != result_ncols)
859 1 : ereport(ERROR,
860 : (errcode(ERRCODE_DATATYPE_MISMATCH),
861 : errmsg("invalid crosstab return type"),
862 : errdetail("Return row must have %d columns, not %d.",
863 : result_ncols, tupdesc->natts)));
864 :
865 : /* allocate space and make sure it's clear */
866 6 : values = (char **) palloc0(result_ncols * sizeof(char *));
867 :
868 72 : for (i = 0; i < proc; i++)
869 : {
870 : HeapTuple spi_tuple;
871 : crosstab_cat_desc *catdesc;
872 : char *catname;
873 :
874 : /* get the next sql result tuple */
875 66 : spi_tuple = spi_tuptable->vals[i];
876 :
877 : /* get the rowid from the current sql result tuple */
878 66 : rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
879 :
880 : /*
881 : * if we're on a new output row, grab the column values up to
882 : * column N-2 now
883 : */
884 66 : if (firstpass || !xstreq(lastrowid, rowid))
885 : {
886 : /*
887 : * a new row means we need to flush the old one first, unless
888 : * we're on the very first row
889 : */
890 18 : if (!firstpass)
891 : {
892 : /* rowid changed, flush the previous output row */
893 12 : tuple = BuildTupleFromCStrings(attinmeta, values);
894 :
895 12 : tuplestore_puttuple(tupstore, tuple);
896 :
897 80 : for (j = 0; j < result_ncols; j++)
898 68 : xpfree(values[j]);
899 : }
900 :
901 18 : values[0] = rowid;
902 33 : for (j = 1; j < ncols - 2; j++)
903 15 : values[j] = SPI_getvalue(spi_tuple, spi_tupdesc, j + 1);
904 :
905 : /* we're no longer on the first pass */
906 18 : firstpass = false;
907 : }
908 :
909 : /* look up the category and fill in the appropriate column */
910 66 : catname = SPI_getvalue(spi_tuple, spi_tupdesc, ncols - 1);
911 :
912 66 : if (catname != NULL)
913 : {
914 594 : crosstab_HashTableLookup(crosstab_hash, catname, catdesc);
915 :
916 66 : if (catdesc)
917 63 : values[catdesc->attidx + ncols - 2] =
918 63 : SPI_getvalue(spi_tuple, spi_tupdesc, ncols);
919 : }
920 :
921 66 : xpfree(lastrowid);
922 66 : xpstrdup(lastrowid, rowid);
923 : }
924 :
925 : /* flush the last output row */
926 6 : tuple = BuildTupleFromCStrings(attinmeta, values);
927 :
928 6 : tuplestore_puttuple(tupstore, tuple);
929 : }
930 :
931 8 : if (SPI_finish() != SPI_OK_FINISH)
932 : /* internal error */
933 0 : elog(ERROR, "get_crosstab_tuplestore: SPI_finish() failed");
934 :
935 8 : return tupstore;
936 : }
937 :
938 : /*
939 : * connectby_text - produce a result set from a hierarchical (parent/child)
940 : * table.
941 : *
942 : * e.g. given table foo:
943 : *
944 : * keyid parent_keyid pos
945 : * ------+------------+--
946 : * row1 NULL 0
947 : * row2 row1 0
948 : * row3 row1 0
949 : * row4 row2 1
950 : * row5 row2 0
951 : * row6 row4 0
952 : * row7 row3 0
953 : * row8 row6 0
954 : * row9 row5 0
955 : *
956 : *
957 : * connectby(text relname, text keyid_fld, text parent_keyid_fld
958 : * [, text orderby_fld], text start_with, int max_depth
959 : * [, text branch_delim])
960 : * connectby('foo', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') returns:
961 : *
962 : * keyid parent_id level branch serial
963 : * ------+-----------+--------+-----------------------
964 : * row2 NULL 0 row2 1
965 : * row5 row2 1 row2~row5 2
966 : * row9 row5 2 row2~row5~row9 3
967 : * row4 row2 1 row2~row4 4
968 : * row6 row4 2 row2~row4~row6 5
969 : * row8 row6 3 row2~row4~row6~row8 6
970 : *
971 : */
972 4 : PG_FUNCTION_INFO_V1(connectby_text);
973 :
974 : #define CONNECTBY_NCOLS 4
975 : #define CONNECTBY_NCOLS_NOBRANCH 3
976 :
977 : Datum
978 19 : connectby_text(PG_FUNCTION_ARGS)
979 : {
980 19 : char *relname = text_to_cstring(PG_GETARG_TEXT_PP(0));
981 19 : char *key_fld = text_to_cstring(PG_GETARG_TEXT_PP(1));
982 19 : char *parent_key_fld = text_to_cstring(PG_GETARG_TEXT_PP(2));
983 19 : char *start_with = text_to_cstring(PG_GETARG_TEXT_PP(3));
984 19 : int max_depth = PG_GETARG_INT32(4);
985 19 : char *branch_delim = NULL;
986 19 : bool show_branch = false;
987 19 : bool show_serial = false;
988 19 : ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
989 : TupleDesc tupdesc;
990 : AttInMetadata *attinmeta;
991 : MemoryContext per_query_ctx;
992 : MemoryContext oldcontext;
993 :
994 : /* check to see if caller supports us returning a tuplestore */
995 19 : if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
996 0 : ereport(ERROR,
997 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
998 : errmsg("set-valued function called in context that cannot accept a set")));
999 19 : if (!(rsinfo->allowedModes & SFRM_Materialize) ||
1000 19 : rsinfo->expectedDesc == NULL)
1001 0 : ereport(ERROR,
1002 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1003 : errmsg("materialize mode required, but it is not allowed in this context")));
1004 :
1005 19 : if (fcinfo->nargs == 6)
1006 : {
1007 11 : branch_delim = text_to_cstring(PG_GETARG_TEXT_PP(5));
1008 11 : show_branch = true;
1009 : }
1010 : else
1011 : /* default is no show, tilde for the delimiter */
1012 8 : branch_delim = pstrdup("~");
1013 :
1014 19 : per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
1015 19 : oldcontext = MemoryContextSwitchTo(per_query_ctx);
1016 :
1017 : /* get the requested return tuple description */
1018 19 : tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
1019 :
1020 : /* does it meet our needs */
1021 19 : validateConnectbyTupleDesc(tupdesc, show_branch, show_serial);
1022 :
1023 : /* OK, use it then */
1024 15 : attinmeta = TupleDescGetAttInMetadata(tupdesc);
1025 :
1026 : /* OK, go to work */
1027 15 : rsinfo->returnMode = SFRM_Materialize;
1028 23 : rsinfo->setResult = connectby(relname,
1029 : key_fld,
1030 : parent_key_fld,
1031 : NULL,
1032 : branch_delim,
1033 : start_with,
1034 : max_depth,
1035 : show_branch,
1036 : show_serial,
1037 : per_query_ctx,
1038 15 : rsinfo->allowedModes & SFRM_Materialize_Random,
1039 : attinmeta);
1040 8 : rsinfo->setDesc = tupdesc;
1041 :
1042 8 : MemoryContextSwitchTo(oldcontext);
1043 :
1044 : /*
1045 : * SFRM_Materialize mode expects us to return a NULL Datum. The actual
1046 : * tuples are in our tuplestore and passed back through rsinfo->setResult.
1047 : * rsinfo->setDesc is set to the tuple description that we actually used
1048 : * to build our tuples with, so the caller can verify we did what it was
1049 : * expecting.
1050 : */
1051 8 : return (Datum) 0;
1052 : }
1053 :
1054 4 : PG_FUNCTION_INFO_V1(connectby_text_serial);
1055 : Datum
1056 4 : connectby_text_serial(PG_FUNCTION_ARGS)
1057 : {
1058 4 : char *relname = text_to_cstring(PG_GETARG_TEXT_PP(0));
1059 4 : char *key_fld = text_to_cstring(PG_GETARG_TEXT_PP(1));
1060 4 : char *parent_key_fld = text_to_cstring(PG_GETARG_TEXT_PP(2));
1061 4 : char *orderby_fld = text_to_cstring(PG_GETARG_TEXT_PP(3));
1062 4 : char *start_with = text_to_cstring(PG_GETARG_TEXT_PP(4));
1063 4 : int max_depth = PG_GETARG_INT32(5);
1064 4 : char *branch_delim = NULL;
1065 4 : bool show_branch = false;
1066 4 : bool show_serial = true;
1067 4 : ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
1068 : TupleDesc tupdesc;
1069 : AttInMetadata *attinmeta;
1070 : MemoryContext per_query_ctx;
1071 : MemoryContext oldcontext;
1072 :
1073 : /* check to see if caller supports us returning a tuplestore */
1074 4 : if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
1075 0 : ereport(ERROR,
1076 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1077 : errmsg("set-valued function called in context that cannot accept a set")));
1078 4 : if (!(rsinfo->allowedModes & SFRM_Materialize) ||
1079 4 : rsinfo->expectedDesc == NULL)
1080 0 : ereport(ERROR,
1081 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1082 : errmsg("materialize mode required, but it is not allowed in this context")));
1083 :
1084 4 : if (fcinfo->nargs == 7)
1085 : {
1086 2 : branch_delim = text_to_cstring(PG_GETARG_TEXT_PP(6));
1087 2 : show_branch = true;
1088 : }
1089 : else
1090 : /* default is no show, tilde for the delimiter */
1091 2 : branch_delim = pstrdup("~");
1092 :
1093 4 : per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
1094 4 : oldcontext = MemoryContextSwitchTo(per_query_ctx);
1095 :
1096 : /* get the requested return tuple description */
1097 4 : tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
1098 :
1099 : /* does it meet our needs */
1100 4 : validateConnectbyTupleDesc(tupdesc, show_branch, show_serial);
1101 :
1102 : /* OK, use it then */
1103 2 : attinmeta = TupleDescGetAttInMetadata(tupdesc);
1104 :
1105 : /* OK, go to work */
1106 2 : rsinfo->returnMode = SFRM_Materialize;
1107 4 : rsinfo->setResult = connectby(relname,
1108 : key_fld,
1109 : parent_key_fld,
1110 : orderby_fld,
1111 : branch_delim,
1112 : start_with,
1113 : max_depth,
1114 : show_branch,
1115 : show_serial,
1116 : per_query_ctx,
1117 2 : rsinfo->allowedModes & SFRM_Materialize_Random,
1118 : attinmeta);
1119 2 : rsinfo->setDesc = tupdesc;
1120 :
1121 2 : MemoryContextSwitchTo(oldcontext);
1122 :
1123 : /*
1124 : * SFRM_Materialize mode expects us to return a NULL Datum. The actual
1125 : * tuples are in our tuplestore and passed back through rsinfo->setResult.
1126 : * rsinfo->setDesc is set to the tuple description that we actually used
1127 : * to build our tuples with, so the caller can verify we did what it was
1128 : * expecting.
1129 : */
1130 2 : return (Datum) 0;
1131 : }
1132 :
1133 :
1134 : /*
1135 : * connectby - does the real work for connectby_text()
1136 : */
1137 : static Tuplestorestate *
1138 17 : connectby(char *relname,
1139 : char *key_fld,
1140 : char *parent_key_fld,
1141 : char *orderby_fld,
1142 : char *branch_delim,
1143 : char *start_with,
1144 : int max_depth,
1145 : bool show_branch,
1146 : bool show_serial,
1147 : MemoryContext per_query_ctx,
1148 : bool randomAccess,
1149 : AttInMetadata *attinmeta)
1150 : {
1151 17 : Tuplestorestate *tupstore = NULL;
1152 : MemoryContext oldcontext;
1153 17 : int serial = 1;
1154 :
1155 : /* Connect to SPI manager */
1156 17 : SPI_connect();
1157 :
1158 : /* switch to longer term context to create the tuple store */
1159 17 : oldcontext = MemoryContextSwitchTo(per_query_ctx);
1160 :
1161 : /* initialize our tuplestore */
1162 17 : tupstore = tuplestore_begin_heap(randomAccess, false, work_mem);
1163 :
1164 17 : MemoryContextSwitchTo(oldcontext);
1165 :
1166 : /* now go get the whole tree */
1167 17 : build_tuplestore_recursively(key_fld,
1168 : parent_key_fld,
1169 : relname,
1170 : orderby_fld,
1171 : branch_delim,
1172 : start_with,
1173 : start_with, /* current_branch */
1174 : 0, /* initial level is 0 */
1175 : &serial, /* initial serial is 1 */
1176 : max_depth,
1177 : show_branch,
1178 : show_serial,
1179 : per_query_ctx,
1180 : attinmeta,
1181 : tupstore);
1182 :
1183 10 : SPI_finish();
1184 :
1185 10 : return tupstore;
1186 : }
1187 :
1188 : static void
1189 65 : build_tuplestore_recursively(char *key_fld,
1190 : char *parent_key_fld,
1191 : char *relname,
1192 : char *orderby_fld,
1193 : char *branch_delim,
1194 : char *start_with,
1195 : char *branch,
1196 : int level,
1197 : int *serial,
1198 : int max_depth,
1199 : bool show_branch,
1200 : bool show_serial,
1201 : MemoryContext per_query_ctx,
1202 : AttInMetadata *attinmeta,
1203 : Tuplestorestate *tupstore)
1204 : {
1205 65 : TupleDesc tupdesc = attinmeta->tupdesc;
1206 : int ret;
1207 : uint64 proc;
1208 : int serial_column;
1209 : StringInfoData sql;
1210 : char **values;
1211 : char *current_key;
1212 : char *current_key_parent;
1213 : char current_level[INT32_STRLEN];
1214 : char serial_str[INT32_STRLEN];
1215 : char *current_branch;
1216 : HeapTuple tuple;
1217 :
1218 65 : if (max_depth > 0 && level > max_depth)
1219 1 : return;
1220 :
1221 64 : initStringInfo(&sql);
1222 :
1223 : /* Build initial sql statement */
1224 64 : if (!show_serial)
1225 : {
1226 52 : appendStringInfo(&sql, "SELECT %s, %s FROM %s WHERE %s = %s AND %s IS NOT NULL AND %s <> %s",
1227 : key_fld,
1228 : parent_key_fld,
1229 : relname,
1230 : parent_key_fld,
1231 : quote_literal_cstr(start_with),
1232 : key_fld, key_fld, parent_key_fld);
1233 52 : serial_column = 0;
1234 : }
1235 : else
1236 : {
1237 12 : appendStringInfo(&sql, "SELECT %s, %s FROM %s WHERE %s = %s AND %s IS NOT NULL AND %s <> %s ORDER BY %s",
1238 : key_fld,
1239 : parent_key_fld,
1240 : relname,
1241 : parent_key_fld,
1242 : quote_literal_cstr(start_with),
1243 : key_fld, key_fld, parent_key_fld,
1244 : orderby_fld);
1245 12 : serial_column = 1;
1246 : }
1247 :
1248 64 : if (show_branch)
1249 40 : values = (char **) palloc((CONNECTBY_NCOLS + serial_column) * sizeof(char *));
1250 : else
1251 24 : values = (char **) palloc((CONNECTBY_NCOLS_NOBRANCH + serial_column) * sizeof(char *));
1252 :
1253 : /* First time through, do a little setup */
1254 64 : if (level == 0)
1255 : {
1256 : /* root value is the one we initially start with */
1257 17 : values[0] = start_with;
1258 :
1259 : /* root value has no parent */
1260 17 : values[1] = NULL;
1261 :
1262 : /* root level is 0 */
1263 17 : sprintf(current_level, "%d", level);
1264 17 : values[2] = current_level;
1265 :
1266 : /* root branch is just starting root value */
1267 17 : if (show_branch)
1268 9 : values[3] = start_with;
1269 :
1270 : /* root starts the serial with 1 */
1271 17 : if (show_serial)
1272 : {
1273 2 : sprintf(serial_str, "%d", (*serial)++);
1274 2 : if (show_branch)
1275 1 : values[4] = serial_str;
1276 : else
1277 1 : values[3] = serial_str;
1278 : }
1279 :
1280 : /* construct the tuple */
1281 17 : tuple = BuildTupleFromCStrings(attinmeta, values);
1282 :
1283 : /* now store it */
1284 17 : tuplestore_puttuple(tupstore, tuple);
1285 :
1286 : /* increment level */
1287 17 : level++;
1288 : }
1289 :
1290 : /* Retrieve the desired rows */
1291 64 : ret = SPI_execute(sql.data, true, 0);
1292 64 : proc = SPI_processed;
1293 :
1294 : /* Check for qualifying tuples */
1295 64 : if ((ret == SPI_OK_SELECT) && (proc > 0))
1296 : {
1297 : HeapTuple spi_tuple;
1298 48 : SPITupleTable *tuptable = SPI_tuptable;
1299 48 : TupleDesc spi_tupdesc = tuptable->tupdesc;
1300 : uint64 i;
1301 : StringInfoData branchstr;
1302 : StringInfoData chk_branchstr;
1303 : StringInfoData chk_current_key;
1304 :
1305 : /*
1306 : * Check that return tupdesc is compatible with the one we got from
1307 : * the query.
1308 : */
1309 48 : compatConnectbyTupleDescs(tupdesc, spi_tupdesc);
1310 :
1311 43 : initStringInfo(&branchstr);
1312 43 : initStringInfo(&chk_branchstr);
1313 43 : initStringInfo(&chk_current_key);
1314 :
1315 88 : for (i = 0; i < proc; i++)
1316 : {
1317 : /* initialize branch for this pass */
1318 52 : appendStringInfoString(&branchstr, branch);
1319 52 : appendStringInfo(&chk_branchstr, "%s%s%s", branch_delim, branch, branch_delim);
1320 :
1321 : /* get the next sql result tuple */
1322 52 : spi_tuple = tuptable->vals[i];
1323 :
1324 : /* get the current key (might be NULL) */
1325 52 : current_key = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
1326 :
1327 : /* get the parent key (might be NULL) */
1328 52 : current_key_parent = SPI_getvalue(spi_tuple, spi_tupdesc, 2);
1329 :
1330 : /* get the current level */
1331 52 : sprintf(current_level, "%d", level);
1332 :
1333 : /* check to see if this key is also an ancestor */
1334 52 : if (current_key)
1335 : {
1336 50 : appendStringInfo(&chk_current_key, "%s%s%s",
1337 : branch_delim, current_key, branch_delim);
1338 50 : if (strstr(chk_branchstr.data, chk_current_key.data))
1339 2 : ereport(ERROR,
1340 : (errcode(ERRCODE_INVALID_RECURSION),
1341 : errmsg("infinite recursion detected")));
1342 : }
1343 :
1344 : /* OK, extend the branch */
1345 50 : if (current_key)
1346 48 : appendStringInfo(&branchstr, "%s%s", branch_delim, current_key);
1347 50 : current_branch = branchstr.data;
1348 :
1349 : /* build a tuple */
1350 50 : values[0] = current_key;
1351 50 : values[1] = current_key_parent;
1352 50 : values[2] = current_level;
1353 50 : if (show_branch)
1354 32 : values[3] = current_branch;
1355 50 : if (show_serial)
1356 : {
1357 10 : sprintf(serial_str, "%d", (*serial)++);
1358 10 : if (show_branch)
1359 5 : values[4] = serial_str;
1360 : else
1361 5 : values[3] = serial_str;
1362 : }
1363 :
1364 50 : tuple = BuildTupleFromCStrings(attinmeta, values);
1365 :
1366 : /* store the tuple for later use */
1367 50 : tuplestore_puttuple(tupstore, tuple);
1368 :
1369 50 : heap_freetuple(tuple);
1370 :
1371 : /* recurse using current_key as the new start_with */
1372 50 : if (current_key)
1373 48 : build_tuplestore_recursively(key_fld,
1374 : parent_key_fld,
1375 : relname,
1376 : orderby_fld,
1377 : branch_delim,
1378 : current_key,
1379 : current_branch,
1380 : level + 1,
1381 : serial,
1382 : max_depth,
1383 : show_branch,
1384 : show_serial,
1385 : per_query_ctx,
1386 : attinmeta,
1387 : tupstore);
1388 :
1389 45 : xpfree(current_key);
1390 45 : xpfree(current_key_parent);
1391 :
1392 : /* reset branch for next pass */
1393 45 : resetStringInfo(&branchstr);
1394 45 : resetStringInfo(&chk_branchstr);
1395 45 : resetStringInfo(&chk_current_key);
1396 : }
1397 :
1398 36 : xpfree(branchstr.data);
1399 36 : xpfree(chk_branchstr.data);
1400 36 : xpfree(chk_current_key.data);
1401 : }
1402 : }
1403 :
1404 : /*
1405 : * Check expected (query runtime) tupdesc suitable for Connectby
1406 : */
1407 : static void
1408 23 : validateConnectbyTupleDesc(TupleDesc td, bool show_branch, bool show_serial)
1409 : {
1410 : int expected_cols;
1411 :
1412 : /* are there the correct number of columns */
1413 23 : if (show_branch)
1414 13 : expected_cols = CONNECTBY_NCOLS;
1415 : else
1416 10 : expected_cols = CONNECTBY_NCOLS_NOBRANCH;
1417 23 : if (show_serial)
1418 4 : expected_cols++;
1419 :
1420 23 : if (td->natts != expected_cols)
1421 2 : ereport(ERROR,
1422 : (errcode(ERRCODE_DATATYPE_MISMATCH),
1423 : errmsg("invalid connectby return type"),
1424 : errdetail("Return row must have %d columns, not %d.",
1425 : expected_cols, td->natts)));
1426 :
1427 : /* the first two columns will be checked against the input tuples later */
1428 :
1429 : /* check that the type of the third column is INT4 */
1430 21 : if (TupleDescAttr(td, 2)->atttypid != INT4OID)
1431 1 : ereport(ERROR,
1432 : (errcode(ERRCODE_DATATYPE_MISMATCH),
1433 : errmsg("invalid connectby return type"),
1434 : errdetail("Third return column (depth) must be type %s.",
1435 : format_type_be(INT4OID))));
1436 :
1437 : /* check that the type of the branch column is TEXT if applicable */
1438 20 : if (show_branch && TupleDescAttr(td, 3)->atttypid != TEXTOID)
1439 1 : ereport(ERROR,
1440 : (errcode(ERRCODE_DATATYPE_MISMATCH),
1441 : errmsg("invalid connectby return type"),
1442 : errdetail("Fourth return column (branch) must be type %s.",
1443 : format_type_be(TEXTOID))));
1444 :
1445 : /* check that the type of the serial column is INT4 if applicable */
1446 19 : if (show_branch && show_serial &&
1447 2 : TupleDescAttr(td, 4)->atttypid != INT4OID)
1448 1 : ereport(ERROR,
1449 : (errcode(ERRCODE_DATATYPE_MISMATCH),
1450 : errmsg("invalid connectby return type"),
1451 : errdetail("Fifth return column (serial) must be type %s.",
1452 : format_type_be(INT4OID))));
1453 18 : if (!show_branch && show_serial &&
1454 2 : TupleDescAttr(td, 3)->atttypid != INT4OID)
1455 1 : ereport(ERROR,
1456 : (errcode(ERRCODE_DATATYPE_MISMATCH),
1457 : errmsg("invalid connectby return type"),
1458 : errdetail("Fourth return column (serial) must be type %s.",
1459 : format_type_be(INT4OID))));
1460 :
1461 : /* OK, the tupdesc is valid for our purposes */
1462 17 : }
1463 :
1464 : /*
1465 : * Check if output tupdesc and SQL query's tupdesc are compatible
1466 : */
1467 : static void
1468 48 : compatConnectbyTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc)
1469 : {
1470 : Oid ret_atttypid;
1471 : Oid sql_atttypid;
1472 : int32 ret_atttypmod;
1473 : int32 sql_atttypmod;
1474 :
1475 : /*
1476 : * Query result must have at least 2 columns.
1477 : */
1478 48 : if (sql_tupdesc->natts < 2)
1479 1 : ereport(ERROR,
1480 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1481 : errmsg("invalid connectby source data query"),
1482 : errdetail("The query must return at least two columns.")));
1483 :
1484 : /*
1485 : * These columns must match the result type indicated by the calling
1486 : * query.
1487 : */
1488 47 : ret_atttypid = TupleDescAttr(ret_tupdesc, 0)->atttypid;
1489 47 : sql_atttypid = TupleDescAttr(sql_tupdesc, 0)->atttypid;
1490 47 : ret_atttypmod = TupleDescAttr(ret_tupdesc, 0)->atttypmod;
1491 47 : sql_atttypmod = TupleDescAttr(sql_tupdesc, 0)->atttypmod;
1492 47 : if (ret_atttypid != sql_atttypid ||
1493 0 : (ret_atttypmod >= 0 && ret_atttypmod != sql_atttypmod))
1494 2 : ereport(ERROR,
1495 : (errcode(ERRCODE_DATATYPE_MISMATCH),
1496 : errmsg("invalid connectby return type"),
1497 : errdetail("Source key type %s does not match return key type %s.",
1498 : format_type_with_typemod(sql_atttypid, sql_atttypmod),
1499 : format_type_with_typemod(ret_atttypid, ret_atttypmod))));
1500 :
1501 45 : ret_atttypid = TupleDescAttr(ret_tupdesc, 1)->atttypid;
1502 45 : sql_atttypid = TupleDescAttr(sql_tupdesc, 1)->atttypid;
1503 45 : ret_atttypmod = TupleDescAttr(ret_tupdesc, 1)->atttypmod;
1504 45 : sql_atttypmod = TupleDescAttr(sql_tupdesc, 1)->atttypmod;
1505 45 : if (ret_atttypid != sql_atttypid ||
1506 0 : (ret_atttypmod >= 0 && ret_atttypmod != sql_atttypmod))
1507 2 : ereport(ERROR,
1508 : (errcode(ERRCODE_DATATYPE_MISMATCH),
1509 : errmsg("invalid connectby return type"),
1510 : errdetail("Source parent key type %s does not match return parent key type %s.",
1511 : format_type_with_typemod(sql_atttypid, sql_atttypmod),
1512 : format_type_with_typemod(ret_atttypid, ret_atttypmod))));
1513 :
1514 : /* OK, the two tupdescs are compatible for our purposes */
1515 43 : }
1516 :
1517 : /*
1518 : * Check if crosstab output tupdesc agrees with input tupdesc
1519 : */
1520 : static void
1521 19 : compatCrosstabTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc)
1522 : {
1523 : int i;
1524 : Oid ret_atttypid;
1525 : Oid sql_atttypid;
1526 : int32 ret_atttypmod;
1527 : int32 sql_atttypmod;
1528 :
1529 19 : if (ret_tupdesc->natts < 2)
1530 1 : ereport(ERROR,
1531 : (errcode(ERRCODE_DATATYPE_MISMATCH),
1532 : errmsg("invalid crosstab return type"),
1533 : errdetail("Return row must have at least two columns.")));
1534 : Assert(sql_tupdesc->natts == 3); /* already checked by caller */
1535 :
1536 : /* check the row_name types match */
1537 18 : ret_atttypid = TupleDescAttr(ret_tupdesc, 0)->atttypid;
1538 18 : sql_atttypid = TupleDescAttr(sql_tupdesc, 0)->atttypid;
1539 18 : ret_atttypmod = TupleDescAttr(ret_tupdesc, 0)->atttypmod;
1540 18 : sql_atttypmod = TupleDescAttr(sql_tupdesc, 0)->atttypmod;
1541 18 : if (ret_atttypid != sql_atttypid ||
1542 0 : (ret_atttypmod >= 0 && ret_atttypmod != sql_atttypmod))
1543 1 : ereport(ERROR,
1544 : (errcode(ERRCODE_DATATYPE_MISMATCH),
1545 : errmsg("invalid crosstab return type"),
1546 : errdetail("Source row_name datatype %s does not match return row_name datatype %s.",
1547 : format_type_with_typemod(sql_atttypid, sql_atttypmod),
1548 : format_type_with_typemod(ret_atttypid, ret_atttypmod))));
1549 :
1550 : /*
1551 : * attribute [1] of sql tuple is the category; no need to check it
1552 : * attribute [2] of sql tuple should match attributes [1] to [natts - 1]
1553 : * of the return tuple
1554 : */
1555 17 : sql_atttypid = TupleDescAttr(sql_tupdesc, 2)->atttypid;
1556 17 : sql_atttypmod = TupleDescAttr(sql_tupdesc, 2)->atttypmod;
1557 66 : for (i = 1; i < ret_tupdesc->natts; i++)
1558 : {
1559 50 : ret_atttypid = TupleDescAttr(ret_tupdesc, i)->atttypid;
1560 50 : ret_atttypmod = TupleDescAttr(ret_tupdesc, i)->atttypmod;
1561 :
1562 50 : if (ret_atttypid != sql_atttypid ||
1563 0 : (ret_atttypmod >= 0 && ret_atttypmod != sql_atttypmod))
1564 1 : ereport(ERROR,
1565 : (errcode(ERRCODE_DATATYPE_MISMATCH),
1566 : errmsg("invalid crosstab return type"),
1567 : errdetail("Source value datatype %s does not match return value datatype %s in column %d.",
1568 : format_type_with_typemod(sql_atttypid, sql_atttypmod),
1569 : format_type_with_typemod(ret_atttypid, ret_atttypmod),
1570 : i + 1)));
1571 : }
1572 :
1573 : /* OK, the two tupdescs are compatible for our purposes */
1574 16 : }
|
