PostgreSQL Source Code git master
Data Structures | Macros | Typedefs | Functions
tablefunc.c File Reference
#include "postgres.h"
#include <math.h>
#include "access/htup_details.h"
#include "catalog/pg_type.h"
#include "common/pg_prng.h"
#include "executor/spi.h"
#include "fmgr.h"
#include "funcapi.h"
#include "lib/stringinfo.h"
#include "miscadmin.h"
#include "utils/builtins.h"
Include dependency graph for tablefunc.c:

Go to the source code of this file.

Data Structures

struct   normal_rand_fctx
 
struct   crosstab_cat_desc
 
struct   crosstab_hashent
 

Macros

#define  xpfree(var_)
 
#define  xpstrdup(tgtvar_, srcvar_)
 
#define  xstreq(tgtvar_, srcvar_)
 
#define  INT32_STRLEN   12
 
#define  MAX_CATNAME_LEN   NAMEDATALEN
 
#define  INIT_CATS   64
 
#define  crosstab_HashTableLookup(HASHTAB, CATNAME, CATDESC)
 
#define  crosstab_HashTableInsert(HASHTAB, CATDESC)
 
#define  CONNECTBY_NCOLS   4
 
#define  CONNECTBY_NCOLS_NOBRANCH   3
 

Typedefs

typedef struct crosstab_cat_desc  crosstab_cat_desc
 
typedef struct crosstab_hashent  crosstab_HashEnt
 

Functions

  PG_MODULE_MAGIC_EXT (.name="tablefunc",.version=PG_VERSION)
 
static HTAB *  load_categories_hash (char *cats_sql, MemoryContext per_query_ctx)
 
static Tuplestorestate *  get_crosstab_tuplestore (char *sql, HTAB *crosstab_hash, TupleDesc tupdesc, bool randomAccess)
 
static void  validateConnectbyTupleDesc (TupleDesc td, bool show_branch, bool show_serial)
 
static void  compatCrosstabTupleDescs (TupleDesc ret_tupdesc, TupleDesc sql_tupdesc)
 
static void  compatConnectbyTupleDescs (TupleDesc ret_tupdesc, TupleDesc sql_tupdesc)
 
static void  get_normal_pair (float8 *x1, float8 *x2)
 
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)
 
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)
 
  PG_FUNCTION_INFO_V1 (normal_rand)
 
Datum  normal_rand (PG_FUNCTION_ARGS)
 
  PG_FUNCTION_INFO_V1 (crosstab)
 
Datum  crosstab (PG_FUNCTION_ARGS)
 
  PG_FUNCTION_INFO_V1 (crosstab_hash)
 
Datum  crosstab_hash (PG_FUNCTION_ARGS)
 
  PG_FUNCTION_INFO_V1 (connectby_text)
 
Datum  connectby_text (PG_FUNCTION_ARGS)
 
  PG_FUNCTION_INFO_V1 (connectby_text_serial)
 
Datum  connectby_text_serial (PG_FUNCTION_ARGS)
 

Macro Definition Documentation

◆ CONNECTBY_NCOLS

#define CONNECTBY_NCOLS   4

Definition at line 973 of file tablefunc.c.

◆ CONNECTBY_NCOLS_NOBRANCH

#define CONNECTBY_NCOLS_NOBRANCH   3

Definition at line 974 of file tablefunc.c.

◆ crosstab_HashTableInsert

#define crosstab_HashTableInsert (   HASHTAB,
  CATDESC 
)
Value:
do { \
crosstab_HashEnt *hentry; bool found; char key[MAX_CATNAME_LEN]; \
snprintf(key, MAX_CATNAME_LEN - 1, "%s", CATDESC->catname); \
hentry = (crosstab_HashEnt*) hash_search(HASHTAB, \
key, HASH_ENTER, &found); \
if (found) \
ereport(ERROR, \
errmsg("duplicate category name"))); \
hentry->catdesc = CATDESC; \
} while(0)
#define MemSet(start, val, len)
Definition: c.h:1019
void * hash_search(HTAB *hashp, const void *keyPtr, HASHACTION action, bool *foundPtr)
Definition: dynahash.c:952
int errcode(int sqlerrcode)
Definition: elog.c:854
int errmsg(const char *fmt,...)
Definition: elog.c:1071
#define ERROR
Definition: elog.h:39
@ HASH_ENTER
Definition: hsearch.h:114
#define ERRCODE_DUPLICATE_OBJECT
Definition: streamutil.c:30
Definition: tablefunc.c:162
#define MAX_CATNAME_LEN
Definition: tablefunc.c:128

Definition at line 145 of file tablefunc.c.

◆ crosstab_HashTableLookup

#define crosstab_HashTableLookup (   HASHTAB,
  CATNAME,
  CATDESC 
)
Value:
do { \
crosstab_HashEnt *hentry; char key[MAX_CATNAME_LEN]; \
snprintf(key, MAX_CATNAME_LEN - 1, "%s", CATNAME); \
hentry = (crosstab_HashEnt*) hash_search(HASHTAB, \
key, HASH_FIND, NULL); \
if (hentry) \
CATDESC = hentry->catdesc; \
else \
CATDESC = NULL; \
} while(0)
@ HASH_FIND
Definition: hsearch.h:113

Definition at line 131 of file tablefunc.c.

◆ INIT_CATS

#define INIT_CATS   64

Definition at line 129 of file tablefunc.c.

◆ INT32_STRLEN

#define INT32_STRLEN   12

Definition at line 119 of file tablefunc.c.

◆ MAX_CATNAME_LEN

#define MAX_CATNAME_LEN   NAMEDATALEN

Definition at line 128 of file tablefunc.c.

◆ xpfree

#define xpfree (   var_ )
Value:
do { \
if (var_ != NULL) \
{ \
pfree(var_); \
var_ = NULL; \
} \
} while (0)

Definition at line 97 of file tablefunc.c.

◆ xpstrdup

#define xpstrdup (   tgtvar_,
  srcvar_ 
)
Value:
do { \
if (srcvar_) \
tgtvar_ = pstrdup(srcvar_); \
else \
tgtvar_ = NULL; \
} while (0)
char * pstrdup(const char *in)
Definition: mcxt.c:1759

Definition at line 106 of file tablefunc.c.

◆ xstreq

#define xstreq (   tgtvar_,
  srcvar_ 
)
Value:
(((tgtvar_ == NULL) && (srcvar_ == NULL)) || \
((tgtvar_ != NULL) && (srcvar_ != NULL) && (strcmp(tgtvar_, srcvar_) == 0)))

Definition at line 114 of file tablefunc.c.

Typedef Documentation

◆ crosstab_cat_desc

typedef struct crosstab_cat_desc crosstab_cat_desc

◆ crosstab_HashEnt

typedef struct crosstab_hashent crosstab_HashEnt

Function Documentation

◆ build_tuplestore_recursively()

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 
)
static

Definition at line 1188 of file tablefunc.c.

