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

Generated by: LCOV version 1.14