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

Generated by: LCOV version 1.13