1203{
1204 TupleDesc tupdesc = attinmeta->tupdesc;
1205 int ret;
1206 uint64 proc;
1207 int serial_column;
1208 StringInfoData sql;
1209 char **values;
1210 char *current_key;
1211 char *current_key_parent;
1212 char current_level[INT32_STRLEN];
1213 char serial_str[INT32_STRLEN];
1214 char *current_branch;
1215 HeapTuple tuple;
1216
1217 if (max_depth > 0 && level > max_depth)
1218 return;
1219
1220 initStringInfo(&sql);
1221
1222 /* Build initial sql statement */
1223 if (!show_serial)
1224 {
1225 appendStringInfo(&sql, "SELECT %s, %s FROM %s WHERE %s = %s AND %s IS NOT NULL AND %s <> %s",
1226 key_fld,
1227 parent_key_fld,
1228 relname,
1229 parent_key_fld,
1230 quote_literal_cstr(start_with),
1231 key_fld, key_fld, parent_key_fld);
1232 serial_column = 0;
1233 }
1234 else
1235 {
1236 appendStringInfo(&sql, "SELECT %s, %s FROM %s WHERE %s = %s AND %s IS NOT NULL AND %s <> %s ORDER BY %s",
1237 key_fld,
1238 parent_key_fld,
1239 relname,
1240 parent_key_fld,
1241 quote_literal_cstr(start_with),
1242 key_fld, key_fld, parent_key_fld,
1243 orderby_fld);
1244 serial_column = 1;
1245 }
1246
1247 if (show_branch)
1248 values = (char **) palloc((CONNECTBY_NCOLS + serial_column) * sizeof(char *));
1249 else
1250 values = (char **) palloc((CONNECTBY_NCOLS_NOBRANCH + serial_column) * sizeof(char *));
1251
1252 /* First time through, do a little setup */
1253 if (level == 0)
1254 {
1255 /* root value is the one we initially start with */
1256 values[0] = start_with;
1257
1258 /* root value has no parent */
1259 values[1] = NULL;
1260
1261 /* root level is 0 */
1262 sprintf(current_level, "%d", level);
1263 values[2] = current_level;
1264
1265 /* root branch is just starting root value */
1266 if (show_branch)
1267 values[3] = start_with;
1268
1269 /* root starts the serial with 1 */
1270 if (show_serial)
1271 {
1272 sprintf(serial_str, "%d", (*serial)++);
1273 if (show_branch)
1274 values[4] = serial_str;
1275 else
1276 values[3] = serial_str;
1277 }
1278
1279 /* construct the tuple */
1280 tuple = BuildTupleFromCStrings(attinmeta, values);
1281
1282 /* now store it */
1283 tuplestore_puttuple(tupstore, tuple);
1284
1285 /* increment level */
1286 level++;
1287 }
1288
1289 /* Retrieve the desired rows */
1290 ret = SPI_execute(sql.data, true, 0);
1291 proc = SPI_processed;
1292
1293 /* Check for qualifying tuples */
1294 if ((ret == SPI_OK_SELECT) && (proc > 0))
1295 {
1296 HeapTuple spi_tuple;
1297 SPITupleTable *tuptable = SPI_tuptable;
1298 TupleDesc spi_tupdesc = tuptable->tupdesc;
1299 uint64 i;
1300 StringInfoData branchstr;
1301 StringInfoData chk_branchstr;
1302 StringInfoData chk_current_key;
1303
1304 /*
1305 * Check that return tupdesc is compatible with the one we got from
1306 * the query.
1307 */
1308 compatConnectbyTupleDescs(tupdesc, spi_tupdesc);
1309
1310 initStringInfo(&branchstr);
1311 initStringInfo(&chk_branchstr);
1312 initStringInfo(&chk_current_key);
1313
1314 for (i = 0; i < proc; i++)
1315 {
1316 /* initialize branch for this pass */
1317 appendStringInfoString(&branchstr, branch);
1318 appendStringInfo(&chk_branchstr, "%s%s%s", branch_delim, branch, branch_delim);
1319
1320 /* get the next sql result tuple */
1321 spi_tuple = tuptable->vals[i];
1322
1323 /* get the current key (might be NULL) */
1324 current_key = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
1325
1326 /* get the parent key (might be NULL) */
1327 current_key_parent = SPI_getvalue(spi_tuple, spi_tupdesc, 2);
1328
1329 /* get the current level */
1330 sprintf(current_level, "%d", level);
1331
1332 /* check to see if this key is also an ancestor */
1333 if (current_key)
1334 {
1335 appendStringInfo(&chk_current_key, "%s%s%s",
1336 branch_delim, current_key, branch_delim);
1337 if (strstr(chk_branchstr.data, chk_current_key.data))
1338 ereport(ERROR,
1339 (errcode(ERRCODE_INVALID_RECURSION),
1340 errmsg("infinite recursion detected")));
1341 }
1342
1343 /* OK, extend the branch */
1344 if (current_key)
1345 appendStringInfo(&branchstr, "%s%s", branch_delim, current_key);
1346 current_branch = branchstr.data;
1347
1348 /* build a tuple */
1349 values[0] = current_key;
1350 values[1] = current_key_parent;
1351 values[2] = current_level;
1352 if (show_branch)
1353 values[3] = current_branch;
1354 if (show_serial)
1355 {
1356 sprintf(serial_str, "%d", (*serial)++);
1357 if (show_branch)
1358 values[4] = serial_str;
1359 else
1360 values[3] = serial_str;
1361 }
1362
1363 tuple = BuildTupleFromCStrings(attinmeta, values);
1364
1365 /* store the tuple for later use */
1366 tuplestore_puttuple(tupstore, tuple);
1367
1368 heap_freetuple(tuple);
1369
1370 /* recurse using current_key as the new start_with */
1371 if (current_key)
1373 parent_key_fld,
1374 relname,
1375 orderby_fld,
1376 branch_delim,
1377 current_key,
1378 current_branch,
1379 level + 1,
1380 serial,
1381 max_depth,
1382 show_branch,
1383 show_serial,
1384 per_query_ctx,
1385 attinmeta,
1386 tupstore);
1387
1388 xpfree(current_key);
1389 xpfree(current_key_parent);
1390
1391 /* reset branch for next pass */
1392 resetStringInfo(&branchstr);
1393 resetStringInfo(&chk_branchstr);
1394 resetStringInfo(&chk_current_key);
1395 }
1396
1397 xpfree(branchstr.data);
1398 xpfree(chk_branchstr.data);
1399 xpfree(chk_current_key.data);
1400 }
1401}
static Datum values[MAXATTR]
Definition: bootstrap.c:153
uint64_t uint64
Definition: c.h:539
#define ereport(elevel,...)
Definition: elog.h:150
HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values)
Definition: execTuples.c:2324
void heap_freetuple(HeapTuple htup)
Definition: heaptuple.c:1435
i
int i
Definition: isn.c:77
void * palloc(Size size)
Definition: mcxt.c:1365
NameData relname
Definition: pg_class.h:38
#define sprintf
Definition: port.h:241
char * quote_literal_cstr(const char *rawstr)
Definition: quote.c:103
uint64 SPI_processed
Definition: spi.c:44
SPITupleTable * SPI_tuptable
Definition: spi.c:45
char * SPI_getvalue(HeapTuple tuple, TupleDesc tupdesc, int fnumber)
Definition: spi.c:1220
int SPI_execute(const char *src, bool read_only, long tcount)
Definition: spi.c:596
#define SPI_OK_SELECT
Definition: spi.h:86
void resetStringInfo(StringInfo str)
Definition: stringinfo.c:126
void appendStringInfo(StringInfo str, const char *fmt,...)
Definition: stringinfo.c:145
void appendStringInfoString(StringInfo str, const char *s)
Definition: stringinfo.c:230
void initStringInfo(StringInfo str)
Definition: stringinfo.c:97
TupleDesc tupdesc
Definition: funcapi.h:38
Definition: spi.h:23
TupleDesc tupdesc
Definition: spi.h:25
HeapTuple * vals
Definition: spi.h:26
char * data
Definition: stringinfo.h:48
#define CONNECTBY_NCOLS_NOBRANCH
Definition: tablefunc.c:974
#define INT32_STRLEN
Definition: tablefunc.c:119
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)
Definition: tablefunc.c:1188
#define CONNECTBY_NCOLS
Definition: tablefunc.c:973
static void compatConnectbyTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc)
Definition: tablefunc.c:1467
#define xpfree(var_)
Definition: tablefunc.c:97
void tuplestore_puttuple(Tuplestorestate *state, HeapTuple tuple)
Definition: tuplestore.c:764

References appendStringInfo(), appendStringInfoString(), build_tuplestore_recursively(), BuildTupleFromCStrings(), compatConnectbyTupleDescs(), CONNECTBY_NCOLS, CONNECTBY_NCOLS_NOBRANCH, StringInfoData::data, ereport, errcode(), errmsg(), ERROR, heap_freetuple(), i, initStringInfo(), INT32_STRLEN, palloc(), quote_literal_cstr(), relname, resetStringInfo(), SPI_execute(), SPI_getvalue(), SPI_OK_SELECT, SPI_processed, SPI_tuptable, sprintf, SPITupleTable::tupdesc, AttInMetadata::tupdesc, tuplestore_puttuple(), SPITupleTable::vals, values, and xpfree.

