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

Generated by: LCOV version 1.14