4 * Sample to demonstrate C functions which return setof scalar
6 * Joe Conway <mail@joeconway.com>
8 * Nabil Sayegh <postgresql@e-trolley.de>
10 * Copyright (c) 2002-2007, PostgreSQL Global Development Group
12 * Permission to use, copy, modify, and distribute this software and its
13 * documentation for any purpose, without fee, and without a written agreement
14 * is hereby granted, provided that the above copyright notice and this
15 * paragraph and the following two paragraphs appear in all copies.
17 * IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
18 * DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
19 * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
20 * DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
21 * POSSIBILITY OF SUCH DAMAGE.
23 * THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
24 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
25 * AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
26 * ON AN "AS IS" BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
27 * PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
36 #include "executor/spi.h"
37 #include "lib/stringinfo.h"
38 #include "miscadmin.h"
39 #include "utils/builtins.h"
40 #include "utils/guc.h"
41 #include "utils/lsyscache.h"
43 #include "tablefunc.h"
47 static int load_categories_hash(char *cats_sql, MemoryContext per_query_ctx);
48 static Tuplestorestate *get_crosstab_tuplestore(char *sql,
51 MemoryContext per_query_ctx);
52 static void validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch, bool show_serial);
53 static bool compatCrosstabTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
54 static bool compatConnectbyTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2);
55 static void get_normal_pair(float8 *x1, float8 *x2);
56 static Tuplestorestate *connectby(char *relname,
65 MemoryContext per_query_ctx,
66 AttInMetadata *attinmeta);
67 static Tuplestorestate *build_tuplestore_recursively(char *key_fld,
79 MemoryContext per_query_ctx,
80 AttInMetadata *attinmeta,
81 Tuplestorestate *tupstore);
82 static char *quote_literal_cstr(char *rawstr);
86 float8 mean; /* mean of the distribution */
87 float8 stddev; /* stddev of the distribution */
88 float8 carry_val; /* hold second generated value */
89 bool use_carry; /* use second generated value */
94 SPITupleTable *spi_tuptable; /* sql results from user query */
95 char *lastrowid; /* rowid of the last tuple sent */
98 #define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
99 #define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
100 #define xpfree(var_) \
109 #define xpstrdup(tgtvar_, srcvar_) \
112 tgtvar_ = pstrdup(srcvar_); \
117 #define xstreq(tgtvar_, srcvar_) \
118 (((tgtvar_ == NULL) && (srcvar_ == NULL)) || \
119 ((tgtvar_ != NULL) && (srcvar_ != NULL) && (strcmp(tgtvar_, srcvar_) == 0)))
121 /* sign, 10 digits, '\0' */
122 #define INT32_STRLEN 12
124 /* hash table support */
125 static HTAB *crosstab_HashTable;
127 /* The information we cache about loaded procedures */
128 typedef struct crosstab_cat_desc
131 int attidx; /* zero based */
134 #define MAX_CATNAME_LEN NAMEDATALEN
137 #define crosstab_HashTableLookup(CATNAME, CATDESC) \
139 crosstab_HashEnt *hentry; char key[MAX_CATNAME_LEN]; \
141 MemSet(key, 0, MAX_CATNAME_LEN); \
142 snprintf(key, MAX_CATNAME_LEN - 1, "%s", CATNAME); \
143 hentry = (crosstab_HashEnt*) hash_search(crosstab_HashTable, \
144 key, HASH_FIND, NULL); \
146 CATDESC = hentry->catdesc; \
151 #define crosstab_HashTableInsert(CATDESC) \
153 crosstab_HashEnt *hentry; bool found; char key[MAX_CATNAME_LEN]; \
155 MemSet(key, 0, MAX_CATNAME_LEN); \
156 snprintf(key, MAX_CATNAME_LEN - 1, "%s", CATDESC->catname); \
157 hentry = (crosstab_HashEnt*) hash_search(crosstab_HashTable, \
158 key, HASH_ENTER, &found); \
161 (errcode(ERRCODE_DUPLICATE_OBJECT), \
162 errmsg("duplicate category name"))); \
163 hentry->catdesc = CATDESC; \
167 typedef struct crosstab_hashent
169 char internal_catname[MAX_CATNAME_LEN];
170 crosstab_cat_desc *catdesc;
174 * normal_rand - return requested number of random values
175 * with a Gaussian (Normal) distribution.
177 * inputs are int numvals, float8 mean, and float8 stddev
178 * returns setof float8
180 PG_FUNCTION_INFO_V1(normal_rand);
182 normal_rand(PG_FUNCTION_ARGS)
184 FuncCallContext *funcctx;
187 normal_rand_fctx *fctx;
192 MemoryContext oldcontext;
194 /* stuff done only on the first call of the function */
195 if (SRF_IS_FIRSTCALL())
197 /* create a function context for cross-call persistence */
198 funcctx = SRF_FIRSTCALL_INIT();
201 * switch to memory context appropriate for multiple function calls
203 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
205 /* total number of tuples to be returned */
206 funcctx->max_calls = PG_GETARG_UINT32(0);
208 /* allocate memory for user context */
209 fctx = (normal_rand_fctx *) palloc(sizeof(normal_rand_fctx));
212 * Use fctx to keep track of upper and lower bounds from call to call.
213 * It will also be used to carry over the spare value we get from the
214 * Box-Muller algorithm so that we only actually calculate a new value
217 fctx->mean = PG_GETARG_FLOAT8(1);
218 fctx->stddev = PG_GETARG_FLOAT8(2);
220 fctx->use_carry = false;
222 funcctx->user_fctx = fctx;
224 MemoryContextSwitchTo(oldcontext);
227 /* stuff done on every call of the function */
228 funcctx = SRF_PERCALL_SETUP();
230 call_cntr = funcctx->call_cntr;
231 max_calls = funcctx->max_calls;
232 fctx = funcctx->user_fctx;
234 stddev = fctx->stddev;
235 carry_val = fctx->carry_val;
236 use_carry = fctx->use_carry;
238 if (call_cntr < max_calls) /* do when there is more left to send */
245 * reset use_carry and use second value obtained on last pass
247 fctx->use_carry = false;
255 /* Get the next two normal values */
256 get_normal_pair(&normval_1, &normval_2);
259 result = mean + (stddev * normval_1);
261 /* and save the second */
262 fctx->carry_val = mean + (stddev * normval_2);
263 fctx->use_carry = true;
266 /* send the result */
267 SRF_RETURN_NEXT(funcctx, Float8GetDatum(result));
270 /* do when there is no more left */
271 SRF_RETURN_DONE(funcctx);
276 * Assigns normally distributed (Gaussian) values to a pair of provided
277 * parameters, with mean 0, standard deviation 1.
279 * This routine implements Algorithm P (Polar method for normal deviates)
280 * from Knuth's _The_Art_of_Computer_Programming_, Volume 2, 3rd ed., pages
281 * 122-126. Knuth cites his source as "The polar method", G. E. P. Box, M. E.
282 * Muller, and G. Marsaglia, _Annals_Math,_Stat._ 29 (1958), 610-611.
286 get_normal_pair(float8 *x1, float8 *x2)
296 u1 = (float8) random() / (float8) MAX_RANDOM_VALUE;
297 u2 = (float8) random() / (float8) MAX_RANDOM_VALUE;
299 v1 = (2.0 * u1) - 1.0;
300 v2 = (2.0 * u2) - 1.0;
302 s = v1 * v1 + v2 * v2;
312 s = sqrt((-2.0 * log(s)) / s);
319 * crosstab - create a crosstab of rowids and values columns from a
320 * SQL statement returning one rowid column, one category column,
321 * and one value column.
323 * e.g. given sql which produces:
326 * ------+-------+-------
337 * <===== values columns =====>
338 * rowid cat1 cat2 cat3 cat4
339 * ------+-------+-------+-------+-------
340 * row1 val1 val2 val3 val4
341 * row2 val5 val6 val7 val8
344 * 1. SQL result must be ordered by 1,2.
345 * 2. The number of values columns depends on the tuple description
346 * of the function's declared return type. The return type's columns
347 * must match the datatypes of the SQL query's result. The datatype
348 * of the category column can be anything, however.
349 * 3. Missing values (i.e. not enough adjacent rows of same rowid to
350 * fill the number of result values columns) are filled in with nulls.
351 * 4. Extra values (i.e. too many adjacent rows of same rowid to fill
352 * the number of result values columns) are skipped.
353 * 5. Rows with all nulls in the values columns are skipped.
355 PG_FUNCTION_INFO_V1(crosstab);
357 crosstab(PG_FUNCTION_ARGS)
359 FuncCallContext *funcctx;
360 TupleDesc ret_tupdesc;
363 AttInMetadata *attinmeta;
364 SPITupleTable *spi_tuptable = NULL;
365 TupleDesc spi_tupdesc;
366 char *lastrowid = NULL;
370 bool firstpass = false;
371 MemoryContext oldcontext;
373 /* stuff done only on the first call of the function */
374 if (SRF_IS_FIRSTCALL())
376 char *sql = GET_STR(PG_GETARG_TEXT_P(0));
381 /* create a function context for cross-call persistence */
382 funcctx = SRF_FIRSTCALL_INIT();
385 * switch to memory context appropriate for multiple function calls
387 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
389 /* Connect to SPI manager */
390 if ((ret = SPI_connect()) < 0)
392 elog(ERROR, "crosstab: SPI_connect returned %d", ret);
394 /* Retrieve the desired rows */
395 ret = SPI_execute(sql, true, 0);
396 proc = SPI_processed;
398 /* Check for qualifying tuples */
399 if ((ret == SPI_OK_SELECT) && (proc > 0))
401 spi_tuptable = SPI_tuptable;
402 spi_tupdesc = spi_tuptable->tupdesc;
405 * The provided SQL query must always return three columns.
408 * the label or identifier for each row in the final result
410 * the label or identifier for each column in the final result
412 * the value for each column in the final result
415 if (spi_tupdesc->natts != 3)
417 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
418 errmsg("invalid source data SQL statement"),
419 errdetail("The provided SQL must return 3 "
420 "columns: rowid, category, and values.")));
424 /* no qualifying tuples */
426 SRF_RETURN_DONE(funcctx);
429 /* SPI switches context on us, so reset it */
430 MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
432 /* get a tuple descriptor for our result type */
433 switch (get_call_result_type(fcinfo, NULL, &tupdesc))
435 case TYPEFUNC_COMPOSITE:
438 case TYPEFUNC_RECORD:
439 /* failed to determine actual type of RECORD */
441 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
442 errmsg("function returning record called in context "
443 "that cannot accept type record")));
446 /* result type isn't composite */
447 elog(ERROR, "return type must be a row type");
451 /* make sure we have a persistent copy of the tupdesc */
452 tupdesc = CreateTupleDescCopy(tupdesc);
455 * Check that return tupdesc is compatible with the data we got from
456 * SPI, at least based on number and type of attributes
458 if (!compatCrosstabTupleDescs(tupdesc, spi_tupdesc))
460 (errcode(ERRCODE_SYNTAX_ERROR),
461 errmsg("return and sql tuple descriptions are " \
465 * Generate attribute metadata needed later to produce tuples from raw
468 attinmeta = TupleDescGetAttInMetadata(tupdesc);
469 funcctx->attinmeta = attinmeta;
471 /* allocate memory for user context */
472 fctx = (crosstab_fctx *) palloc(sizeof(crosstab_fctx));
475 * Save spi data for use across calls
477 fctx->spi_tuptable = spi_tuptable;
478 fctx->lastrowid = NULL;
479 funcctx->user_fctx = fctx;
481 /* total number of tuples to be returned */
482 funcctx->max_calls = proc;
484 MemoryContextSwitchTo(oldcontext);
488 /* stuff done on every call of the function */
489 funcctx = SRF_PERCALL_SETUP();
492 * initialize per-call variables
494 call_cntr = funcctx->call_cntr;
495 max_calls = funcctx->max_calls;
497 /* user context info */
498 fctx = (crosstab_fctx *) funcctx->user_fctx;
499 lastrowid = fctx->lastrowid;
500 spi_tuptable = fctx->spi_tuptable;
503 spi_tupdesc = spi_tuptable->tupdesc;
505 /* attribute return type and return tuple description */
506 attinmeta = funcctx->attinmeta;
507 ret_tupdesc = attinmeta->tupdesc;
509 /* the return tuple always must have 1 rowid + num_categories columns */
510 num_categories = ret_tupdesc->natts - 1;
512 if (call_cntr < max_calls) /* do when there is more left to send */
517 bool skip_tuple = false;
522 values = (char **) palloc((1 + num_categories) * sizeof(char *));
524 /* and make sure it's clear */
525 memset(values, '\0', (1 + num_categories) * sizeof(char *));
528 * now loop through the sql results and assign each value in
529 * sequence to the next category
531 for (i = 0; i < num_categories; i++)
536 /* see if we've gone too far already */
537 if (call_cntr >= max_calls)
540 /* get the next sql result tuple */
541 spi_tuple = spi_tuptable->vals[call_cntr];
543 /* get the rowid from the current sql result tuple */
544 rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
547 * If this is the first pass through the values for this
548 * rowid, set the first column to rowid
552 xpstrdup(values[0], rowid);
555 * Check to see if the rowid is the same as that of the
556 * last tuple sent -- if so, skip this tuple entirely
558 if (!firstpass && xstreq(lastrowid, rowid))
566 * If rowid hasn't changed on us, continue building the ouput
569 if (xstreq(rowid, values[0]))
572 * Get the next category item value, which is always
573 * attribute number three.
575 * Be careful to assign the value to the array index based
576 * on which category we are presently processing.
578 values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3);
581 * increment the counter since we consume a row for each
582 * category, but not for last pass because the API will do
585 if (i < (num_categories - 1))
586 call_cntr = ++funcctx->call_cntr;
591 * We'll fill in NULLs for the missing values, but we need
592 * to decrement the counter since this sql result row
593 * doesn't belong to the current output tuple.
595 call_cntr = --funcctx->call_cntr;
602 * switch to memory context appropriate for multiple function
605 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
607 xpfree(fctx->lastrowid);
608 xpstrdup(fctx->lastrowid, values[0]);
609 lastrowid = fctx->lastrowid;
611 MemoryContextSwitchTo(oldcontext);
615 /* build the tuple */
616 tuple = BuildTupleFromCStrings(attinmeta, values);
618 /* make the tuple into a datum */
619 result = HeapTupleGetDatum(tuple);
622 for (i = 0; i < num_categories + 1; i++)
623 if (values[i] != NULL)
627 SRF_RETURN_NEXT(funcctx, result);
632 * Skipping this tuple entirely, but we need to advance the
633 * counter like the API would if we had returned one.
635 call_cntr = ++funcctx->call_cntr;
637 /* we'll start over at the top */
640 /* see if we've gone too far already */
641 if (call_cntr >= max_calls)
643 /* release SPI related resources */
645 SRF_RETURN_DONE(funcctx);
648 /* need to reset this before the next tuple is started */
654 /* do when there is no more left */
656 /* release SPI related resources */
658 SRF_RETURN_DONE(funcctx);
663 * crosstab_hash - reimplement crosstab as materialized function and
664 * properly deal with missing values (i.e. don't pack remaining
665 * values to the left)
667 * crosstab - create a crosstab of rowids and values columns from a
668 * SQL statement returning one rowid column, one category column,
669 * and one value column.
671 * e.g. given sql which produces:
674 * ------+-------+-------
684 * <===== values columns =====>
685 * rowid cat1 cat2 cat3 cat4
686 * ------+-------+-------+-------+-------
687 * row1 val1 val2 null val4
688 * row2 val5 val6 val7 val8
691 * 1. SQL result must be ordered by 1.
692 * 2. The number of values columns depends on the tuple description
693 * of the function's declared return type.
694 * 3. Missing values (i.e. missing category) are filled in with nulls.
695 * 4. Extra values (i.e. not in category results) are skipped.
697 PG_FUNCTION_INFO_V1(crosstab_hash);
699 crosstab_hash(PG_FUNCTION_ARGS)
701 char *sql = GET_STR(PG_GETARG_TEXT_P(0));
702 char *cats_sql = GET_STR(PG_GETARG_TEXT_P(1));
703 ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
705 MemoryContext per_query_ctx;
706 MemoryContext oldcontext;
709 /* check to see if caller supports us returning a tuplestore */
710 if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
712 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
713 errmsg("set-valued function called in context that cannot accept a set")));
714 if (!(rsinfo->allowedModes & SFRM_Materialize))
716 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
717 errmsg("materialize mode required, but it is not " \
718 "allowed in this context")));
720 per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
721 oldcontext = MemoryContextSwitchTo(per_query_ctx);
723 /* get the requested return tuple description */
724 tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
727 * Check to make sure we have a reasonable tuple descriptor
729 * Note we will attempt to coerce the values into whatever the return
730 * attribute type is and depend on the "in" function to complain if
733 if (tupdesc->natts < 2)
735 (errcode(ERRCODE_SYNTAX_ERROR),
736 errmsg("query-specified return tuple and " \
737 "crosstab function are not compatible")));
739 /* load up the categories hash table */
740 num_categories = load_categories_hash(cats_sql, per_query_ctx);
742 /* let the caller know we're sending back a tuplestore */
743 rsinfo->returnMode = SFRM_Materialize;
745 /* now go build it */
746 rsinfo->setResult = get_crosstab_tuplestore(sql,
752 * SFRM_Materialize mode expects us to return a NULL Datum. The actual
753 * tuples are in our tuplestore and passed back through rsinfo->setResult.
754 * rsinfo->setDesc is set to the tuple description that we actually used
755 * to build our tuples with, so the caller can verify we did what it was
758 rsinfo->setDesc = tupdesc;
759 MemoryContextSwitchTo(oldcontext);
765 * load up the categories hash table
768 load_categories_hash(char *cats_sql, MemoryContext per_query_ctx)
773 MemoryContext SPIcontext;
774 int num_categories = 0;
776 /* initialize the category hash table */
777 ctl.keysize = MAX_CATNAME_LEN;
778 ctl.entrysize = sizeof(crosstab_HashEnt);
781 * use INIT_CATS, defined above as a guess of how many hash table entries
782 * to create, initially
784 crosstab_HashTable = hash_create("crosstab hash", INIT_CATS, &ctl, HASH_ELEM);
786 /* Connect to SPI manager */
787 if ((ret = SPI_connect()) < 0)
789 elog(ERROR, "load_categories_hash: SPI_connect returned %d", ret);
791 /* Retrieve the category name rows */
792 ret = SPI_execute(cats_sql, true, 0);
793 num_categories = proc = SPI_processed;
795 /* Check for qualifying tuples */
796 if ((ret == SPI_OK_SELECT) && (proc > 0))
798 SPITupleTable *spi_tuptable = SPI_tuptable;
799 TupleDesc spi_tupdesc = spi_tuptable->tupdesc;
803 * The provided categories SQL query must always return one column:
804 * category - the label or identifier for each column
806 if (spi_tupdesc->natts != 1)
808 (errcode(ERRCODE_SYNTAX_ERROR),
809 errmsg("provided \"categories\" SQL must " \
810 "return 1 column of at least one row")));
812 for (i = 0; i < proc; i++)
814 crosstab_cat_desc *catdesc;
818 /* get the next sql result tuple */
819 spi_tuple = spi_tuptable->vals[i];
821 /* get the category from the current sql result tuple */
822 catname = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
824 SPIcontext = MemoryContextSwitchTo(per_query_ctx);
826 catdesc = (crosstab_cat_desc *) palloc(sizeof(crosstab_cat_desc));
827 catdesc->catname = catname;
830 /* Add the proc description block to the hashtable */
831 crosstab_HashTableInsert(catdesc);
833 MemoryContextSwitchTo(SPIcontext);
837 if (SPI_finish() != SPI_OK_FINISH)
839 elog(ERROR, "load_categories_hash: SPI_finish() failed");
841 return num_categories;
845 * create and populate the crosstab tuplestore using the provided source query
847 static Tuplestorestate *
848 get_crosstab_tuplestore(char *sql,
851 MemoryContext per_query_ctx)
853 Tuplestorestate *tupstore;
854 AttInMetadata *attinmeta = TupleDescGetAttInMetadata(tupdesc);
859 MemoryContext SPIcontext;
861 /* initialize our tuplestore */
862 tupstore = tuplestore_begin_heap(true, false, work_mem);
864 /* Connect to SPI manager */
865 if ((ret = SPI_connect()) < 0)
867 elog(ERROR, "get_crosstab_tuplestore: SPI_connect returned %d", ret);
869 /* Now retrieve the crosstab source rows */
870 ret = SPI_execute(sql, true, 0);
871 proc = SPI_processed;
873 /* Check for qualifying tuples */
874 if ((ret == SPI_OK_SELECT) && (proc > 0))
876 SPITupleTable *spi_tuptable = SPI_tuptable;
877 TupleDesc spi_tupdesc = spi_tuptable->tupdesc;
878 int ncols = spi_tupdesc->natts;
880 char *lastrowid = NULL;
881 bool firstpass = true;
886 if (num_categories == 0)
888 /* no qualifying category tuples */
890 (errcode(ERRCODE_SYNTAX_ERROR),
891 errmsg("provided \"categories\" SQL must " \
892 "return 1 column of at least one row")));
896 * The provided SQL query must always return at least three columns:
898 * 1. rowname the label for each row - column 1 in the final result
899 * 2. category the label for each value-column in the final result 3.
900 * value the values used to populate the value-columns
902 * If there are more than three columns, the last two are taken as
903 * "category" and "values". The first column is taken as "rowname".
904 * Additional columns (2 thru N-2) are assumed the same for the same
905 * "rowname", and are copied into the result tuple from the first time
906 * we encounter a particular rowname.
910 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
911 errmsg("invalid source data SQL statement"),
912 errdetail("The provided SQL must return 3 " \
913 " columns; rowid, category, and values.")));
915 result_ncols = (ncols - 2) + num_categories;
917 /* Recheck to make sure we tuple descriptor still looks reasonable */
918 if (tupdesc->natts != result_ncols)
920 (errcode(ERRCODE_SYNTAX_ERROR),
921 errmsg("invalid return type"),
922 errdetail("Query-specified return " \
923 "tuple has %d columns but crosstab " \
924 "returns %d.", tupdesc->natts, result_ncols)));
927 values = (char **) palloc(result_ncols * sizeof(char *));
929 /* and make sure it's clear */
930 memset(values, '\0', result_ncols * sizeof(char *));
932 for (i = 0; i < proc; i++)
935 crosstab_cat_desc *catdesc;
938 /* get the next sql result tuple */
939 spi_tuple = spi_tuptable->vals[i];
941 /* get the rowid from the current sql result tuple */
942 rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
945 * if we're on a new output row, grab the column values up to
948 if (firstpass || !xstreq(lastrowid, rowid))
951 * a new row means we need to flush the old one first, unless
952 * we're on the very first row
956 /* rowid changed, flush the previous output row */
957 tuple = BuildTupleFromCStrings(attinmeta, values);
959 /* switch to appropriate context while storing the tuple */
960 SPIcontext = MemoryContextSwitchTo(per_query_ctx);
961 tuplestore_puttuple(tupstore, tuple);
962 MemoryContextSwitchTo(SPIcontext);
964 for (j = 0; j < result_ncols; j++)
969 for (j = 1; j < ncols - 2; j++)
970 values[j] = SPI_getvalue(spi_tuple, spi_tupdesc, j + 1);
972 /* we're no longer on the first pass */
976 /* look up the category and fill in the appropriate column */
977 catname = SPI_getvalue(spi_tuple, spi_tupdesc, ncols - 1);
981 crosstab_HashTableLookup(catname, catdesc);
984 values[catdesc->attidx + ncols - 2] =
985 SPI_getvalue(spi_tuple, spi_tupdesc, ncols);
989 xpstrdup(lastrowid, rowid);
992 /* flush the last output row */
993 tuple = BuildTupleFromCStrings(attinmeta, values);
995 /* switch to appropriate context while storing the tuple */
996 SPIcontext = MemoryContextSwitchTo(per_query_ctx);
997 tuplestore_puttuple(tupstore, tuple);
998 MemoryContextSwitchTo(SPIcontext);
1001 if (SPI_finish() != SPI_OK_FINISH)
1002 /* internal error */
1003 elog(ERROR, "get_crosstab_tuplestore: SPI_finish() failed");
1005 tuplestore_donestoring(tupstore);
1011 * connectby_text - produce a result set from a hierarchical (parent/child)
1014 * e.g. given table foo:
1016 * keyid parent_keyid pos
1017 * ------+------------+--
1029 * connectby(text relname, text keyid_fld, text parent_keyid_fld
1030 * [, text orderby_fld], text start_with, int max_depth
1031 * [, text branch_delim])
1032 * connectby('foo', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') returns:
1034 * keyid parent_id level branch serial
1035 * ------+-----------+--------+-----------------------
1036 * row2 NULL 0 row2 1
1037 * row5 row2 1 row2~row5 2
1038 * row9 row5 2 row2~row5~row9 3
1039 * row4 row2 1 row2~row4 4
1040 * row6 row4 2 row2~row4~row6 5
1041 * row8 row6 3 row2~row4~row6~row8 6
1044 PG_FUNCTION_INFO_V1(connectby_text);
1046 #define CONNECTBY_NCOLS 4
1047 #define CONNECTBY_NCOLS_NOBRANCH 3
1050 connectby_text(PG_FUNCTION_ARGS)
1052 char *relname = GET_STR(PG_GETARG_TEXT_P(0));
1053 char *key_fld = GET_STR(PG_GETARG_TEXT_P(1));
1054 char *parent_key_fld = GET_STR(PG_GETARG_TEXT_P(2));
1055 char *start_with = GET_STR(PG_GETARG_TEXT_P(3));
1056 int max_depth = PG_GETARG_INT32(4);
1057 char *branch_delim = NULL;
1058 bool show_branch = false;
1059 bool show_serial = false;
1060 ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
1062 AttInMetadata *attinmeta;
1063 MemoryContext per_query_ctx;
1064 MemoryContext oldcontext;
1066 /* check to see if caller supports us returning a tuplestore */
1067 if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
1069 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1070 errmsg("set-valued function called in context that cannot accept a set")));
1071 if (!(rsinfo->allowedModes & SFRM_Materialize))
1073 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1074 errmsg("materialize mode required, but it is not " \
1075 "allowed in this context")));
1077 if (fcinfo->nargs == 6)
1079 branch_delim = GET_STR(PG_GETARG_TEXT_P(5));
1083 /* default is no show, tilde for the delimiter */
1084 branch_delim = pstrdup("~");
1086 per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
1087 oldcontext = MemoryContextSwitchTo(per_query_ctx);
1089 /* get the requested return tuple description */
1090 tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
1092 /* does it meet our needs */
1093 validateConnectbyTupleDesc(tupdesc, show_branch, show_serial);
1095 /* OK, use it then */
1096 attinmeta = TupleDescGetAttInMetadata(tupdesc);
1098 /* OK, go to work */
1099 rsinfo->returnMode = SFRM_Materialize;
1100 rsinfo->setResult = connectby(relname,
1111 rsinfo->setDesc = tupdesc;
1113 MemoryContextSwitchTo(oldcontext);
1116 * SFRM_Materialize mode expects us to return a NULL Datum. The actual
1117 * tuples are in our tuplestore and passed back through rsinfo->setResult.
1118 * rsinfo->setDesc is set to the tuple description that we actually used
1119 * to build our tuples with, so the caller can verify we did what it was
1125 PG_FUNCTION_INFO_V1(connectby_text_serial);
1127 connectby_text_serial(PG_FUNCTION_ARGS)
1129 char *relname = GET_STR(PG_GETARG_TEXT_P(0));
1130 char *key_fld = GET_STR(PG_GETARG_TEXT_P(1));
1131 char *parent_key_fld = GET_STR(PG_GETARG_TEXT_P(2));
1132 char *orderby_fld = GET_STR(PG_GETARG_TEXT_P(3));
1133 char *start_with = GET_STR(PG_GETARG_TEXT_P(4));
1134 int max_depth = PG_GETARG_INT32(5);
1135 char *branch_delim = NULL;
1136 bool show_branch = false;
1137 bool show_serial = true;
1139 ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
1141 AttInMetadata *attinmeta;
1142 MemoryContext per_query_ctx;
1143 MemoryContext oldcontext;
1145 /* check to see if caller supports us returning a tuplestore */
1146 if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
1148 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1149 errmsg("set-valued function called in context that cannot accept a set")));
1150 if (!(rsinfo->allowedModes & SFRM_Materialize))
1152 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1153 errmsg("materialize mode required, but it is not " \
1154 "allowed in this context")));
1156 if (fcinfo->nargs == 7)
1158 branch_delim = GET_STR(PG_GETARG_TEXT_P(6));
1162 /* default is no show, tilde for the delimiter */
1163 branch_delim = pstrdup("~");
1165 per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
1166 oldcontext = MemoryContextSwitchTo(per_query_ctx);
1168 /* get the requested return tuple description */
1169 tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
1171 /* does it meet our needs */
1172 validateConnectbyTupleDesc(tupdesc, show_branch, show_serial);
1174 /* OK, use it then */
1175 attinmeta = TupleDescGetAttInMetadata(tupdesc);
1177 /* OK, go to work */
1178 rsinfo->returnMode = SFRM_Materialize;
1179 rsinfo->setResult = connectby(relname,
1190 rsinfo->setDesc = tupdesc;
1192 MemoryContextSwitchTo(oldcontext);
1195 * SFRM_Materialize mode expects us to return a NULL Datum. The actual
1196 * tuples are in our tuplestore and passed back through rsinfo->setResult.
1197 * rsinfo->setDesc is set to the tuple description that we actually used
1198 * to build our tuples with, so the caller can verify we did what it was
1206 * connectby - does the real work for connectby_text()
1208 static Tuplestorestate *
1209 connectby(char *relname,
1211 char *parent_key_fld,
1218 MemoryContext per_query_ctx,
1219 AttInMetadata *attinmeta)
1221 Tuplestorestate *tupstore = NULL;
1223 MemoryContext oldcontext;
1227 /* Connect to SPI manager */
1228 if ((ret = SPI_connect()) < 0)
1229 /* internal error */
1230 elog(ERROR, "connectby: SPI_connect returned %d", ret);
1232 /* switch to longer term context to create the tuple store */
1233 oldcontext = MemoryContextSwitchTo(per_query_ctx);
1235 /* initialize our tuplestore */
1236 tupstore = tuplestore_begin_heap(true, false, work_mem);
1238 MemoryContextSwitchTo(oldcontext);
1240 /* now go get the whole tree */
1241 tupstore = build_tuplestore_recursively(key_fld,
1247 start_with, /* current_branch */
1248 0, /* initial level is 0 */
1249 &serial, /* initial serial is 1 */
1262 static Tuplestorestate *
1263 build_tuplestore_recursively(char *key_fld,
1264 char *parent_key_fld,
1275 MemoryContext per_query_ctx,
1276 AttInMetadata *attinmeta,
1277 Tuplestorestate *tupstore)
1279 TupleDesc tupdesc = attinmeta->tupdesc;
1280 MemoryContext oldcontext;
1287 char *current_key_parent;
1288 char current_level[INT32_STRLEN];
1289 char serial_str[INT32_STRLEN];
1290 char *current_branch;
1293 if (max_depth > 0 && level > max_depth)
1296 initStringInfo(&sql);
1298 /* Build initial sql statement */
1301 appendStringInfo(&sql, "SELECT %s, %s FROM %s WHERE %s = %s AND %s IS NOT NULL AND %s <> %s",
1306 quote_literal_cstr(start_with),
1307 key_fld, key_fld, parent_key_fld);
1312 appendStringInfo(&sql, "SELECT %s, %s FROM %s WHERE %s = %s AND %s IS NOT NULL AND %s <> %s ORDER BY %s",
1317 quote_literal_cstr(start_with),
1318 key_fld, key_fld, parent_key_fld,
1324 values = (char **) palloc((CONNECTBY_NCOLS + serial_column) * sizeof(char *));
1326 values = (char **) palloc((CONNECTBY_NCOLS_NOBRANCH + serial_column) * sizeof(char *));
1328 /* First time through, do a little setup */
1331 /* root value is the one we initially start with */
1332 values[0] = start_with;
1334 /* root value has no parent */
1337 /* root level is 0 */
1338 sprintf(current_level, "%d", level);
1339 values[2] = current_level;
1341 /* root branch is just starting root value */
1343 values[3] = start_with;
1345 /* root starts the serial with 1 */
1348 sprintf(serial_str, "%d", (*serial)++);
1350 values[4] = serial_str;
1352 values[3] = serial_str;
1355 /* construct the tuple */
1356 tuple = BuildTupleFromCStrings(attinmeta, values);
1358 /* switch to long lived context while storing the tuple */
1359 oldcontext = MemoryContextSwitchTo(per_query_ctx);
1362 tuplestore_puttuple(tupstore, tuple);
1364 /* now reset the context */
1365 MemoryContextSwitchTo(oldcontext);
1367 /* increment level */
1371 /* Retrieve the desired rows */
1372 ret = SPI_execute(sql.data, true, 0);
1373 proc = SPI_processed;
1375 /* Check for qualifying tuples */
1376 if ((ret == SPI_OK_SELECT) && (proc > 0))
1378 HeapTuple spi_tuple;
1379 SPITupleTable *tuptable = SPI_tuptable;
1380 TupleDesc spi_tupdesc = tuptable->tupdesc;
1382 StringInfoData branchstr;
1383 StringInfoData chk_branchstr;
1384 StringInfoData chk_current_key;
1386 /* First time through, do a little more setup */
1390 * Check that return tupdesc is compatible with the one we got
1391 * from the query, but only at level 0 -- no need to check more
1395 if (!compatConnectbyTupleDescs(tupdesc, spi_tupdesc))
1397 (errcode(ERRCODE_SYNTAX_ERROR),
1398 errmsg("invalid return type"),
1399 errdetail("Return and SQL tuple descriptions are " \
1403 initStringInfo(&branchstr);
1404 initStringInfo(&chk_branchstr);
1405 initStringInfo(&chk_current_key);
1407 for (i = 0; i < proc; i++)
1409 /* initialize branch for this pass */
1410 appendStringInfo(&branchstr, "%s", branch);
1411 appendStringInfo(&chk_branchstr, "%s%s%s", branch_delim, branch, branch_delim);
1413 /* get the next sql result tuple */
1414 spi_tuple = tuptable->vals[i];
1416 /* get the current key and parent */
1417 current_key = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
1418 appendStringInfo(&chk_current_key, "%s%s%s", branch_delim, current_key, branch_delim);
1419 current_key_parent = pstrdup(SPI_getvalue(spi_tuple, spi_tupdesc, 2));
1421 /* get the current level */
1422 sprintf(current_level, "%d", level);
1424 /* check to see if this key is also an ancestor */
1425 if (strstr(chk_branchstr.data, chk_current_key.data))
1426 elog(ERROR, "infinite recursion detected");
1428 /* OK, extend the branch */
1429 appendStringInfo(&branchstr, "%s%s", branch_delim, current_key);
1430 current_branch = branchstr.data;
1433 values[0] = pstrdup(current_key);
1434 values[1] = current_key_parent;
1435 values[2] = current_level;
1437 values[3] = current_branch;
1440 sprintf(serial_str, "%d", (*serial)++);
1442 values[4] = serial_str;
1444 values[3] = serial_str;
1447 tuple = BuildTupleFromCStrings(attinmeta, values);
1449 xpfree(current_key);
1450 xpfree(current_key_parent);
1452 /* switch to long lived context while storing the tuple */
1453 oldcontext = MemoryContextSwitchTo(per_query_ctx);
1455 /* store the tuple for later use */
1456 tuplestore_puttuple(tupstore, tuple);
1458 /* now reset the context */
1459 MemoryContextSwitchTo(oldcontext);
1461 heap_freetuple(tuple);
1463 /* recurse using current_key_parent as the new start_with */
1464 tupstore = build_tuplestore_recursively(key_fld,
1480 /* reset branch for next pass */
1481 resetStringInfo(&branchstr);
1482 resetStringInfo(&chk_branchstr);
1483 resetStringInfo(&chk_current_key);
1486 xpfree(branchstr.data);
1487 xpfree(chk_branchstr.data);
1488 xpfree(chk_current_key.data);
1495 * Check expected (query runtime) tupdesc suitable for Connectby
1498 validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch, bool show_serial)
1500 int serial_column = 0;
1505 /* are there the correct number of columns */
1508 if (tupdesc->natts != (CONNECTBY_NCOLS + serial_column))
1510 (errcode(ERRCODE_SYNTAX_ERROR),
1511 errmsg("invalid return type"),
1512 errdetail("Query-specified return tuple has " \
1513 "wrong number of columns.")));
1517 if (tupdesc->natts != CONNECTBY_NCOLS_NOBRANCH + serial_column)
1519 (errcode(ERRCODE_SYNTAX_ERROR),
1520 errmsg("invalid return type"),
1521 errdetail("Query-specified return tuple has " \
1522 "wrong number of columns.")));
1525 /* check that the types of the first two columns match */
1526 if (tupdesc->attrs[0]->atttypid != tupdesc->attrs[1]->atttypid)
1528 (errcode(ERRCODE_SYNTAX_ERROR),
1529 errmsg("invalid return type"),
1530 errdetail("First two columns must be the same type.")));
1532 /* check that the type of the third column is INT4 */
1533 if (tupdesc->attrs[2]->atttypid != INT4OID)
1535 (errcode(ERRCODE_SYNTAX_ERROR),
1536 errmsg("invalid return type"),
1537 errdetail("Third column must be type %s.",
1538 format_type_be(INT4OID))));
1540 /* check that the type of the fourth column is TEXT if applicable */
1541 if (show_branch && tupdesc->attrs[3]->atttypid != TEXTOID)
1543 (errcode(ERRCODE_SYNTAX_ERROR),
1544 errmsg("invalid return type"),
1545 errdetail("Fourth column must be type %s.",
1546 format_type_be(TEXTOID))));
1548 /* check that the type of the fifth column is INT4 */
1549 if (show_branch && show_serial && tupdesc->attrs[4]->atttypid != INT4OID)
1550 elog(ERROR, "query-specified return tuple not valid for Connectby: "
1551 "fifth column must be type %s", format_type_be(INT4OID));
1553 /* check that the type of the fifth column is INT4 */
1554 if (!show_branch && show_serial && tupdesc->attrs[3]->atttypid != INT4OID)
1555 elog(ERROR, "query-specified return tuple not valid for Connectby: "
1556 "fourth column must be type %s", format_type_be(INT4OID));
1558 /* OK, the tupdesc is valid for our purposes */
1562 * Check if spi sql tupdesc and return tupdesc are compatible
1565 compatConnectbyTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc)
1570 /* check the key_fld types match */
1571 ret_atttypid = ret_tupdesc->attrs[0]->atttypid;
1572 sql_atttypid = sql_tupdesc->attrs[0]->atttypid;
1573 if (ret_atttypid != sql_atttypid)
1575 (errcode(ERRCODE_SYNTAX_ERROR),
1576 errmsg("invalid return type"),
1577 errdetail("SQL key field datatype does " \
1578 "not match return key field datatype.")));
1580 /* check the parent_key_fld types match */
1581 ret_atttypid = ret_tupdesc->attrs[1]->atttypid;
1582 sql_atttypid = sql_tupdesc->attrs[1]->atttypid;
1583 if (ret_atttypid != sql_atttypid)
1585 (errcode(ERRCODE_SYNTAX_ERROR),
1586 errmsg("invalid return type"),
1587 errdetail("SQL parent key field datatype does " \
1588 "not match return parent key field datatype.")));
1590 /* OK, the two tupdescs are compatible for our purposes */
1595 * Check if two tupdescs match in type of attributes
1598 compatCrosstabTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc)
1601 Form_pg_attribute ret_attr;
1603 Form_pg_attribute sql_attr;
1606 /* check the rowid types match */
1607 ret_atttypid = ret_tupdesc->attrs[0]->atttypid;
1608 sql_atttypid = sql_tupdesc->attrs[0]->atttypid;
1609 if (ret_atttypid != sql_atttypid)
1611 (errcode(ERRCODE_SYNTAX_ERROR),
1612 errmsg("invalid return type"),
1613 errdetail("SQL rowid datatype does not match " \
1614 "return rowid datatype.")));
1617 * - attribute [1] of the sql tuple is the category; no need to check it -
1618 * attribute [2] of the sql tuple should match attributes [1] to [natts]
1619 * of the return tuple
1621 sql_attr = sql_tupdesc->attrs[2];
1622 for (i = 1; i < ret_tupdesc->natts; i++)
1624 ret_attr = ret_tupdesc->attrs[i];
1626 if (ret_attr->atttypid != sql_attr->atttypid)
1630 /* OK, the two tupdescs are compatible for our purposes */
1635 * Return a properly quoted literal value.
1636 * Uses quote_literal in quote.c
1639 quote_literal_cstr(char *rawstr)
1645 rawstr_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(rawstr)));
1646 result_text = DatumGetTextP(DirectFunctionCall1(quote_literal, PointerGetDatum(rawstr_text)));
1647 result = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(result_text)));