Referenced by build_tuplestore_recursively(), and connectby().

◆ compatConnectbyTupleDescs()

static void compatConnectbyTupleDescs ( TupleDesc  ret_tupdesc,
TupleDesc  sql_tupdesc 
)
static

Definition at line 1467 of file tablefunc.c.

1468{
1469 Oid ret_atttypid;
1470 Oid sql_atttypid;
1471 int32 ret_atttypmod;
1472 int32 sql_atttypmod;
1473
1474 /*
1475 * Query result must have at least 2 columns.
1476 */
1477 if (sql_tupdesc->natts < 2)
1478 ereport(ERROR,
1479 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1480 errmsg("invalid connectby source data query"),
1481 errdetail("The query must return at least two columns.")));
1482
1483 /*
1484 * These columns must match the result type indicated by the calling
1485 * query.
1486 */
1487 ret_atttypid = TupleDescAttr(ret_tupdesc, 0)->atttypid;
1488 sql_atttypid = TupleDescAttr(sql_tupdesc, 0)->atttypid;
1489 ret_atttypmod = TupleDescAttr(ret_tupdesc, 0)->atttypmod;
1490 sql_atttypmod = TupleDescAttr(sql_tupdesc, 0)->atttypmod;
1491 if (ret_atttypid != sql_atttypid ||
1492 (ret_atttypmod >= 0 && ret_atttypmod != sql_atttypmod))
1493 ereport(ERROR,
1494 (errcode(ERRCODE_DATATYPE_MISMATCH),
1495 errmsg("invalid connectby return type"),
1496 errdetail("Source key type %s does not match return key type %s.",
1497 format_type_with_typemod(sql_atttypid, sql_atttypmod),
1498 format_type_with_typemod(ret_atttypid, ret_atttypmod))));
1499
1500 ret_atttypid = TupleDescAttr(ret_tupdesc, 1)->atttypid;
1501 sql_atttypid = TupleDescAttr(sql_tupdesc, 1)->atttypid;
1502 ret_atttypmod = TupleDescAttr(ret_tupdesc, 1)->atttypmod;
1503 sql_atttypmod = TupleDescAttr(sql_tupdesc, 1)->atttypmod;
1504 if (ret_atttypid != sql_atttypid ||
1505 (ret_atttypmod >= 0 && ret_atttypmod != sql_atttypmod))
1506 ereport(ERROR,
1507 (errcode(ERRCODE_DATATYPE_MISMATCH),
1508 errmsg("invalid connectby return type"),
1509 errdetail("Source parent key type %s does not match return parent key type %s.",
1510 format_type_with_typemod(sql_atttypid, sql_atttypmod),
1511 format_type_with_typemod(ret_atttypid, ret_atttypmod))));
1512
1513 /* OK, the two tupdescs are compatible for our purposes */
1514}
int32_t int32
Definition: c.h:534
int errdetail(const char *fmt,...)
Definition: elog.c:1207
char * format_type_with_typemod(Oid type_oid, int32 typemod)
Definition: format_type.c:362
unsigned int Oid
Definition: postgres_ext.h:32
int natts
Definition: tupdesc.h:137
static FormData_pg_attribute * TupleDescAttr(TupleDesc tupdesc, int i)
Definition: tupdesc.h:160

References ereport, errcode(), errdetail(), errmsg(), ERROR, format_type_with_typemod(), TupleDescData::natts, and TupleDescAttr().

Referenced by build_tuplestore_recursively().

◆ compatCrosstabTupleDescs()

static void compatCrosstabTupleDescs ( TupleDesc  ret_tupdesc,
TupleDesc  sql_tupdesc 
)
static

Definition at line 1520 of file tablefunc.c.

1521{
1522 int i;
1523 Oid ret_atttypid;
1524 Oid sql_atttypid;
1525 int32 ret_atttypmod;
1526 int32 sql_atttypmod;
1527
1528 if (ret_tupdesc->natts < 2)
1529 ereport(ERROR,
1530 (errcode(ERRCODE_DATATYPE_MISMATCH),
1531 errmsg("invalid crosstab return type"),
1532 errdetail("Return row must have at least two columns.")));
1533 Assert(sql_tupdesc->natts == 3); /* already checked by caller */
1534
1535 /* check the row_name types match */
1536 ret_atttypid = TupleDescAttr(ret_tupdesc, 0)->atttypid;
1537 sql_atttypid = TupleDescAttr(sql_tupdesc, 0)->atttypid;
1538 ret_atttypmod = TupleDescAttr(ret_tupdesc, 0)->atttypmod;
1539 sql_atttypmod = TupleDescAttr(sql_tupdesc, 0)->atttypmod;
1540 if (ret_atttypid != sql_atttypid ||
1541 (ret_atttypmod >= 0 && ret_atttypmod != sql_atttypmod))
1542 ereport(ERROR,
1543 (errcode(ERRCODE_DATATYPE_MISMATCH),
1544 errmsg("invalid crosstab return type"),
1545 errdetail("Source row_name datatype %s does not match return row_name datatype %s.",
1546 format_type_with_typemod(sql_atttypid, sql_atttypmod),
1547 format_type_with_typemod(ret_atttypid, ret_atttypmod))));
1548
1549 /*
1550 * attribute [1] of sql tuple is the category; no need to check it
1551 * attribute [2] of sql tuple should match attributes [1] to [natts - 1]
1552 * of the return tuple
1553 */
1554 sql_atttypid = TupleDescAttr(sql_tupdesc, 2)->atttypid;
1555 sql_atttypmod = TupleDescAttr(sql_tupdesc, 2)->atttypmod;
1556 for (i = 1; i < ret_tupdesc->natts; i++)
1557 {
1558 ret_atttypid = TupleDescAttr(ret_tupdesc, i)->atttypid;
1559 ret_atttypmod = TupleDescAttr(ret_tupdesc, i)->atttypmod;
1560
1561 if (ret_atttypid != sql_atttypid ||
1562 (ret_atttypmod >= 0 && ret_atttypmod != sql_atttypmod))
1563 ereport(ERROR,
1564 (errcode(ERRCODE_DATATYPE_MISMATCH),
1565 errmsg("invalid crosstab return type"),
1566 errdetail("Source value datatype %s does not match return value datatype %s in column %d.",
1567 format_type_with_typemod(sql_atttypid, sql_atttypmod),
1568 format_type_with_typemod(ret_atttypid, ret_atttypmod),
1569 i + 1)));
1570 }
1571
1572 /* OK, the two tupdescs are compatible for our purposes */
1573}
Assert(PointerIsAligned(start, uint64))

References Assert(), ereport, errcode(), errdetail(), errmsg(), ERROR, format_type_with_typemod(), i, TupleDescData::natts, and TupleDescAttr().

Referenced by crosstab().

◆ connectby()

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 
)
static

Definition at line 1137 of file tablefunc.c.

1149{
1150 Tuplestorestate *tupstore = NULL;
1151 MemoryContext oldcontext;
1152 int serial = 1;
1153
1154 /* Connect to SPI manager */
1155 SPI_connect();
1156
1157 /* switch to longer term context to create the tuple store */
1158 oldcontext = MemoryContextSwitchTo(per_query_ctx);
1159
1160 /* initialize our tuplestore */
1161 tupstore = tuplestore_begin_heap(randomAccess, false, work_mem);
1162
1163 MemoryContextSwitchTo(oldcontext);
1164
1165 /* now go get the whole tree */
1167 parent_key_fld,
1168 relname,
1169 orderby_fld,
1170 branch_delim,
1171 start_with,
1172 start_with, /* current_branch */
1173 0, /* initial level is 0 */
1174 &serial, /* initial serial is 1 */
1175 max_depth,
1176 show_branch,
1177 show_serial,
1178 per_query_ctx,
1179 attinmeta,
1180 tupstore);
1181
1182 SPI_finish();
1183
1184 return tupstore;
1185}
int work_mem
Definition: globals.c:131
static MemoryContext MemoryContextSwitchTo(MemoryContext context)
Definition: palloc.h:124
int SPI_connect(void)
Definition: spi.c:94
int SPI_finish(void)
Definition: spi.c:182
Tuplestorestate * tuplestore_begin_heap(bool randomAccess, bool interXact, int maxKBytes)
Definition: tuplestore.c:330

