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

Generated by: LCOV version 1.14