LCOV - code coverage report
Current view: top level - contrib/tablefunc - tablefunc.c (source / functions) Hit Total Coverage
Test: PostgreSQL 13devel Lines: 382 420 91.0 %
Date: 2019-11-13 23:06:49 Functions: 19 19 100.0 %
Legend: Lines: hit not hit

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

Generated by: LCOV version 1.13