References build_tuplestore_recursively(), MemoryContextSwitchTo(), relname, SPI_connect(), SPI_finish(), tuplestore_begin_heap(), and work_mem.

Referenced by connectby_text(), and connectby_text_serial().

◆ connectby_text()

Datum connectby_text ( PG_FUNCTION_ARGS  )

Definition at line 977 of file tablefunc.c.

978{
980 char *key_fld = text_to_cstring(PG_GETARG_TEXT_PP(1));
981 char *parent_key_fld = text_to_cstring(PG_GETARG_TEXT_PP(2));
982 char *start_with = text_to_cstring(PG_GETARG_TEXT_PP(3));
983 int max_depth = PG_GETARG_INT32(4);
984 char *branch_delim = NULL;
985 bool show_branch = false;
986 bool show_serial = false;
987 ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
988 TupleDesc tupdesc;
989 AttInMetadata *attinmeta;
990 MemoryContext per_query_ctx;
991 MemoryContext oldcontext;
992
993 /* check to see if caller supports us returning a tuplestore */
994 if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
996 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
997 errmsg("set-valued function called in context that cannot accept a set")));
998 if (!(rsinfo->allowedModes & SFRM_Materialize) ||
999 rsinfo->expectedDesc == NULL)
1000 ereport(ERROR,
1001 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1002 errmsg("materialize mode required, but it is not allowed in this context")));
1003
1004 if (fcinfo->nargs == 6)
1005 {
1006 branch_delim = text_to_cstring(PG_GETARG_TEXT_PP(5));
1007 show_branch = true;
1008 }
1009 else
1010 /* default is no show, tilde for the delimiter */
1011 branch_delim = pstrdup("~");
1012
1013 per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
1014 oldcontext = MemoryContextSwitchTo(per_query_ctx);
1015
1016 /* get the requested return tuple description */
1017 tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
1018
1019 /* does it meet our needs */
1020 validateConnectbyTupleDesc(tupdesc, show_branch, show_serial);
1021
1022 /* OK, use it then */
1023 attinmeta = TupleDescGetAttInMetadata(tupdesc);
1024
1025 /* OK, go to work */
1026 rsinfo->returnMode = SFRM_Materialize;
1027 rsinfo->setResult = connectby(relname,
1028 key_fld,
1029 parent_key_fld,
1030 NULL,
1031 branch_delim,
1032 start_with,
1033 max_depth,
1034 show_branch,
1035 show_serial,
1036 per_query_ctx,
1038 attinmeta);
1039 rsinfo->setDesc = tupdesc;
1040
1041 MemoryContextSwitchTo(oldcontext);
1042
1043 /*
1044 * SFRM_Materialize mode expects us to return a NULL Datum. The actual
1045 * tuples are in our tuplestore and passed back through rsinfo->setResult.
1046 * rsinfo->setDesc is set to the tuple description that we actually used
1047 * to build our tuples with, so the caller can verify we did what it was
1048 * expecting.
1049 */
1050 return (Datum) 0;
1051}
AttInMetadata * TupleDescGetAttInMetadata(TupleDesc tupdesc)
Definition: execTuples.c:2275
@ SFRM_Materialize_Random
Definition: execnodes.h:342
@ SFRM_Materialize
Definition: execnodes.h:341
#define PG_GETARG_TEXT_PP(n)
Definition: fmgr.h:309
#define PG_GETARG_INT32(n)
Definition: fmgr.h:269
if(TABLE==NULL||TABLE_index==NULL)
Definition: isn.c:81
#define IsA(nodeptr, _type_)
Definition: nodes.h:164
uint64_t Datum
Definition: postgres.h:70
MemoryContext ecxt_per_query_memory
Definition: execnodes.h:280
SetFunctionReturnMode returnMode
Definition: execnodes.h:360
ExprContext * econtext
Definition: execnodes.h:356
TupleDesc setDesc
Definition: execnodes.h:364
Tuplestorestate * setResult
Definition: execnodes.h:363
TupleDesc expectedDesc
Definition: execnodes.h:357
int allowedModes
Definition: execnodes.h:358
static void validateConnectbyTupleDesc(TupleDesc td, bool show_branch, bool show_serial)
Definition: tablefunc.c:1407
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)
Definition: tablefunc.c:1137
TupleDesc CreateTupleDescCopy(TupleDesc tupdesc)
Definition: tupdesc.c:252
char * text_to_cstring(const text *t)
Definition: varlena.c:214

References ReturnSetInfo::allowedModes, connectby(), CreateTupleDescCopy(), ReturnSetInfo::econtext, ExprContext::ecxt_per_query_memory, ereport, errcode(), errmsg(), ERROR, ReturnSetInfo::expectedDesc, if(), IsA, MemoryContextSwitchTo(), PG_GETARG_INT32, PG_GETARG_TEXT_PP, pstrdup(), relname, ReturnSetInfo::returnMode, ReturnSetInfo::setDesc, ReturnSetInfo::setResult, SFRM_Materialize, SFRM_Materialize_Random, text_to_cstring(), TupleDescGetAttInMetadata(), and validateConnectbyTupleDesc().

◆ connectby_text_serial()

Datum connectby_text_serial ( PG_FUNCTION_ARGS  )

Definition at line 1055 of file tablefunc.c.

1056{
1058 char *key_fld = text_to_cstring(PG_GETARG_TEXT_PP(1));
1059 char *parent_key_fld = text_to_cstring(PG_GETARG_TEXT_PP(2));
1060 char *orderby_fld = text_to_cstring(PG_GETARG_TEXT_PP(3));
1061 char *start_with = text_to_cstring(PG_GETARG_TEXT_PP(4));
1062 int max_depth = PG_GETARG_INT32(5);
1063 char *branch_delim = NULL;
1064 bool show_branch = false;
1065 bool show_serial = true;
1066 ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
1067 TupleDesc tupdesc;
1068 AttInMetadata *attinmeta;
1069 MemoryContext per_query_ctx;
1070 MemoryContext oldcontext;
1071
1072 /* check to see if caller supports us returning a tuplestore */
1073 if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
1074 ereport(ERROR,
1075 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1076 errmsg("set-valued function called in context that cannot accept a set")));
1077 if (!(rsinfo->allowedModes & SFRM_Materialize) ||
1078 rsinfo->expectedDesc == NULL)
1079 ereport(ERROR,
1080 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1081 errmsg("materialize mode required, but it is not allowed in this context")));
1082
1083 if (fcinfo->nargs == 7)
1084 {
1085 branch_delim = text_to_cstring(PG_GETARG_TEXT_PP(6));
1086 show_branch = true;
1087 }
1088 else
1089 /* default is no show, tilde for the delimiter */
1090 branch_delim = pstrdup("~");
1091
1092 per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
1093 oldcontext = MemoryContextSwitchTo(per_query_ctx);
1094
1095 /* get the requested return tuple description */
1096 tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
1097
1098 /* does it meet our needs */
1099 validateConnectbyTupleDesc(tupdesc, show_branch, show_serial);
1100
1101 /* OK, use it then */
1102 attinmeta = TupleDescGetAttInMetadata(tupdesc);
1103
1104 /* OK, go to work */
1105 rsinfo->returnMode = SFRM_Materialize;
1106 rsinfo->setResult = connectby(relname,
1107 key_fld,
1108 parent_key_fld,
1109 orderby_fld,
1110 branch_delim,
1111 start_with,
1112 max_depth,
1113 show_branch,
1114 show_serial,
1115 per_query_ctx,
1117 attinmeta);
1118 rsinfo->setDesc = tupdesc;
1119
1120 MemoryContextSwitchTo(oldcontext);
1121
1122 /*
1123 * SFRM_Materialize mode expects us to return a NULL Datum. The actual
1124 * tuples are in our tuplestore and passed back through rsinfo->setResult.
1125 * rsinfo->setDesc is set to the tuple description that we actually used
1126 * to build our tuples with, so the caller can verify we did what it was
1127 * expecting.
1128 */
1129 return (Datum) 0;
1130}

