93 #define xpfree(var_) \ 102 #define xpstrdup(tgtvar_, srcvar_) \ 105 tgtvar_ = pstrdup(srcvar_); \ 110 #define xstreq(tgtvar_, srcvar_) \ 111 (((tgtvar_ == NULL) && (srcvar_ == NULL)) || \ 112 ((tgtvar_ != NULL) && (srcvar_ != NULL) && (strcmp(tgtvar_, srcvar_) == 0))) 115 #define INT32_STRLEN 12 124 #define MAX_CATNAME_LEN NAMEDATALEN 127 #define crosstab_HashTableLookup(HASHTAB, CATNAME, CATDESC) \ 129 crosstab_HashEnt *hentry; char key[MAX_CATNAME_LEN]; \ 131 MemSet(key, 0, MAX_CATNAME_LEN); \ 132 snprintf(key, MAX_CATNAME_LEN - 1, "%s", CATNAME); \ 133 hentry = (crosstab_HashEnt*) hash_search(HASHTAB, \ 134 key, HASH_FIND, NULL); \ 136 CATDESC = hentry->catdesc; \ 141 #define crosstab_HashTableInsert(HASHTAB, CATDESC) \ 143 crosstab_HashEnt *hentry; bool found; char key[MAX_CATNAME_LEN]; \ 145 MemSet(key, 0, MAX_CATNAME_LEN); \ 146 snprintf(key, MAX_CATNAME_LEN - 1, "%s", CATDESC->catname); \ 147 hentry = (crosstab_HashEnt*) hash_search(HASHTAB, \ 148 key, HASH_ENTER, &found); \ 151 (errcode(ERRCODE_DUPLICATE_OBJECT), \ 152 errmsg("duplicate category name"))); \ 153 hentry->catdesc = CATDESC; \ 201 (
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
202 errmsg(
"number of rows cannot be negative")));
235 if (call_cntr < max_calls)
256 result = mean + (stddev * normval_1);
259 fctx->
carry_val = mean + (stddev * normval_2);
296 v1 = (2.0 * u1) - 1.0;
297 v2 = (2.0 * u2) - 1.0;
299 s = v1 * v1 + v2 * v2;
309 s = sqrt((-2.0 * log(s)) / s);
377 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
378 errmsg(
"set-valued function called in context that cannot accept a set")));
381 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
382 errmsg(
"materialize mode required, but it is not allowed in this context")));
389 elog(
ERROR,
"crosstab: SPI_connect returned %d", ret);
404 spi_tupdesc = spi_tuptable->
tupdesc;
417 if (spi_tupdesc->natts != 3)
419 (
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
420 errmsg(
"invalid source data SQL statement"),
421 errdetail(
"The provided SQL must return 3 " 422 "columns: rowid, category, and values.")));
433 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
434 errmsg(
"function returning record called in context " 435 "that cannot accept type record")));
440 (
errcode(ERRCODE_DATATYPE_MISMATCH),
441 errmsg(
"return type must be a row type")));
451 (
errcode(ERRCODE_SYNTAX_ERROR),
452 errmsg(
"return and sql tuple descriptions are " \
480 num_categories = tupdesc->natts - 1;
485 for (call_cntr = 0; call_cntr < max_calls; call_cntr++)
487 bool skip_tuple =
false;
491 values = (
char **)
palloc0((1 + num_categories) *
sizeof(
char *));
497 for (
i = 0;
i < num_categories;
i++)
503 if (call_cntr >= max_calls)
507 spi_tuple = spi_tuptable->vals[call_cntr];
524 if (!firstpass &&
xstreq(lastrowid, rowid))
536 if (
xstreq(rowid, values[0]))
552 if (
i < (num_categories - 1))
585 for (
i = 0;
i < num_categories + 1;
i++)
586 if (values[
i] != NULL)
652 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
653 errmsg(
"set-valued function called in context that cannot accept a set")));
657 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
658 errmsg(
"materialize mode required, but it is not allowed in this context")));
673 if (tupdesc->natts < 2)
675 (
errcode(ERRCODE_SYNTAX_ERROR),
676 errmsg(
"query-specified return tuple and " \
677 "crosstab function are not compatible")));
719 ctl.
hcxt = per_query_ctx;
733 elog(
ERROR,
"load_categories_hash: SPI_connect returned %d", ret);
750 if (spi_tupdesc->
natts != 1)
752 (
errcode(ERRCODE_SYNTAX_ERROR),
753 errmsg(
"provided \"categories\" SQL must " \
754 "return 1 column of at least one row")));
756 for (i = 0; i < proc; i++)
763 spi_tuple = spi_tuptable->
vals[
i];
769 (
errcode(ERRCODE_SYNTAX_ERROR),
770 errmsg(
"provided \"categories\" SQL must " \
771 "not return NULL values")));
788 elog(
ERROR,
"load_categories_hash: SPI_finish() failed");
816 elog(
ERROR,
"get_crosstab_tuplestore: SPI_connect returned %d", ret);
827 int ncols = spi_tupdesc->
natts;
829 char *lastrowid = NULL;
830 bool firstpass =
true;
835 if (num_categories == 0)
839 (
errcode(ERRCODE_SYNTAX_ERROR),
840 errmsg(
"provided \"categories\" SQL must " \
841 "return 1 column of at least one row")));
859 (
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
860 errmsg(
"invalid source data SQL statement"),
861 errdetail(
"The provided SQL must return 3 " \
862 " columns; rowid, category, and values.")));
864 result_ncols = (ncols - 2) + num_categories;
867 if (tupdesc->
natts != result_ncols)
869 (
errcode(ERRCODE_SYNTAX_ERROR),
870 errmsg(
"invalid return type"),
872 "tuple has %d columns but crosstab " \
873 "returns %d.", tupdesc->
natts, result_ncols)));
876 values = (
char **)
palloc0(result_ncols *
sizeof(
char *));
878 for (i = 0; i < proc; i++)
885 spi_tuple = spi_tuptable->
vals[
i];
894 if (firstpass || !
xstreq(lastrowid, rowid))
907 for (j = 0; j < result_ncols; j++)
912 for (j = 1; j < ncols - 2; j++)
913 values[j] =
SPI_getvalue(spi_tuple, spi_tupdesc, j + 1);
920 catname =
SPI_getvalue(spi_tuple, spi_tupdesc, ncols - 1);
927 values[catdesc->
attidx + ncols - 2] =
943 elog(
ERROR,
"get_crosstab_tuplestore: SPI_finish() failed");
986 #define CONNECTBY_NCOLS 4 987 #define CONNECTBY_NCOLS_NOBRANCH 3 997 char *branch_delim = NULL;
998 bool show_branch =
false;
999 bool show_serial =
false;
1009 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1010 errmsg(
"set-valued function called in context that cannot accept a set")));
1014 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1015 errmsg(
"materialize mode required, but it is not allowed in this context")));
1017 if (fcinfo->nargs == 6)
1076 char *branch_delim = NULL;
1077 bool show_branch =
false;
1078 bool show_serial =
true;
1088 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1089 errmsg(
"set-valued function called in context that cannot accept a set")));
1093 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1094 errmsg(
"materialize mode required, but it is not allowed in this context")));
1096 if (fcinfo->nargs == 7)
1152 char *parent_key_fld,
1172 elog(
ERROR,
"connectby: SPI_connect returned %d", ret);
1206 char *parent_key_fld,
1228 char *current_key_parent;
1231 char *current_branch;
1234 if (max_depth > 0 && level > max_depth)
1242 appendStringInfo(&sql,
"SELECT %s, %s FROM %s WHERE %s = %s AND %s IS NOT NULL AND %s <> %s",
1248 key_fld, key_fld, parent_key_fld);
1253 appendStringInfo(&sql,
"SELECT %s, %s FROM %s WHERE %s = %s AND %s IS NOT NULL AND %s <> %s ORDER BY %s",
1259 key_fld, key_fld, parent_key_fld,
1273 values[0] = start_with;
1279 sprintf(current_level,
"%d", level);
1280 values[2] = current_level;
1284 values[3] = start_with;
1289 sprintf(serial_str,
"%d", (*serial)++);
1291 values[4] = serial_str;
1293 values[3] = serial_str;
1331 for (i = 0; i < proc; i++)
1335 appendStringInfo(&chk_branchstr,
"%s%s%s", branch_delim, branch, branch_delim);
1338 spi_tuple = tuptable->
vals[
i];
1344 current_key_parent =
SPI_getvalue(spi_tuple, spi_tupdesc, 2);
1347 sprintf(current_level,
"%d", level);
1353 branch_delim, current_key, branch_delim);
1354 if (strstr(chk_branchstr.
data, chk_current_key.
data))
1356 (
errcode(ERRCODE_INVALID_RECURSION),
1357 errmsg(
"infinite recursion detected")));
1363 current_branch = branchstr.
data;
1366 values[0] = current_key;
1367 values[1] = current_key_parent;
1368 values[2] = current_level;
1370 values[3] = current_branch;
1373 sprintf(serial_str,
"%d", (*serial)++);
1375 values[4] = serial_str;
1377 values[3] = serial_str;
1406 xpfree(current_key_parent);
1426 int serial_column = 0;
1436 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1437 errmsg(
"invalid return type"),
1438 errdetail(
"Query-specified return tuple has " \
1439 "wrong number of columns.")));
1445 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1446 errmsg(
"invalid return type"),
1447 errdetail(
"Query-specified return tuple has " \
1448 "wrong number of columns.")));
1454 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1455 errmsg(
"invalid return type"),
1456 errdetail(
"First two columns must be the same type.")));
1461 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1462 errmsg(
"invalid return type"),
1463 errdetail(
"Third column must be type %s.",
1469 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1470 errmsg(
"invalid return type"),
1471 errdetail(
"Fourth column must be type %s.",
1475 if (show_branch && show_serial &&
1478 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1479 errmsg(
"query-specified return tuple not valid for Connectby: " 1480 "fifth column must be type %s",
1484 if (!show_branch && show_serial &&
1487 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1488 errmsg(
"query-specified return tuple not valid for Connectby: " 1489 "fourth column must be type %s",
1503 int32 ret_atttypmod;
1504 int32 sql_atttypmod;
1509 if (sql_tupdesc->
natts < 2)
1511 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1512 errmsg(
"invalid return type"),
1513 errdetail(
"Query must return at least two columns.")));
1523 if (ret_atttypid != sql_atttypid ||
1524 (ret_atttypmod >= 0 && ret_atttypmod != sql_atttypmod))
1526 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1527 errmsg(
"invalid return type"),
1528 errdetail(
"SQL key field type %s does " \
1529 "not match return key field type %s.",
1537 if (ret_atttypid != sql_atttypid ||
1538 (ret_atttypmod >= 0 && ret_atttypmod != sql_atttypmod))
1540 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1541 errmsg(
"invalid return type"),
1542 errdetail(
"SQL parent key field type %s does " \
1543 "not match return parent key field type %s.",
1562 if (ret_tupdesc->
natts < 2 ||
1563 sql_tupdesc->
natts < 3)
1569 if (ret_atttypid != sql_atttypid)
1571 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1572 errmsg(
"invalid return type"),
1573 errdetail(
"SQL rowid datatype does not match " \
1574 "return rowid datatype.")));
1582 for (i = 1; i < ret_tupdesc->
natts; i++)
1586 if (ret_attr->atttypid != sql_attr->atttypid)
#define PG_GETARG_FLOAT8(n)
Datum crosstab(PG_FUNCTION_ARGS)
PG_FUNCTION_INFO_V1(normal_rand)
#define PG_GETARG_INT32(n)
TupleDesc CreateTupleDescCopy(TupleDesc tupdesc)
#define IsA(nodeptr, _type_)
TypeFuncClass get_call_result_type(FunctionCallInfo fcinfo, Oid *resultTypeId, TupleDesc *resultTupleDesc)
static void build_tuplestore_recursively(char *key_fld, char *parent_key_fld, char *relname, char *orderby_fld, char *branch_delim, char *start_with, char *branch, int level, int *serial, int max_depth, bool show_branch, bool show_serial, MemoryContext per_query_ctx, AttInMetadata *attinmeta, Tuplestorestate *tupstore)
char * quote_literal_cstr(const char *rawstr)
#define CONNECTBY_NCOLS_NOBRANCH
static void validateConnectbyTupleDesc(TupleDesc tupdesc, bool show_branch, bool show_serial)
#define SRF_IS_FIRSTCALL()
#define TupleDescAttr(tupdesc, i)
char * pstrdup(const char *in)
static HTAB * load_categories_hash(char *cats_sql, MemoryContext per_query_ctx)
Datum connectby_text_serial(PG_FUNCTION_ARGS)
#define tuplestore_donestoring(state)
static MemoryContext MemoryContextSwitchTo(MemoryContext context)
SPITupleTable * SPI_tuptable
int errcode(int sqlerrcode)
long hash_get_num_entries(HTAB *hashp)
void heap_freetuple(HeapTuple htup)
Datum normal_rand(PG_FUNCTION_ARGS)
#define SRF_PERCALL_SETUP()
Datum Float8GetDatum(float8 X)
HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values)
#define PG_GETARG_TEXT_PP(n)
struct crosstab_cat_desc crosstab_cat_desc
static void compatConnectbyTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
char * SPI_getvalue(HeapTuple tuple, TupleDesc tupdesc, int fnumber)
#define SRF_RETURN_NEXT(_funcctx, _result)
static void get_normal_pair(float8 *x1, float8 *x2)
void pfree(void *pointer)
void appendStringInfo(StringInfo str, const char *fmt,...)
static Tuplestorestate * connectby(char *relname, char *key_fld, char *parent_key_fld, char *orderby_fld, char *branch_delim, char *start_with, int max_depth, bool show_branch, bool show_serial, MemoryContext per_query_ctx, bool randomAccess, AttInMetadata *attinmeta)
void tuplestore_puttuple(Tuplestorestate *state, HeapTuple tuple)
void appendStringInfoString(StringInfo str, const char *s)
#define xstreq(tgtvar_, srcvar_)
int errdetail(const char *fmt,...)
HTAB * hash_create(const char *tabname, long nelem, const HASHCTL *info, int flags)
FormData_pg_attribute * Form_pg_attribute
void resetStringInfo(StringInfo str)
struct crosstab_hashent crosstab_HashEnt
#define crosstab_HashTableLookup(HASHTAB, CATNAME, CATDESC)
void initStringInfo(StringInfo str)
Tuplestorestate * tuplestore_begin_heap(bool randomAccess, bool interXact, int maxKBytes)
void * palloc0(Size size)
AttInMetadata * TupleDescGetAttInMetadata(TupleDesc tupdesc)
#define ereport(elevel,...)
Datum crosstab_hash(PG_FUNCTION_ARGS)
SetFunctionReturnMode returnMode
static bool compatCrosstabTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
MemoryContext multi_call_memory_ctx
crosstab_cat_desc * catdesc
MemoryContext ecxt_per_query_memory
Tuplestorestate * setResult
static Datum values[MAXATTR]
char * text_to_cstring(const text *t)
static Tuplestorestate * get_crosstab_tuplestore(char *sql, HTAB *crosstab_hash, TupleDesc tupdesc, bool randomAccess)
int errmsg(const char *fmt,...)
Datum connectby_text(PG_FUNCTION_ARGS)
#define crosstab_HashTableInsert(HASHTAB, CATDESC)
#define xpstrdup(tgtvar_, srcvar_)
int SPI_execute(const char *src, bool read_only, long tcount)
#define SRF_RETURN_DONE(_funcctx)
#define SRF_FIRSTCALL_INIT()