94 #define xpfree(var_) \
103 #define xpstrdup(tgtvar_, srcvar_) \
106 tgtvar_ = pstrdup(srcvar_); \
111 #define xstreq(tgtvar_, srcvar_) \
112 (((tgtvar_ == NULL) && (srcvar_ == NULL)) || \
113 ((tgtvar_ != NULL) && (srcvar_ != NULL) && (strcmp(tgtvar_, srcvar_) == 0)))
116 #define INT32_STRLEN 12
125 #define MAX_CATNAME_LEN NAMEDATALEN
128 #define crosstab_HashTableLookup(HASHTAB, CATNAME, CATDESC) \
130 crosstab_HashEnt *hentry; char key[MAX_CATNAME_LEN]; \
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); \
137 CATDESC = hentry->catdesc; \
142 #define crosstab_HashTableInsert(HASHTAB, CATDESC) \
144 crosstab_HashEnt *hentry; bool found; char key[MAX_CATNAME_LEN]; \
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); \
152 (errcode(ERRCODE_DUPLICATE_OBJECT), \
153 errmsg("duplicate category name"))); \
154 hentry->catdesc = CATDESC; \
202 (
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
203 errmsg(
"number of rows cannot be negative")));
236 if (call_cntr < max_calls)
257 result = mean + (stddev * normval_1);
260 fctx->
carry_val = mean + (stddev * normval_2);
297 v1 = (2.0 * u1) - 1.0;
298 v2 = (2.0 * u2) - 1.0;
300 s = v1 * v1 + v2 * v2;
310 s = sqrt((-2.0 * log(s)) / s);
378 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
379 errmsg(
"set-valued function called in context that cannot accept a set")));
382 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
383 errmsg(
"materialize mode required, but it is not allowed in this context")));
390 elog(
ERROR,
"crosstab: SPI_connect returned %d", ret);
405 spi_tupdesc = spi_tuptable->
tupdesc;
418 if (spi_tupdesc->natts != 3)
420 (
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
421 errmsg(
"invalid crosstab source data query"),
422 errdetail(
"The query must return 3 columns: row_name, category, and value.")));
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")));
476 num_categories = tupdesc->natts - 1;
481 for (call_cntr = 0; call_cntr < max_calls; call_cntr++)
483 bool skip_tuple =
false;
487 values = (
char **)
palloc0((1 + num_categories) *
sizeof(
char *));
493 for (
i = 0;
i < num_categories;
i++)
499 if (call_cntr >= max_calls)
503 spi_tuple = spi_tuptable->vals[call_cntr];
520 if (!firstpass &&
xstreq(lastrowid, rowid))
548 if (
i < (num_categories - 1))
581 for (
i = 0;
i < num_categories + 1;
i++)
648 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
649 errmsg(
"set-valued function called in context that cannot accept a set")));
653 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
654 errmsg(
"materialize mode required, but it is not allowed in this context")));
669 if (tupdesc->natts < 2)
671 (
errcode(ERRCODE_DATATYPE_MISMATCH),
672 errmsg(
"invalid crosstab return type"),
673 errdetail(
"Return row must have at least two columns.")));
715 ctl.hcxt = per_query_ctx;
729 elog(
ERROR,
"load_categories_hash: SPI_connect returned %d", ret);
746 if (spi_tupdesc->
natts != 1)
748 (
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
749 errmsg(
"invalid crosstab categories query"),
750 errdetail(
"The query must return one column.")));
752 for (
i = 0;
i < proc;
i++)
759 spi_tuple = spi_tuptable->
vals[
i];
765 (
errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
766 errmsg(
"crosstab category value must not be null")));
783 elog(
ERROR,
"load_categories_hash: SPI_finish() failed");
811 elog(
ERROR,
"get_crosstab_tuplestore: SPI_connect returned %d", ret);
822 int ncols = spi_tupdesc->
natts;
824 char *lastrowid = NULL;
825 bool firstpass =
true;
830 if (num_categories == 0)
834 (
errcode(ERRCODE_CARDINALITY_VIOLATION),
835 errmsg(
"crosstab categories query must return at least one row")));
853 (
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
854 errmsg(
"invalid crosstab source data query"),
855 errdetail(
"The query must return at least 3 columns: row_name, category, and value.")));
857 result_ncols = (ncols - 2) + num_categories;
860 if (tupdesc->
natts != result_ncols)
862 (
errcode(ERRCODE_DATATYPE_MISMATCH),
863 errmsg(
"invalid crosstab return type"),
864 errdetail(
"Return row must have %d columns, not %d.",
865 result_ncols, tupdesc->
natts)));
870 for (
i = 0;
i < proc;
i++)
877 spi_tuple = spi_tuptable->
vals[
i];
886 if (firstpass || !
xstreq(lastrowid, rowid))
899 for (
j = 0;
j < result_ncols;
j++)
904 for (
j = 1;
j < ncols - 2;
j++)
912 catname =
SPI_getvalue(spi_tuple, spi_tupdesc, ncols - 1);
935 elog(
ERROR,
"get_crosstab_tuplestore: SPI_finish() failed");
976 #define CONNECTBY_NCOLS 4
977 #define CONNECTBY_NCOLS_NOBRANCH 3
987 char *branch_delim = NULL;
988 bool show_branch =
false;
989 bool show_serial =
false;
999 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1000 errmsg(
"set-valued function called in context that cannot accept a set")));
1004 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1005 errmsg(
"materialize mode required, but it is not allowed in this context")));
1007 if (fcinfo->nargs == 6)
1066 char *branch_delim = NULL;
1067 bool show_branch =
false;
1068 bool show_serial =
true;
1078 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1079 errmsg(
"set-valued function called in context that cannot accept a set")));
1083 (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1084 errmsg(
"materialize mode required, but it is not allowed in this context")));
1086 if (fcinfo->nargs == 7)
1142 char *parent_key_fld,
1162 elog(
ERROR,
"connectby: SPI_connect returned %d", ret);
1196 char *parent_key_fld,
1218 char *current_key_parent;
1221 char *current_branch;
1224 if (max_depth > 0 && level > max_depth)
1232 appendStringInfo(&sql,
"SELECT %s, %s FROM %s WHERE %s = %s AND %s IS NOT NULL AND %s <> %s",
1238 key_fld, key_fld, parent_key_fld);
1243 appendStringInfo(&sql,
"SELECT %s, %s FROM %s WHERE %s = %s AND %s IS NOT NULL AND %s <> %s ORDER BY %s",
1249 key_fld, key_fld, parent_key_fld,
1269 sprintf(current_level,
"%d", level);
1270 values[2] = current_level;
1279 sprintf(serial_str,
"%d", (*serial)++);
1321 for (
i = 0;
i < proc;
i++)
1325 appendStringInfo(&chk_branchstr,
"%s%s%s", branch_delim, branch, branch_delim);
1328 spi_tuple = tuptable->
vals[
i];
1334 current_key_parent =
SPI_getvalue(spi_tuple, spi_tupdesc, 2);
1337 sprintf(current_level,
"%d", level);
1343 branch_delim, current_key, branch_delim);
1344 if (strstr(chk_branchstr.
data, chk_current_key.
data))
1346 (
errcode(ERRCODE_INVALID_RECURSION),
1347 errmsg(
"infinite recursion detected")));
1353 current_branch = branchstr.
data;
1357 values[1] = current_key_parent;
1358 values[2] = current_level;
1360 values[3] = current_branch;
1363 sprintf(serial_str,
"%d", (*serial)++);
1396 xpfree(current_key_parent);
1426 if (td->
natts != expected_cols)
1428 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1429 errmsg(
"invalid connectby return type"),
1430 errdetail(
"Return row must have %d columns, not %d.",
1431 expected_cols, td->
natts)));
1438 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1439 errmsg(
"invalid connectby return type"),
1440 errdetail(
"Third return column (depth) must be type %s.",
1444 if (show_branch &&
TupleDescAttr(td, 3)->atttypid != TEXTOID)
1446 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1447 errmsg(
"invalid connectby return type"),
1448 errdetail(
"Fourth return column (branch) must be type %s.",
1452 if (show_branch && show_serial &&
1455 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1456 errmsg(
"invalid connectby return type"),
1457 errdetail(
"Fifth return column (serial) must be type %s.",
1459 if (!show_branch && show_serial &&
1462 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1463 errmsg(
"invalid connectby return type"),
1464 errdetail(
"Fourth return column (serial) must be type %s.",
1478 int32 ret_atttypmod;
1479 int32 sql_atttypmod;
1484 if (sql_tupdesc->
natts < 2)
1486 (
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1487 errmsg(
"invalid connectby source data query"),
1488 errdetail(
"The query must return at least two columns.")));
1498 if (ret_atttypid != sql_atttypid ||
1499 (ret_atttypmod >= 0 && ret_atttypmod != sql_atttypmod))
1501 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1502 errmsg(
"invalid connectby return type"),
1503 errdetail(
"Source key type %s does not match return key type %s.",
1511 if (ret_atttypid != sql_atttypid ||
1512 (ret_atttypmod >= 0 && ret_atttypmod != sql_atttypmod))
1514 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1515 errmsg(
"invalid connectby return type"),
1516 errdetail(
"Source parent key type %s does not match return parent key type %s.",
1532 int32 ret_atttypmod;
1533 int32 sql_atttypmod;
1535 if (ret_tupdesc->
natts < 2)
1537 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1538 errmsg(
"invalid crosstab return type"),
1539 errdetail(
"Return row must have at least two columns.")));
1547 if (ret_atttypid != sql_atttypid ||
1548 (ret_atttypmod >= 0 && ret_atttypmod != sql_atttypmod))
1550 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1551 errmsg(
"invalid crosstab return type"),
1552 errdetail(
"Source row_name datatype %s does not match return row_name datatype %s.",
1563 for (
i = 1;
i < ret_tupdesc->
natts;
i++)
1568 if (ret_atttypid != sql_atttypid ||
1569 (ret_atttypmod >= 0 && ret_atttypmod != sql_atttypmod))
1571 (
errcode(ERRCODE_DATATYPE_MISMATCH),
1572 errmsg(
"invalid crosstab return type"),
1573 errdetail(
"Source value datatype %s does not match return value datatype %s in column %d.",
static Datum values[MAXATTR]
#define Assert(condition)
HTAB * hash_create(const char *tabname, long nelem, const HASHCTL *info, int flags)
long hash_get_num_entries(HTAB *hashp)
int errdetail(const char *fmt,...)
int errcode(int sqlerrcode)
int errmsg(const char *fmt,...)
#define ereport(elevel,...)
HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values)
AttInMetadata * TupleDescGetAttInMetadata(TupleDesc tupdesc)
@ SFRM_Materialize_Random
Datum Float8GetDatum(float8 X)
#define PG_GETARG_TEXT_PP(n)
#define PG_GETARG_FLOAT8(n)
#define PG_GETARG_INT32(n)
TypeFuncClass get_call_result_type(FunctionCallInfo fcinfo, Oid *resultTypeId, TupleDesc *resultTupleDesc)
#define SRF_IS_FIRSTCALL()
#define SRF_PERCALL_SETUP()
#define SRF_RETURN_NEXT(_funcctx, _result)
#define SRF_FIRSTCALL_INIT()
#define SRF_RETURN_DONE(_funcctx)
void heap_freetuple(HeapTuple htup)
if(TABLE==NULL||TABLE_index==NULL)
char * pstrdup(const char *in)
void pfree(void *pointer)
void * palloc0(Size size)
#define IsA(nodeptr, _type_)
double pg_prng_double(pg_prng_state *state)
pg_prng_state pg_global_prng_state
char * quote_literal_cstr(const char *rawstr)
MemoryContextSwitchTo(old_ctx)
SPITupleTable * SPI_tuptable
char * SPI_getvalue(HeapTuple tuple, TupleDesc tupdesc, int fnumber)
int SPI_execute(const char *src, bool read_only, long tcount)
void resetStringInfo(StringInfo str)
void appendStringInfo(StringInfo str, const char *fmt,...)
void appendStringInfoString(StringInfo str, const char *s)
void initStringInfo(StringInfo str)
MemoryContext ecxt_per_query_memory
MemoryContext multi_call_memory_ctx
SetFunctionReturnMode returnMode
Tuplestorestate * setResult
crosstab_cat_desc * catdesc
char internal_catname[MAX_CATNAME_LEN]
#define crosstab_HashTableInsert(HASHTAB, CATDESC)
#define xpstrdup(tgtvar_, srcvar_)
static void compatCrosstabTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc)
struct crosstab_cat_desc crosstab_cat_desc
Datum connectby_text(PG_FUNCTION_ARGS)
Datum normal_rand(PG_FUNCTION_ARGS)
Datum crosstab(PG_FUNCTION_ARGS)
#define CONNECTBY_NCOLS_NOBRANCH
static void get_normal_pair(float8 *x1, float8 *x2)
#define crosstab_HashTableLookup(HASHTAB, CATNAME, CATDESC)
static Tuplestorestate * get_crosstab_tuplestore(char *sql, HTAB *crosstab_hash, TupleDesc tupdesc, bool randomAccess)
Datum connectby_text_serial(PG_FUNCTION_ARGS)
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)
Datum crosstab_hash(PG_FUNCTION_ARGS)
static void compatConnectbyTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc)
#define xstreq(tgtvar_, srcvar_)
static void validateConnectbyTupleDesc(TupleDesc td, bool show_branch, bool show_serial)
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)
PG_FUNCTION_INFO_V1(normal_rand)
struct crosstab_hashent crosstab_HashEnt
static HTAB * load_categories_hash(char *cats_sql, MemoryContext per_query_ctx)
TupleDesc CreateTupleDescCopy(TupleDesc tupdesc)
#define TupleDescAttr(tupdesc, i)
Tuplestorestate * tuplestore_begin_heap(bool randomAccess, bool interXact, int maxKBytes)
void tuplestore_puttuple(Tuplestorestate *state, HeapTuple tuple)
char * text_to_cstring(const text *t)