References ReturnSetInfo::allowedModes, connectby(), CreateTupleDescCopy(), ReturnSetInfo::econtext, ExprContext::ecxt_per_query_memory, ereport, errcode(), errmsg(), ERROR, ReturnSetInfo::expectedDesc, if(), IsA, MemoryContextSwitchTo(), PG_GETARG_INT32, PG_GETARG_TEXT_PP, pstrdup(), relname, ReturnSetInfo::returnMode, ReturnSetInfo::setDesc, ReturnSetInfo::setResult, SFRM_Materialize, SFRM_Materialize_Random, text_to_cstring(), TupleDescGetAttInMetadata(), and validateConnectbyTupleDesc().

◆ crosstab()

Datum crosstab ( PG_FUNCTION_ARGS  )

Definition at line 358 of file tablefunc.c.

359{
360 char *sql = text_to_cstring(PG_GETARG_TEXT_PP(0));
361 ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
362 Tuplestorestate *tupstore;
363 TupleDesc tupdesc;
364 uint64 call_cntr;
365 uint64 max_calls;
366 AttInMetadata *attinmeta;
367 SPITupleTable *spi_tuptable;
368 TupleDesc spi_tupdesc;
369 bool firstpass;
370 char *lastrowid;
371 int i;
372 int num_categories;
373 MemoryContext per_query_ctx;
374 MemoryContext oldcontext;
375 int ret;
376 uint64 proc;
377
378 /* check to see if caller supports us returning a tuplestore */
379 if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
381 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
382 errmsg("set-valued function called in context that cannot accept a set")));
383 if (!(rsinfo->allowedModes & SFRM_Materialize))
385 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
386 errmsg("materialize mode required, but it is not allowed in this context")));
387
388 per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
389
390 /* Connect to SPI manager */
391 SPI_connect();
392
393 /* Retrieve the desired rows */
394 ret = SPI_execute(sql, true, 0);
395 proc = SPI_processed;
396
397 /* If no qualifying tuples, fall out early */
398 if (ret != SPI_OK_SELECT || proc == 0)
399 {
400 SPI_finish();
401 rsinfo->isDone = ExprEndResult;
403 }
404
405 spi_tuptable = SPI_tuptable;
406 spi_tupdesc = spi_tuptable->tupdesc;
407
408 /*----------
409 * The provided SQL query must always return three columns.
410 *
411 * 1. rowname
412 * the label or identifier for each row in the final result
413 * 2. category
414 * the label or identifier for each column in the final result
415 * 3. values
416 * the value for each column in the final result
417 *----------
418 */
419 if (spi_tupdesc->natts != 3)
421 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
422 errmsg("invalid crosstab source data query"),
423 errdetail("The query must return 3 columns: row_name, category, and value.")));
424
425 /* get a tuple descriptor for our result type */
426 switch (get_call_result_type(fcinfo, NULL, &tupdesc))
427 {
429 /* success */
430 break;
431 case TYPEFUNC_RECORD:
432 /* failed to determine actual type of RECORD */
434 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
435 errmsg("function returning record called in context "
436 "that cannot accept type record")));
437 break;
438 default:
439 /* result type isn't composite */
441 (errcode(ERRCODE_DATATYPE_MISMATCH),
442 errmsg("return type must be a row type")));
443 break;
444 }
445
446 /*
447 * Check that return tupdesc is compatible with the data we got from SPI,
448 * at least based on number and type of attributes
449 */
450 compatCrosstabTupleDescs(tupdesc, spi_tupdesc);
451
452 /*
453 * switch to long-lived memory context
454 */
455 oldcontext = MemoryContextSwitchTo(per_query_ctx);
456
457 /* make sure we have a persistent copy of the result tupdesc */
458 tupdesc = CreateTupleDescCopy(tupdesc);
459
460 /* initialize our tuplestore in long-lived context */
461 tupstore =
463 false, work_mem);
464
465 MemoryContextSwitchTo(oldcontext);
466
467 /*
468 * Generate attribute metadata needed later to produce tuples from raw C
469 * strings
470 */
471 attinmeta = TupleDescGetAttInMetadata(tupdesc);
472
473 /* total number of tuples to be examined */
474 max_calls = proc;
475
476 /* the return tuple always must have 1 rowid + num_categories columns */
477 num_categories = tupdesc->natts - 1;
478
479 firstpass = true;
480 lastrowid = NULL;
481
482 for (call_cntr = 0; call_cntr < max_calls; call_cntr++)
483 {
484 bool skip_tuple = false;
485 char **values;
486
487 /* allocate and zero space */
488 values = (char **) palloc0((1 + num_categories) * sizeof(char *));
489
490 /*
491 * now loop through the sql results and assign each value in sequence
492 * to the next category
493 */
494 for (i = 0; i < num_categories; i++)
495 {
496 HeapTuple spi_tuple;
497 char *rowid;
498
499 /* see if we've gone too far already */
500 if (call_cntr >= max_calls)
501 break;
502
503 /* get the next sql result tuple */
504 spi_tuple = spi_tuptable->vals[call_cntr];
505
506 /* get the rowid from the current sql result tuple */
507 rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
508
509 /*
510 * If this is the first pass through the values for this rowid,
511 * set the first column to rowid
512 */
513 if (i == 0)
514 {
515 xpstrdup(values[0], rowid);
516
517 /*
518 * Check to see if the rowid is the same as that of the last
519 * tuple sent -- if so, skip this tuple entirely
520 */
521 if (!firstpass && xstreq(lastrowid, rowid))
522 {
523 xpfree(rowid);
524 skip_tuple = true;
525 break;
526 }
527 }
528
529 /*
530 * If rowid hasn't changed on us, continue building the output
531 * tuple.
532 */
533 if (xstreq(rowid, values[0]))
534 {
535 /*
536 * Get the next category item value, which is always attribute
537 * number three.
538 *
539 * Be careful to assign the value to the array index based on
540 * which category we are presently processing.
541 */
542 values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3);
543
544 /*
545 * increment the counter since we consume a row for each
546 * category, but not for last pass because the outer loop will
547 * do that for us
548 */
549 if (i < (num_categories - 1))
550 call_cntr++;
551 xpfree(rowid);
552 }
553 else
554 {
555 /*
556 * We'll fill in NULLs for the missing values, but we need to
557 * decrement the counter since this sql result row doesn't
558 * belong to the current output tuple.
559 */
560 call_cntr--;
561 xpfree(rowid);
562 break;
563 }
564 }
565
566 if (!skip_tuple)
567 {
568 HeapTuple tuple;
569
570 /* build the tuple and store it */
571 tuple = BuildTupleFromCStrings(attinmeta, values);
572 tuplestore_puttuple(tupstore, tuple);
573 heap_freetuple(tuple);
574 }
575
576 /* Remember current rowid */
577 xpfree(lastrowid);
578 xpstrdup(lastrowid, values[0]);
579 firstpass = false;
580
581 /* Clean up */
582 for (i = 0; i < num_categories + 1; i++)
583 if (values[i] != NULL)
584 pfree(values[i]);
585 pfree(values);
586 }
587
588 /* let the caller know we're sending back a tuplestore */
590 rsinfo->setResult = tupstore;
591 rsinfo->setDesc = tupdesc;
592
593 /* release SPI related resources (and return to caller's context) */
594 SPI_finish();
595
596 return (Datum) 0;
597}
@ ExprEndResult
Definition: execnodes.h:329
#define PG_RETURN_NULL()
Definition: fmgr.h:345
TypeFuncClass get_call_result_type(FunctionCallInfo fcinfo, Oid *resultTypeId, TupleDesc *resultTupleDesc)
Definition: funcapi.c:276
@ TYPEFUNC_COMPOSITE
Definition: funcapi.h:149
@ TYPEFUNC_RECORD
Definition: funcapi.h:151
void pfree(void *pointer)
Definition: mcxt.c:1594
void * palloc0(Size size)
Definition: mcxt.c:1395
ExprDoneCond isDone
Definition: execnodes.h:361
#define xpstrdup(tgtvar_, srcvar_)
Definition: tablefunc.c:106
static void compatCrosstabTupleDescs(TupleDesc ret_tupdesc, TupleDesc sql_tupdesc)
Definition: tablefunc.c:1520
#define xstreq(tgtvar_, srcvar_)
Definition: tablefunc.c:114

