PostgreSQL Source Code: contrib/tablefunc/tablefunc.c Source File (original) (raw)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
34
35#include <math.h>
36
46
48 .name = "tablefunc",
50);
51
56 bool randomAccess);
71 bool randomAccess,
80 int level,
88
96
97#define xpfree(var_) \
98 do { \
99 if (var_ != NULL) \
100 { \
101 pfree(var_); \
102 var_ = NULL; \
103 } \
104 } while (0)
105
106#define xpstrdup(tgtvar_, srcvar_) \
107 do { \
108 if (srcvar_) \
109 tgtvar_ = pstrdup(srcvar_); \
110 else \
111 tgtvar_ = NULL; \
112 } while (0)
113
114#define xstreq(tgtvar_, srcvar_) \
115 (((tgtvar_ == NULL) && (srcvar_ == NULL)) || \
116 ((tgtvar_ != NULL) && (srcvar_ != NULL) && (strcmp(tgtvar_, srcvar_) == 0)))
117
118
119#define INT32_STRLEN 12
120
121
127
128#define MAX_CATNAME_LEN NAMEDATALEN
129#define INIT_CATS 64
130
131#define crosstab_HashTableLookup(HASHTAB, CATNAME, CATDESC) \
132do { \
133 crosstab_HashEnt *hentry; char key[MAX_CATNAME_LEN]; \
134 \
135 MemSet(key, 0, MAX_CATNAME_LEN); \
136 snprintf(key, MAX_CATNAME_LEN - 1, "%s", CATNAME); \
137 hentry = (crosstab_HashEnt*) hash_search(HASHTAB, \
138 key, HASH_FIND, NULL); \
139 if (hentry) \
140 CATDESC = hentry->catdesc; \
141 else \
142 CATDESC = NULL; \
143} while(0)
144
145#define crosstab_HashTableInsert(HASHTAB, CATDESC) \
146do { \
147 crosstab_HashEnt *hentry; bool found; char key[MAX_CATNAME_LEN]; \
148 \
149 MemSet(key, 0, MAX_CATNAME_LEN); \
150 snprintf(key, MAX_CATNAME_LEN - 1, "%s", CATDESC->catname); \
151 hentry = (crosstab_HashEnt*) hash_search(HASHTAB, \
152 key, HASH_ENTER, &found); \
153 if (found) \
154 ereport(ERROR, \
155 (errcode(ERRCODE_DUPLICATE_OBJECT), \
156 errmsg("duplicate category name"))); \
157 hentry->catdesc = CATDESC; \
158} while(0)
159
160
166
167
168
169
170
171
172
173
177{
185 bool use_carry;
187
188
190 {
191 int32 num_tuples;
192
193
195
196
197
198
200
201
203 if (num_tuples < 0)
206 errmsg("number of rows cannot be negative")));
207 funcctx->max_calls = num_tuples;
208
209
211
212
213
214
215
216
217
220 fctx->carry_val = 0;
221 fctx->use_carry = false;
222
224
226 }
227
228
230
231 call_cntr = funcctx->call_cntr;
232 max_calls = funcctx->max_calls;
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)
240 {
242
243 if (use_carry)
244 {
245
246
247
248 fctx->use_carry = false;
249 result = carry_val;
250 }
251 else
252 {
255
256
258
259
260 result = mean + (stddev * normval_1);
261
262
264 fctx->use_carry = true;
265 }
266
267
269 }
270 else
271
273}
274
275
276
277
278
279
280
281
282
283
284
285
286static void
288{
291 v1,
293 s;
294
295 do
296 {
299
300 v1 = (2.0 * u1) - 1.0;
302
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;
316 }
317}
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
359{
371 int i;
375 int ret;
377
378
382 errmsg("set-valued function called in context that cannot accept a set")));
386 errmsg("materialize mode required, but it is not allowed in this context")));
387
389
390
392
393
396
397
399 {
403 }
404
407
408
409
410
411
412
413
414
415
416
417
418
422 errmsg("invalid crosstab source data query"),
423 errdetail("The query must return 3 columns: row_name, category, and value.")));
424
425
427 {
429
430 break;
432
435 errmsg("function returning record called in context "
436 "that cannot accept type record")));
437 break;
438 default:
439
442 errmsg("return type must be a row type")));
443 break;
444 }
445
446
447
448
449
451
452
453
454
456
457
459
460
461 tupstore =
464
466
467
468
469
470
472
473
474 max_calls = proc;
475
476
478
481
482 for (call_cntr = 0; call_cntr < max_calls; call_cntr++)
483 {
486
487
489
490
491
492
493
495 {
498
499
500 if (call_cntr >= max_calls)
501 break;
502
503
505
506
508
509
510
511
512
513 if (i == 0)
514 {
516
517
518
519
520
522 {
525 break;
526 }
527 }
528
529
530
531
532
534 {
535
536
537
538
539
540
541
543
544
545
546
547
548
550 call_cntr++;
552 }
553 else
554 {
555
556
557
558
559
560 call_cntr--;
562 break;
563 }
564 }
565
567 {
569
570
574 }
575
576
580
581
586 }
587
588
590 rsinfo->setResult = tupstore;
591 rsinfo->setDesc = tupdesc;
592
593
595
596 return (Datum) 0;
597}
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
637{
645
646
650 errmsg("set-valued function called in context that cannot accept a set")));
655 errmsg("materialize mode required, but it is not allowed in this context")));
656
659
660
662
663
664
665
666
667
668
669
670 if (tupdesc->natts < 2)
673 errmsg("invalid crosstab return type"),
674 errdetail("Return row must have at least two columns.")));
675
676
678
679
681
682
685 tupdesc,
687
688
689
690
691
692
693
694
695 rsinfo->setDesc = tupdesc;
697
698 return (Datum) 0;
699}
700
701
702
703
706{
709 int ret;
712
713
717
718
719
720
721
726
727
729
730
733
734
736 {
740
741
742
743
744
748 errmsg("invalid crosstab categories query"),
749 errdetail("The query must return one column.")));
750
751 for (i = 0; i < proc; i++)
752 {
754 char *catname;
756
757
759
760
762 if (catname == NULL)
765 errmsg("crosstab category value must not be null")));
766
768
770 catdesc->catname = catname;
772
773
775
777 }
778 }
779
781
782 elog(ERROR, "load_categories_hash: SPI_finish() failed");
783
785}
786
787
788
789
794 bool randomAccess)
795{
801 int ret;
803
804
806
807
809
810
813
814
816 {
824 int j;
826
828 {
829
832 errmsg("crosstab categories query must return at least one row")));
833 }
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848 if (ncols < 3)
851 errmsg("invalid crosstab source data query"),
852 errdetail("The query must return at least 3 columns: row_name, category, and value.")));
853
855
856
860 errmsg("invalid crosstab return type"),
861 errdetail("Return row must have %d columns, not %d.",
863
864
866
867 for (i = 0; i < proc; i++)
868 {
871 char *catname;
872
873
875
876
878
879
880
881
882
884 {
885
886
887
888
890 {
891
893
895
898 }
899
901 for (j = 1; j < ncols - 2; j++)
903
904
906 }
907
908
910
911 if (catname != NULL)
912 {
914
915 if (catdesc)
918 }
919
922 }
923
924
926
928 }
929
931
932 elog(ERROR, "get_crosstab_tuplestore: SPI_finish() failed");
933
934 return tupstore;
935}
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
972
973#define CONNECTBY_NCOLS 4
974#define CONNECTBY_NCOLS_NOBRANCH 3
975
978{
992
993
997 errmsg("set-valued function called in context that cannot accept a set")));
1002 errmsg("materialize mode required, but it is not allowed in this context")));
1003
1004 if (fcinfo->nargs == 6)
1005 {
1008 }
1009 else
1010
1012
1015
1016
1018
1019
1021
1022
1024
1025
1038 attinmeta);
1039 rsinfo->setDesc = tupdesc;
1040
1042
1043
1044
1045
1046
1047
1048
1049
1050 return (Datum) 0;
1051}
1052
1056{
1071
1072
1076 errmsg("set-valued function called in context that cannot accept a set")));
1081 errmsg("materialize mode required, but it is not allowed in this context")));
1082
1083 if (fcinfo->nargs == 7)
1084 {
1087 }
1088 else
1089
1091
1094
1095
1097
1098
1100
1101
1103
1104
1117 attinmeta);
1118 rsinfo->setDesc = tupdesc;
1119
1121
1122
1123
1124
1125
1126
1127
1128
1129 return (Datum) 0;
1130}
1131
1132
1133
1134
1135
1147 bool randomAccess,
1149{
1153
1154
1156
1157
1159
1160
1162
1164
1165
1173 0,
1174 &serial,
1179 attinmeta,
1180 tupstore);
1181
1183
1184 return tupstore;
1185}
1186
1187static void
1195 int level,
1203{
1205 int ret;
1216
1218 return;
1219
1221
1222
1224 {
1225 appendStringInfo(&sql, "SELECT %s, %s FROM %s WHERE %s = %s AND %s IS NOT NULL AND %s <> %s",
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",
1245 }
1246
1249 else
1251
1252
1253 if (level == 0)
1254 {
1255
1257
1258
1260
1261
1264
1265
1268
1269
1271 {
1275 else
1277 }
1278
1279
1281
1282
1284
1285
1286 level++;
1287 }
1288
1289
1292
1293
1295 {
1303
1304
1305
1306
1307
1309
1313
1314 for (i = 0; i < proc; i++)
1315 {
1316
1319
1320
1322
1323
1325
1326
1328
1329
1331
1332
1334 {
1340 errmsg("infinite recursion detected")));
1341 }
1342
1343
1347
1348
1355 {
1359 else
1361 }
1362
1364
1365
1367
1369
1370
1379 level + 1,
1385 attinmeta,
1386 tupstore);
1387
1390
1391
1395 }
1396
1400 }
1401}
1402
1403
1404
1405
1406static void
1408{
1410
1411
1414 else
1418
1422 errmsg("invalid connectby return type"),
1423 errdetail("Return row must have %d columns, not %d.",
1425
1426
1427
1428
1432 errmsg("invalid connectby return type"),
1433 errdetail("Third return column (depth) must be type %s.",
1435
1436
1440 errmsg("invalid connectby return type"),
1441 errdetail("Fourth return column (branch) must be type %s.",
1443
1444
1449 errmsg("invalid connectby return type"),
1450 errdetail("Fifth return column (serial) must be type %s.",
1456 errmsg("invalid connectby return type"),
1457 errdetail("Fourth return column (serial) must be type %s.",
1459
1460
1461}
1462
1463
1464
1465
1466static void
1468{
1473
1474
1475
1476
1480 errmsg("invalid connectby source data query"),
1481 errdetail("The query must return at least two columns.")));
1482
1483
1484
1485
1486
1495 errmsg("invalid connectby return type"),
1496 errdetail("Source key type %s does not match return key type %s.",
1499
1508 errmsg("invalid connectby return type"),
1509 errdetail("Source parent key type %s does not match return parent key type %s.",
1512
1513
1514}
1515
1516
1517
1518
1519static void
1521{
1522 int i;
1527
1528 if (ret_tupdesc->natts < 2)
1531 errmsg("invalid crosstab return type"),
1532 errdetail("Return row must have at least two columns.")));
1533 Assert(sql_tupdesc->natts == 3);
1534
1535
1544 errmsg("invalid crosstab return type"),
1545 errdetail("Source row_name datatype %s does not match return row_name datatype %s.",
1548
1549
1550
1551
1552
1553
1556 for (i = 1; i < ret_tupdesc->natts; i++)
1557 {
1560
1565 errmsg("invalid crosstab return type"),
1566 errdetail("Source value datatype %s does not match return value datatype %s in column %d.",
1569 i + 1)));
1570 }
1571
1572
1573}
static Datum values[MAXATTR]
#define Assert(condition)
HTAB * hash_create(const char *tabname, int64 nelem, const HASHCTL *info, int flags)
int64 hash_get_num_entries(HTAB *hashp)
int errcode(int sqlerrcode)
int errdetail(const char *fmt,...) pg_attribute_printf(1
#define ereport(elevel,...)
HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values)
AttInMetadata * TupleDescGetAttInMetadata(TupleDesc tupdesc)
@ SFRM_Materialize_Random
#define palloc_object(type)
#define PG_GETARG_TEXT_PP(n)
#define PG_GETARG_FLOAT8(n)
#define PG_MODULE_MAGIC_EXT(...)
#define PG_FUNCTION_INFO_V1(funcname)
#define PG_GETARG_INT32(n)
char * format_type_with_typemod(Oid type_oid, int32 typemod)
char * format_type_be(Oid type_oid)
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)
char * pstrdup(const char *in)
void pfree(void *pointer)
void * palloc0(Size size)
#define IsA(nodeptr, _type_)
static MemoryContext MemoryContextSwitchTo(MemoryContext context)
double pg_prng_double(pg_prng_state *state)
pg_prng_state pg_global_prng_state
static Datum Float8GetDatum(float8 X)
char * quote_literal_cstr(const char *rawstr)
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)
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)
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)
struct crosstab_hashent crosstab_HashEnt
static HTAB * load_categories_hash(char *cats_sql, MemoryContext per_query_ctx)
TupleDesc CreateTupleDescCopy(TupleDesc tupdesc)
static FormData_pg_attribute * TupleDescAttr(TupleDesc tupdesc, int 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)