References ReturnSetInfo::allowedModes, BuildTupleFromCStrings(), compatCrosstabTupleDescs(), CreateTupleDescCopy(), ReturnSetInfo::econtext, ExprContext::ecxt_per_query_memory, ereport, errcode(), errdetail(), errmsg(), ERROR, ExprEndResult, get_call_result_type(), heap_freetuple(), i, if(), IsA, ReturnSetInfo::isDone, MemoryContextSwitchTo(), palloc0(), pfree(), PG_GETARG_TEXT_PP, PG_RETURN_NULL, ReturnSetInfo::returnMode, ReturnSetInfo::setDesc, ReturnSetInfo::setResult, SFRM_Materialize, SFRM_Materialize_Random, SPI_connect(), SPI_execute(), SPI_finish(), SPI_getvalue(), SPI_OK_SELECT, SPI_processed, SPI_tuptable, text_to_cstring(), SPITupleTable::tupdesc, TupleDescGetAttInMetadata(), tuplestore_begin_heap(), tuplestore_puttuple(), TYPEFUNC_COMPOSITE, TYPEFUNC_RECORD, values, work_mem, xpfree, xpstrdup, and xstreq.

◆ crosstab_hash()

Datum crosstab_hash ( PG_FUNCTION_ARGS  )

Definition at line 636 of file tablefunc.c.

637{
638 char *sql = text_to_cstring(PG_GETARG_TEXT_PP(0));
639 char *cats_sql = text_to_cstring(PG_GETARG_TEXT_PP(1));
640 ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
641 TupleDesc tupdesc;
642 MemoryContext per_query_ctx;
643 MemoryContext oldcontext;
645
646 /* check to see if caller supports us returning a tuplestore */
647 if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
649 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
650 errmsg("set-valued function called in context that cannot accept a set")));
651 if (!(rsinfo->allowedModes & SFRM_Materialize) ||
652 rsinfo->expectedDesc == NULL)
654 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
655 errmsg("materialize mode required, but it is not allowed in this context")));
656
657 per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
658 oldcontext = MemoryContextSwitchTo(per_query_ctx);
659
660 /* get the requested return tuple description */
661 tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc);
662
663 /*
664 * Check to make sure we have a reasonable tuple descriptor
665 *
666 * Note we will attempt to coerce the values into whatever the return
667 * attribute type is and depend on the "in" function to complain if
668 * needed.
669 */
670 if (tupdesc->natts < 2)
672 (errcode(ERRCODE_DATATYPE_MISMATCH),
673 errmsg("invalid crosstab return type"),
674 errdetail("Return row must have at least two columns.")));
675
676 /* load up the categories hash table */
677 crosstab_hash = load_categories_hash(cats_sql, per_query_ctx);
678
679 /* let the caller know we're sending back a tuplestore */
681
682 /* now go build it */
685 tupdesc,
687
688 /*
689 * SFRM_Materialize mode expects us to return a NULL Datum. The actual
690 * tuples are in our tuplestore and passed back through rsinfo->setResult.
691 * rsinfo->setDesc is set to the tuple description that we actually used
692 * to build our tuples with, so the caller can verify we did what it was
693 * expecting.
694 */
695 rsinfo->setDesc = tupdesc;
696 MemoryContextSwitchTo(oldcontext);
697
698 return (Datum) 0;
699}
Definition: dynahash.c:222
static Tuplestorestate * get_crosstab_tuplestore(char *sql, HTAB *crosstab_hash, TupleDesc tupdesc, bool randomAccess)
Definition: tablefunc.c:791
Datum crosstab_hash(PG_FUNCTION_ARGS)
Definition: tablefunc.c:636
static HTAB * load_categories_hash(char *cats_sql, MemoryContext per_query_ctx)
Definition: tablefunc.c:705

References ReturnSetInfo::allowedModes, CreateTupleDescCopy(), crosstab_hash(), ReturnSetInfo::econtext, ExprContext::ecxt_per_query_memory, ereport, errcode(), errdetail(), errmsg(), ERROR, ReturnSetInfo::expectedDesc, get_crosstab_tuplestore(), if(), IsA, load_categories_hash(), MemoryContextSwitchTo(), PG_GETARG_TEXT_PP, ReturnSetInfo::returnMode, ReturnSetInfo::setDesc, ReturnSetInfo::setResult, SFRM_Materialize, SFRM_Materialize_Random, and text_to_cstring().

Referenced by crosstab_hash(), get_crosstab_tuplestore(), and load_categories_hash().

◆ get_crosstab_tuplestore()

static Tuplestorestate * get_crosstab_tuplestore ( char *  sql,
HTAB *  crosstab_hash,
TupleDesc  tupdesc,
bool  randomAccess 
)
static

Definition at line 791 of file tablefunc.c.

795{
796 Tuplestorestate *tupstore;
797 int num_categories = hash_get_num_entries(crosstab_hash);
798 AttInMetadata *attinmeta = TupleDescGetAttInMetadata(tupdesc);
799 char **values;
800 HeapTuple tuple;
801 int ret;
802 uint64 proc;
803
804 /* initialize our tuplestore (while still in query context!) */
805 tupstore = tuplestore_begin_heap(randomAccess, false, work_mem);
806
807 /* Connect to SPI manager */
808 SPI_connect();
809
810 /* Now retrieve the crosstab source rows */
811 ret = SPI_execute(sql, true, 0);
812 proc = SPI_processed;
813
814 /* Check for qualifying tuples */
815 if ((ret == SPI_OK_SELECT) && (proc > 0))
816 {
817 SPITupleTable *spi_tuptable = SPI_tuptable;
818 TupleDesc spi_tupdesc = spi_tuptable->tupdesc;
819 int ncols = spi_tupdesc->natts;
820 char *rowid;
821 char *lastrowid = NULL;
822 bool firstpass = true;
823 uint64 i;
824 int j;
825 int result_ncols;
826
827 if (num_categories == 0)
828 {
829 /* no qualifying category tuples */
831 (errcode(ERRCODE_CARDINALITY_VIOLATION),
832 errmsg("crosstab categories query must return at least one row")));
833 }
834
835 /*
836 * The provided SQL query must always return at least three columns:
837 *
838 * 1. rowname the label for each row - column 1 in the final result
839 * 2. category the label for each value-column in the final result 3.
840 * value the values used to populate the value-columns
841 *
842 * If there are more than three columns, the last two are taken as
843 * "category" and "values". The first column is taken as "rowname".
844 * Additional columns (2 thru N-2) are assumed the same for the same
845 * "rowname", and are copied into the result tuple from the first time
846 * we encounter a particular rowname.
847 */
848 if (ncols < 3)
850 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
851 errmsg("invalid crosstab source data query"),
852 errdetail("The query must return at least 3 columns: row_name, category, and value.")));
853
854 result_ncols = (ncols - 2) + num_categories;
855
856 /* Recheck to make sure output tuple descriptor looks reasonable */
857 if (tupdesc->natts != result_ncols)
859 (errcode(ERRCODE_DATATYPE_MISMATCH),
860 errmsg("invalid crosstab return type"),
861 errdetail("Return row must have %d columns, not %d.",
862 result_ncols, tupdesc->natts)));
863
864 /* allocate space and make sure it's clear */
865 values = (char **) palloc0(result_ncols * sizeof(char *));
866
867 for (i = 0; i < proc; i++)
868 {
869 HeapTuple spi_tuple;
870 crosstab_cat_desc *catdesc;
871 char *catname;
872
873 /* get the next sql result tuple */
874 spi_tuple = spi_tuptable->vals[i];
875
876 /* get the rowid from the current sql result tuple */
877 rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
878
879 /*
880 * if we're on a new output row, grab the column values up to
881 * column N-2 now
882 */
883 if (firstpass || !xstreq(lastrowid, rowid))
884 {
885 /*
886 * a new row means we need to flush the old one first, unless
887 * we're on the very first row
888 */
889 if (!firstpass)
890 {
891 /* rowid changed, flush the previous output row */
892 tuple = BuildTupleFromCStrings(attinmeta, values);
893
894 tuplestore_puttuple(tupstore, tuple);
895
896 for (j = 0; j < result_ncols; j++)
897 xpfree(values[j]);
898 }
899
900 values[0] = rowid;
901 for (j = 1; j < ncols - 2; j++)
902 values[j] = SPI_getvalue(spi_tuple, spi_tupdesc, j + 1);
903
904 /* we're no longer on the first pass */
905 firstpass = false;
906 }
907
908 /* look up the category and fill in the appropriate column */
909 catname = SPI_getvalue(spi_tuple, spi_tupdesc, ncols - 1);
910
911 if (catname != NULL)
912 {
913 crosstab_HashTableLookup(crosstab_hash, catname, catdesc);
914
915 if (catdesc)
916 values[catdesc->attidx + ncols - 2] =
917 SPI_getvalue(spi_tuple, spi_tupdesc, ncols);
918 }
919
920 xpfree(lastrowid);
921 xpstrdup(lastrowid, rowid);
922 }
923
924 /* flush the last output row */
925 tuple = BuildTupleFromCStrings(attinmeta, values);
926
927 tuplestore_puttuple(tupstore, tuple);
928 }
929
930 if (SPI_finish() != SPI_OK_FINISH)
931 /* internal error */
932 elog(ERROR, "get_crosstab_tuplestore: SPI_finish() failed");
933
934 return tupstore;
935}
int64 hash_get_num_entries(HTAB *hashp)
Definition: dynahash.c:1336
#define elog(elevel,...)
Definition: elog.h:226
j
int j
Definition: isn.c:78
#define SPI_OK_FINISH
Definition: spi.h:83
Definition: tablefunc.c:123
uint64 attidx
Definition: tablefunc.c:125
#define crosstab_HashTableLookup(HASHTAB, CATNAME, CATDESC)
Definition: tablefunc.c:131

References crosstab_cat_desc::attidx, BuildTupleFromCStrings(), crosstab_hash(), crosstab_HashTableLookup, elog, ereport, errcode(), errdetail(), errmsg(), ERROR, hash_get_num_entries(), i, j, TupleDescData::natts, palloc0(), SPI_connect(), SPI_execute(), SPI_finish(), SPI_getvalue(), SPI_OK_FINISH, SPI_OK_SELECT, SPI_processed, SPI_tuptable, SPITupleTable::tupdesc, TupleDescGetAttInMetadata(), tuplestore_begin_heap(), tuplestore_puttuple(), SPITupleTable::vals, values, work_mem, xpfree, xpstrdup, and xstreq.

Referenced by crosstab_hash().

◆ get_normal_pair()

static void get_normal_pair ( float8 *  x1,
float8 *  x2 
)
static

Definition at line 287 of file tablefunc.c.

288{
289 float8 u1,
290 u2,
291 v1,
292 v2,
293 s;
294
295 do
296 {
299
300 v1 = (2.0 * u1) - 1.0;
301 v2 = (2.0 * u2) - 1.0;
302
303 s = v1 * v1 + v2 * v2;
304 } while (s >= 1.0);
305
306 if (s == 0)
307 {
308 *x1 = 0;
309 *x2 = 0;
310 }
311 else
312 {
313 s = sqrt((-2.0 * log(s)) / s);
314 *x1 = v1 * s;
315 *x2 = v2 * s;
316 }
317}
double float8
Definition: c.h:635
double pg_prng_double(pg_prng_state *state)
Definition: pg_prng.c:268
pg_prng_state pg_global_prng_state
Definition: pg_prng.c:34

References pg_global_prng_state, and pg_prng_double().

Referenced by normal_rand().

◆ load_categories_hash()

static HTAB * load_categories_hash ( char *  cats_sql,
MemoryContext  per_query_ctx 
)
static

Definition at line 705 of file tablefunc.c.

706{
708 HASHCTL ctl;
709 int ret;
710 uint64 proc;
711 MemoryContext SPIcontext;
712
713 /* initialize the category hash table */
714 ctl.keysize = MAX_CATNAME_LEN;
715 ctl.entrysize = sizeof(crosstab_HashEnt);
716 ctl.hcxt = per_query_ctx;
717
718 /*
719 * use INIT_CATS, defined above as a guess of how many hash table entries
720 * to create, initially
721 */
722 crosstab_hash = hash_create("crosstab hash",
723 INIT_CATS,
724 &ctl,
726
727 /* Connect to SPI manager */
728 SPI_connect();
729
730 /* Retrieve the category name rows */
731 ret = SPI_execute(cats_sql, true, 0);
732 proc = SPI_processed;
733
734 /* Check for qualifying tuples */
735 if ((ret == SPI_OK_SELECT) && (proc > 0))
736 {
737 SPITupleTable *spi_tuptable = SPI_tuptable;
738 TupleDesc spi_tupdesc = spi_tuptable->tupdesc;
739 uint64 i;
740
741 /*
742 * The provided categories SQL query must always return one column:
743 * category - the label or identifier for each column
744 */
745 if (spi_tupdesc->natts != 1)
747 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
748 errmsg("invalid crosstab categories query"),
749 errdetail("The query must return one column.")));
750
751 for (i = 0; i < proc; i++)
752 {
753 crosstab_cat_desc *catdesc;
754 char *catname;
755 HeapTuple spi_tuple;
756
757 /* get the next sql result tuple */
758 spi_tuple = spi_tuptable->vals[i];
759
760 /* get the category from the current sql result tuple */
761 catname = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
762 if (catname == NULL)
764 (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
765 errmsg("crosstab category value must not be null")));
766
767 SPIcontext = MemoryContextSwitchTo(per_query_ctx);
768
769 catdesc = (crosstab_cat_desc *) palloc(sizeof(crosstab_cat_desc));
770 catdesc->catname = catname;
771 catdesc->attidx = i;
772
773 /* Add the proc description block to the hashtable */
775
776 MemoryContextSwitchTo(SPIcontext);
777 }
778 }
779
780 if (SPI_finish() != SPI_OK_FINISH)
781 /* internal error */
782 elog(ERROR, "load_categories_hash: SPI_finish() failed");
783
784 return crosstab_hash;
785}
HTAB * hash_create(const char *tabname, int64 nelem, const HASHCTL *info, int flags)
Definition: dynahash.c:358
#define HASH_STRINGS
Definition: hsearch.h:96
#define HASH_CONTEXT
Definition: hsearch.h:102
#define HASH_ELEM
Definition: hsearch.h:95
tree ctl
Definition: radixtree.h:1838
Definition: hsearch.h:66
char * catname
Definition: tablefunc.c:124
#define crosstab_HashTableInsert(HASHTAB, CATDESC)
Definition: tablefunc.c:145
#define INIT_CATS
Definition: tablefunc.c:129
struct crosstab_hashent crosstab_HashEnt

References crosstab_cat_desc::attidx, crosstab_cat_desc::catname, crosstab_hash(), crosstab_HashTableInsert, ctl, elog, ereport, errcode(), errdetail(), errmsg(), ERROR, HASH_CONTEXT, hash_create(), HASH_ELEM, HASH_STRINGS, i, INIT_CATS, MAX_CATNAME_LEN, MemoryContextSwitchTo(), TupleDescData::natts, palloc(), SPI_connect(), SPI_execute(), SPI_finish(), SPI_getvalue(), SPI_OK_FINISH, SPI_OK_SELECT, SPI_processed, SPI_tuptable, SPITupleTable::tupdesc, and SPITupleTable::vals.

Referenced by crosstab_hash().

◆ normal_rand()

Datum normal_rand ( PG_FUNCTION_ARGS  )

Definition at line 176 of file tablefunc.c.

177{
178 FuncCallContext *funcctx;
179 uint64 call_cntr;
180 uint64 max_calls;
181 normal_rand_fctx *fctx;
182 float8 mean;
183 float8 stddev;
184 float8 carry_val;
185 bool use_carry;
186 MemoryContext oldcontext;
187
188 /* stuff done only on the first call of the function */
189 if (SRF_IS_FIRSTCALL())
190 {
191 int32 num_tuples;
192
193 /* create a function context for cross-call persistence */
194 funcctx = SRF_FIRSTCALL_INIT();
195
196 /*
197 * switch to memory context appropriate for multiple function calls
198 */
199 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
200
201 /* total number of tuples to be returned */
202 num_tuples = PG_GETARG_INT32(0);
203 if (num_tuples < 0)
205 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
206 errmsg("number of rows cannot be negative")));
207 funcctx->max_calls = num_tuples;
208
209 /* allocate memory for user context */
210 fctx = (normal_rand_fctx *) palloc(sizeof(normal_rand_fctx));
211
212 /*
213 * Use fctx to keep track of upper and lower bounds from call to call.
214 * It will also be used to carry over the spare value we get from the
215 * Box-Muller algorithm so that we only actually calculate a new value
216 * every other call.
217 */
218 fctx->mean = PG_GETARG_FLOAT8(1);
219 fctx->stddev = PG_GETARG_FLOAT8(2);
220 fctx->carry_val = 0;
221 fctx->use_carry = false;
222
223 funcctx->user_fctx = fctx;
224
225 MemoryContextSwitchTo(oldcontext);
226 }
227
228 /* stuff done on every call of the function */
229 funcctx = SRF_PERCALL_SETUP();
230
231 call_cntr = funcctx->call_cntr;
232 max_calls = funcctx->max_calls;
233 fctx = funcctx->user_fctx;
234 mean = fctx->mean;
235 stddev = fctx->stddev;
236 carry_val = fctx->carry_val;
237 use_carry = fctx->use_carry;
238
239 if (call_cntr < max_calls) /* do when there is more left to send */
240 {
241 float8 result;
242
243 if (use_carry)
244 {
245 /*
246 * reset use_carry and use second value obtained on last pass
247 */
248 fctx->use_carry = false;
249 result = carry_val;
250 }
251 else
252 {
253 float8 normval_1;
254 float8 normval_2;
255
256 /* Get the next two normal values */
257 get_normal_pair(&normval_1, &normval_2);
258
259 /* use the first */
260 result = mean + (stddev * normval_1);
261
262 /* and save the second */
263 fctx->carry_val = mean + (stddev * normval_2);
264 fctx->use_carry = true;
265 }
266
267 /* send the result */
268 SRF_RETURN_NEXT(funcctx, Float8GetDatum(result));
269 }
270 else
271 /* do when there is no more left */
272 SRF_RETURN_DONE(funcctx);
273}
#define PG_GETARG_FLOAT8(n)
Definition: fmgr.h:282
#define SRF_IS_FIRSTCALL()
Definition: funcapi.h:304
#define SRF_PERCALL_SETUP()
Definition: funcapi.h:308
#define SRF_RETURN_NEXT(_funcctx, _result)
Definition: funcapi.h:310
#define SRF_FIRSTCALL_INIT()
Definition: funcapi.h:306
#define SRF_RETURN_DONE(_funcctx)
Definition: funcapi.h:328
static Datum Float8GetDatum(float8 X)
Definition: postgres.h:492
void * user_fctx
Definition: funcapi.h:82
uint64 max_calls
Definition: funcapi.h:74
uint64 call_cntr
Definition: funcapi.h:65
MemoryContext multi_call_memory_ctx
Definition: funcapi.h:101
Definition: tablefunc.c:90
bool use_carry
Definition: tablefunc.c:94
float8 carry_val
Definition: tablefunc.c:93
float8 mean
Definition: tablefunc.c:91
float8 stddev
Definition: tablefunc.c:92
static void get_normal_pair(float8 *x1, float8 *x2)
Definition: tablefunc.c:287

References FuncCallContext::call_cntr, normal_rand_fctx::carry_val, ereport, errcode(), errmsg(), ERROR, Float8GetDatum(), get_normal_pair(), FuncCallContext::max_calls, normal_rand_fctx::mean, MemoryContextSwitchTo(), FuncCallContext::multi_call_memory_ctx, palloc(), PG_GETARG_FLOAT8, PG_GETARG_INT32, SRF_FIRSTCALL_INIT, SRF_IS_FIRSTCALL, SRF_PERCALL_SETUP, SRF_RETURN_DONE, SRF_RETURN_NEXT, normal_rand_fctx::stddev, normal_rand_fctx::use_carry, and FuncCallContext::user_fctx.

◆ PG_FUNCTION_INFO_V1() [1/5]

PG_FUNCTION_INFO_V1 ( connectby_text  )

◆ PG_FUNCTION_INFO_V1() [2/5]

PG_FUNCTION_INFO_V1 ( connectby_text_serial  )

◆ PG_FUNCTION_INFO_V1() [3/5]

PG_FUNCTION_INFO_V1 ( crosstab  )

◆ PG_FUNCTION_INFO_V1() [4/5]

PG_FUNCTION_INFO_V1 ( crosstab_hash  )

◆ PG_FUNCTION_INFO_V1() [5/5]

PG_FUNCTION_INFO_V1 ( normal_rand  )

◆ PG_MODULE_MAGIC_EXT()

PG_MODULE_MAGIC_EXT ( .  name = "tablefunc",
.  version = PG_VERSION 
)

◆ validateConnectbyTupleDesc()

static void validateConnectbyTupleDesc ( TupleDesc  td,
bool  show_branch,
bool  show_serial 
)
static

Definition at line 1407 of file tablefunc.c.

1408{
1409 int expected_cols;
1410
1411 /* are there the correct number of columns */
1412 if (show_branch)
1413 expected_cols = CONNECTBY_NCOLS;
1414 else
1415 expected_cols = CONNECTBY_NCOLS_NOBRANCH;
1416 if (show_serial)
1417 expected_cols++;
1418
1419 if (td->natts != expected_cols)
1420 ereport(ERROR,
1421 (errcode(ERRCODE_DATATYPE_MISMATCH),
1422 errmsg("invalid connectby return type"),
1423 errdetail("Return row must have %d columns, not %d.",
1424 expected_cols, td->natts)));
1425
1426 /* the first two columns will be checked against the input tuples later */
1427
1428 /* check that the type of the third column is INT4 */
1429 if (TupleDescAttr(td, 2)->atttypid != INT4OID)
1430 ereport(ERROR,
1431 (errcode(ERRCODE_DATATYPE_MISMATCH),
1432 errmsg("invalid connectby return type"),
1433 errdetail("Third return column (depth) must be type %s.",
1434 format_type_be(INT4OID))));
1435
1436 /* check that the type of the branch column is TEXT if applicable */
1437 if (show_branch && TupleDescAttr(td, 3)->atttypid != TEXTOID)
1438 ereport(ERROR,
1439 (errcode(ERRCODE_DATATYPE_MISMATCH),
1440 errmsg("invalid connectby return type"),
1441 errdetail("Fourth return column (branch) must be type %s.",
1442 format_type_be(TEXTOID))));
1443
1444 /* check that the type of the serial column is INT4 if applicable */
1445 if (show_branch && show_serial &&
1446 TupleDescAttr(td, 4)->atttypid != INT4OID)
1447 ereport(ERROR,
1448 (errcode(ERRCODE_DATATYPE_MISMATCH),
1449 errmsg("invalid connectby return type"),
1450 errdetail("Fifth return column (serial) must be type %s.",
1451 format_type_be(INT4OID))));
1452 if (!show_branch && show_serial &&
1453 TupleDescAttr(td, 3)->atttypid != INT4OID)
1454 ereport(ERROR,
1455 (errcode(ERRCODE_DATATYPE_MISMATCH),
1456 errmsg("invalid connectby return type"),
1457 errdetail("Fourth return column (serial) must be type %s.",
1458 format_type_be(INT4OID))));
1459
1460 /* OK, the tupdesc is valid for our purposes */
1461}
char * format_type_be(Oid type_oid)
Definition: format_type.c:343

References CONNECTBY_NCOLS, CONNECTBY_NCOLS_NOBRANCH, ereport, errcode(), errdetail(), errmsg(), ERROR, format_type_be(), TupleDescData::natts, and TupleDescAttr().

Referenced by connectby_text(), and connectby_text_serial().

AltStyle によって変換されたページ (->オリジナル) /