own_test.c (23452B)
1 #include "postgres.h" 2 3 #include <stdio.h> 4 #include <stdlib.h> 5 #include <postgresql/libpq-fe.h> 6 #include <libpq-int.h> 7 #include <catalog/pg_type.h> 8 #include <executor/spi.h> 9 #include <funcapi.h> 10 #include <fmgr.h> 11 #include <utils/builtins.h> 12 #include <utils/array.h> 13 #include <sys/time.h> 14 #include <utils/numeric.h> 15 #include <utils/timestamp.h> 16 #include <utils/bytea.h> 17 18 #ifdef PG_MODULE_MAGIC 19 PG_MODULE_MAGIC; 20 #endif 21 22 typedef struct 23 { 24 Datum col1; 25 Datum col2; 26 } valuest; 27 28 void _PG_init (void); 29 30 void _PG_fini (void); 31 32 void 33 _PG_init (void) 34 { 35 } 36 37 38 PG_FUNCTION_INFO_V1 (pg_spi_insert_int); 39 PG_FUNCTION_INFO_V1 (pg_spi_select_from_x); 40 PG_FUNCTION_INFO_V1 (pg_spi_select_pair_from_y); 41 // PG_FUNCTION_INFO_V1(pg_spi_select_with_cond); 42 PG_FUNCTION_INFO_V1 (pg_spi_update_y); 43 PG_FUNCTION_INFO_V1 (pg_spi_prepare_example); 44 PG_FUNCTION_INFO_V1 (pg_spi_prepare_example_without_saveplan); 45 PG_FUNCTION_INFO_V1 (pg_spi_prepare_insert); 46 PG_FUNCTION_INFO_V1 (pg_spi_prepare_insert_without_saveplan); 47 // PG_FUNCTION_INFO_V1(pg_spi_prepare_select_with_cond); 48 PG_FUNCTION_INFO_V1 (pg_spi_prepare_select_with_cond_without_saveplan); 49 PG_FUNCTION_INFO_V1 (pg_spi_prepare_update); 50 PG_FUNCTION_INFO_V1 (pg_spi_get_dep_ref_fees); 51 // SIMPLE SELECT 52 Datum 53 pg_spi_prepare_example (PG_FUNCTION_ARGS) 54 { 55 static SPIPlanPtr prepared_plan; 56 int ret; 57 int64 result; 58 char *value; 59 SPIPlanPtr new_plan; 60 61 ret = SPI_connect (); 62 if (ret != SPI_OK_CONNECT) 63 { 64 elog (ERROR, "DB connection failed ! \n"); 65 } 66 { 67 if (prepared_plan == NULL) 68 { 69 new_plan = SPI_prepare ("SELECT 1 FROM X", 0, NULL); 70 prepared_plan = SPI_saveplan (new_plan); 71 72 if (prepared_plan == NULL) 73 { 74 elog (ERROR, "FAIL TO SAVE !\n"); 75 } 76 } 77 78 ret = SPI_execute_plan (prepared_plan, NULL, 0,false, 0); 79 if (ret != SPI_OK_SELECT) 80 { 81 elog (ERROR, "SELECT FAILED %d !\n", ret); 82 } 83 84 if (SPI_tuptable != NULL && SPI_tuptable->vals != NULL && 85 SPI_tuptable->tupdesc != NULL) 86 { 87 value = SPI_getvalue (SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1); 88 result = atoi (value); 89 } 90 else 91 { 92 elog (ERROR, "EMPTY TABLE !\n"); 93 } 94 } 95 SPI_finish (); 96 PG_RETURN_INT64 (result); 97 } 98 99 100 Datum 101 pg_spi_prepare_example_without_saveplan (PG_FUNCTION_ARGS) 102 { 103 int ret; 104 int64 result; 105 char *value; 106 SPIPlanPtr new_plan; 107 108 ret = SPI_connect (); 109 if (ret != SPI_OK_CONNECT) 110 { 111 elog (ERROR, "DB connection failed ! \n"); 112 } 113 114 { 115 new_plan = SPI_prepare ("SELECT 1 FROM X", 0, NULL); 116 ret = SPI_execute_plan (new_plan, NULL, 0,false, 0); 117 if (ret != SPI_OK_SELECT) 118 { 119 elog (ERROR, "SELECT FAILED %d !\n", ret); 120 } 121 122 if (SPI_tuptable != NULL 123 && SPI_tuptable->vals != NULL 124 && SPI_tuptable->tupdesc != NULL) 125 { 126 value = SPI_getvalue (SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1); 127 result = atoi (value); 128 } 129 else 130 { 131 elog (ERROR, "EMPTY TABLE !\n"); 132 } 133 } 134 SPI_finish (); 135 PG_RETURN_INT64 (result);// PG_RETURN_INT64(result); 136 } 137 138 139 // SELECT 1 FROM X 140 // V1 141 Datum 142 pg_spi_select_from_x (PG_FUNCTION_ARGS) 143 { 144 int ret; 145 char *query = "SELECT 1 FROM X"; 146 uint64 proc; 147 ret = SPI_connect (); 148 149 if (ret != SPI_OK_CONNECT) 150 { 151 elog (ERROR, "SPI_connect failed"); 152 } 153 154 ret = SPI_exec (query, 10); 155 proc = SPI_processed; 156 if (ret != SPI_OK_SELECT) 157 { 158 elog (ERROR, "SPI_exec failed"); 159 } 160 161 SPI_finish (); 162 163 PG_RETURN_INT64 (proc); 164 } 165 166 167 // INSERT INTO X VALUES (1) 168 Datum 169 pg_spi_insert_int (PG_FUNCTION_ARGS) 170 { 171 int ret; 172 int nargs; 173 Oid argtypes[1]; 174 Datum values[1]; 175 char *query = "INSERT INTO X (a) VALUES ($1)"; 176 177 ret = SPI_connect (); 178 if (ret != SPI_OK_CONNECT) 179 { 180 elog (ERROR, "SPI_connect failed"); 181 } 182 183 nargs = 1; 184 argtypes[0] = INT4OID; 185 values[0] = Int32GetDatum (3); 186 187 ret = SPI_execute_with_args (query, nargs, argtypes, values, NULL, false, 0); 188 if (ret != SPI_OK_INSERT) 189 { 190 elog (ERROR, "SPI_execute_with_args failed"); 191 } 192 193 SPI_finish (); 194 195 PG_RETURN_VOID (); 196 } 197 198 199 Datum 200 pg_spi_prepare_insert (PG_FUNCTION_ARGS) 201 { 202 static SPIPlanPtr prepared_plan = NULL; 203 int ret; 204 int nargs; 205 Oid argtypes[1]; 206 Datum values[1]; 207 const char *query = "INSERT INTO X (a) VALUES ($1)"; 208 SPIPlanPtr new_plan; 209 ret = SPI_connect (); 210 if (ret != SPI_OK_CONNECT) 211 { 212 elog (ERROR, "SPI_connect failed ! \n"); 213 } 214 if (prepared_plan == NULL) 215 { 216 217 argtypes[0] = INT4OID; 218 nargs = 1; 219 values[0] = Int32GetDatum (3); 220 new_plan = SPI_prepare (query, nargs, argtypes); 221 if (new_plan== NULL) 222 { 223 elog (ERROR, "SPI_prepare failed ! \n"); 224 } 225 prepared_plan = SPI_saveplan (new_plan); 226 if (prepared_plan == NULL) 227 { 228 elog (ERROR, "SPI_saveplan failed ! \n"); 229 } 230 } 231 232 ret = SPI_execute_plan (prepared_plan, values, NULL, false, 0); 233 if (ret != SPI_OK_INSERT) 234 { 235 elog (ERROR, "SPI_execute_plan failed ! \n"); 236 } 237 238 SPI_finish (); 239 240 PG_RETURN_VOID (); 241 } 242 243 244 /* 245 Datum 246 pg_spi_prepare_insert_bytea(PG_FUNCTION_ARGS) 247 { 248 static SPIPlanPtr prepared_plan = NULL; 249 int ret; 250 int nargs; 251 Oid argtypes[1]; 252 Datum values[1]; 253 Oid argtypes2[1]; 254 Datum val[1]; 255 char *query = "INSERT INTO X (a) VALUES ($1)"; 256 SPIPlanPtr new_plan; 257 ret = SPI_connect(); 258 if (ret != SPI_OK_CONNECT) 259 { 260 elog(ERROR, "SPI_connect failed ! \n"); 261 } 262 if (prepared_plan == NULL) { 263 argtypes2[0] = BOOLOID; 264 val[0] = BoolGetDatum(); 265 argtypes[0] = BYTEAOID; 266 nargs = 1; 267 values[0] = Int32GetDatum(3); 268 new_plan = SPI_prepare(query, nargs, argtypes); 269 if (new_plan== NULL) 270 { 271 elog(ERROR, "SPI_prepare failed ! \n"); 272 } 273 prepared_plan = SPI_saveplan(new_plan); 274 if (prepared_plan == NULL) 275 { 276 elog(ERROR, "SPI_saveplan failed ! \n"); 277 } 278 } 279 280 ret = SPI_execute_plan(prepared_plan, values, NULL, false, 0); 281 if (ret != SPI_OK_INSERT) 282 { 283 elog(ERROR, "SPI_execute_plan failed ! \n"); 284 } 285 286 SPI_finish(); 287 288 PG_RETURN_VOID(); 289 } 290 */ 291 292 Datum 293 pg_spi_prepare_insert_without_saveplan (PG_FUNCTION_ARGS) 294 { 295 int ret; 296 int nargs; 297 Oid argtypes[1]; 298 Datum values[1]; 299 const char *query = "INSERT INTO X (a) VALUES ($1)"; 300 SPIPlanPtr new_plan; 301 ret = SPI_connect (); 302 if (ret != SPI_OK_CONNECT) 303 { 304 elog (ERROR, "SPI_connect failed"); 305 } 306 { 307 argtypes[0] = INT4OID; 308 nargs = 1; 309 values[0] = Int32GetDatum (3); 310 new_plan = SPI_prepare (query, nargs, argtypes); 311 if (new_plan== NULL) 312 { 313 elog (ERROR, "SPI_prepare failed"); 314 } 315 } 316 317 ret = SPI_execute_plan (new_plan, values, NULL, false, 0); 318 if (ret != SPI_OK_INSERT) 319 { 320 elog (ERROR, "SPI_execute_plan failed"); 321 } 322 323 SPI_finish (); 324 325 PG_RETURN_VOID (); 326 } 327 328 329 /* 330 Datum 331 pg_spi_select_pair_from_y(PG_FUNCTION_ARGS) 332 { 333 int ret; 334 valuest result; 335 bool isnull; 336 char *query = "SELECT 1,1 FROM Y"; 337 result.col1 = 0; 338 result.col2 = 0; 339 340 if ((ret = SPI_connect()) < 0) { 341 fprintf(stderr, "SPI_connect returned %d\n", ret); 342 exit(1); 343 } 344 ret = SPI_exec(query, 0); 345 if (ret == SPI_OK_SELECT && SPI_processed > 0) { 346 int i; 347 SPITupleTable *tuptable = SPI_tuptable; 348 TupleDesc tupdesc = tuptable->tupdesc; 349 for (i = 0; i < SPI_processed; i++) { 350 HeapTuple tuple = tuptable->vals[i]; 351 result.col1 = SPI_getbinval(tuple, tupdesc, 1, &isnull); 352 result.col2 = SPI_getbinval(tuple, tupdesc, 2, &isnull); 353 } 354 } 355 SPI_finish(); 356 PG_RETURN_TEXT_P(result); 357 } 358 */ 359 360 // SELECT X FROM Y WHERE Z=$1 361 /* 362 Datum 363 pg_spi_select_with_cond(PG_FUNCTION_ARGS) 364 { 365 int ret; 366 char *query; 367 int nargs; 368 Oid argtypes[1]; 369 Datum values[1]; 370 uint64 proc; 371 query = "SELECT col1 FROM Y WHERE col2 = $1"; 372 373 ret = SPI_connect(); 374 if (ret != SPI_OK_CONNECT) { 375 elog(ERROR, "SPI_connect failed: %d", ret); 376 } 377 nargs = 1; 378 argtypes[0] = INT4OID; 379 values[0] = Int32GetDatum(2); 380 381 ret = SPI_execute_with_args(query, nargs, argtypes, values, NULL, false, 0); 382 proc = SPI_processed; 383 if (ret != SPI_OK_SELECT) 384 { 385 elog(ERROR, "SPI_execute_with_args failed"); 386 } 387 388 SPI_finish(); 389 390 391 PG_RETURN_INT64(proc); 392 }*/ 393 394 ////////SELECT WITH COND 395 /* 396 Datum pg_spi_prepare_select_with_cond(PG_FUNCTION_ARGS) { 397 static SPIPlanPtr prepared_plan = NULL; 398 SPIPlanPtr new_plan; 399 int ret; 400 Datum values[1]; 401 uint64 proc; 402 int nargs; 403 Oid argtypes[1]; 404 char *query = "SELECT col1 FROM Y WHERE col1 = $1"; 405 int result = 0; 406 407 ret = SPI_connect(); 408 if (ret != SPI_OK_CONNECT) 409 elog(ERROR, "SPI_connect failed ! \n"); 410 411 if (prepared_plan == NULL) { 412 413 argtypes[0] = INT4OID; 414 nargs = 1; 415 values[0] = DatumGetByteaP(SPI_getbinval(tuptable->vals[0], tupdesc, 1, &isnull)); //Value col2 416 417 new_plan = SPI_prepare(query, nargs, argtypes); 418 if (new_plan == NULL) 419 elog(ERROR, "SPI_prepare failed ! \n"); 420 421 prepared_plan = SPI_saveplan(new_plan); 422 if (prepared_plan == NULL) 423 elog(ERROR, "SPI_saveplan failed ! \n"); 424 } 425 426 427 ret = SPI_execute_plan(prepared_plan, values, NULL, false, 0); 428 429 if (ret != SPI_OK_SELECT) { 430 elog(ERROR, "SPI_execute_plan failed: %d \n", ret); 431 } 432 433 proc = SPI_processed; 434 435 if (proc > 0) { 436 SPITupleTable *tuptable = SPI_tuptable; 437 TupleDesc tupdesc = tuptable->tupdesc; 438 HeapTuple tuple; 439 int i; 440 441 for (i = 0; i < proc; i++) { 442 tuple = tuptable->vals[i]; 443 for (int j = 1; j <= tupdesc->natts; j++) { 444 char * value = SPI_getvalue(tuple, tupdesc, j); 445 result += atoi(value); 446 } 447 } 448 } 449 SPI_finish(); 450 PG_RETURN_INT64(result); 451 } 452 */ 453 454 Datum 455 pg_spi_prepare_select_with_cond_without_saveplan (PG_FUNCTION_ARGS) 456 { 457 458 SPIPlanPtr new_plan; 459 int ret; 460 Datum values[1]; 461 uint64 proc; 462 int nargs; 463 Oid argtypes[1]; 464 char *query = "SELECT col1 FROM Y WHERE col2 = $1"; 465 int result = 0; 466 467 ret = SPI_connect (); 468 if (ret != SPI_OK_CONNECT) 469 elog (ERROR, "SPI_connect failed ! \n"); 470 471 { 472 473 argtypes[0] = INT4OID; 474 nargs = 1; 475 values[0] = Int32GetDatum (2); // Value col2 476 477 new_plan = SPI_prepare (query, nargs, argtypes); 478 if (new_plan == NULL) 479 elog (ERROR, "SPI_prepare failed ! \n"); 480 481 } 482 483 484 ret = SPI_execute_plan (new_plan, values, NULL, false, 0); 485 486 if (ret != SPI_OK_SELECT) 487 { 488 elog (ERROR, "SPI_execute_plan failed: %d \n", ret); 489 } 490 491 proc = SPI_processed; 492 493 if (proc > 0) 494 { 495 SPITupleTable *tuptable = SPI_tuptable; 496 TupleDesc tupdesc = tuptable->tupdesc; 497 HeapTuple tuple; 498 int i; 499 500 for (i = 0; i < proc; i++) 501 { 502 tuple = tuptable->vals[i]; 503 for (int j = 1; j <= tupdesc->natts; j++) 504 { 505 char *value = SPI_getvalue (tuple, tupdesc, j); 506 result += atoi (value); 507 } 508 } 509 } 510 SPI_finish (); 511 PG_RETURN_INT64 (result); 512 } 513 514 515 Datum 516 pg_spi_update_y (PG_FUNCTION_ARGS) 517 { 518 int ret; 519 int nargs; 520 Oid argtypes[1]; 521 Datum values[1]; 522 const char *query = "UPDATE Y SET col1 = 4 WHERE (col2 = $1)"; 523 524 ret = SPI_connect (); 525 if (ret != SPI_OK_CONNECT) 526 { 527 elog (ERROR, "SPI_connect failed ! \n"); 528 } 529 530 nargs = 1; 531 argtypes[0] = INT4OID; 532 values[0] = Int32GetDatum (0); 533 534 ret = SPI_execute_with_args (query, nargs, argtypes, values, NULL, false, 0); 535 if (ret != SPI_OK_UPDATE) 536 { 537 elog (ERROR, "SPI_execute_with_args failed ! \n"); 538 } 539 540 SPI_finish (); 541 542 PG_RETURN_VOID (); 543 } 544 545 546 Datum 547 pg_spi_prepare_update (PG_FUNCTION_ARGS) 548 { 549 static SPIPlanPtr prepared_plan = NULL; 550 SPIPlanPtr new_plan; 551 int ret; 552 int nargs; 553 Oid argtypes[1]; 554 Datum values[1]; 555 const char *query = "UPDATE Y SET col1 = 4 WHERE (col2 = $1)"; 556 557 ret = SPI_connect (); 558 if (ret != SPI_OK_CONNECT) 559 { 560 elog (ERROR, "SPI_connect failed ! \n"); 561 } 562 563 if (prepared_plan == NULL) 564 { 565 argtypes[0] = INT4OID; 566 nargs = 1; 567 values[0] = Int32GetDatum (3); 568 // PREPARE 569 new_plan = SPI_prepare (query, nargs, argtypes); 570 if (new_plan == NULL) 571 elog (ERROR, "SPI_prepare failed ! \n"); 572 // SAVEPLAN 573 prepared_plan = SPI_saveplan (new_plan); 574 if (prepared_plan == NULL) 575 elog (ERROR, "SPI_saveplan failed ! \n"); 576 } 577 ret = SPI_execute_plan (prepared_plan, values, NULL, false, 0); 578 if (ret != SPI_OK_UPDATE) 579 elog (ERROR, "SPI_execute_plan failed ! \n"); 580 581 SPI_finish (); 582 PG_RETURN_VOID (); 583 } 584 585 586 /* 587 Datum 588 pg_spi_prepare_update_without_saveplan(PG_FUNCTION_ARGS) 589 {}*/ 590 void 591 _PG_fini (void) 592 { 593 } 594 595 596 /* 597 598 */ 599 600 601 Datum 602 pg_spi_get_dep_ref_fees (PG_FUNCTION_ARGS) 603 { 604 /* Define plan to save */ 605 static SPIPlanPtr deposit_plan; 606 static SPIPlanPtr ref_plan; 607 static SPIPlanPtr fees_plan; 608 static SPIPlanPtr dummy_plan; 609 /* Define variables to update */ 610 Timestamp refund_deadline = PG_GETARG_TIMESTAMP (0); 611 bytea *merchant_pub = PG_GETARG_BYTEA_P (1); 612 bytea *wire_target_h_payto = PG_GETARG_BYTEA_P (2); 613 bytea *wtid_raw = PG_GETARG_BYTEA_P (3); 614 bool is_null; 615 /* Define variables to store the results of each SPI query */ 616 uint64_t sum_deposit_val = 0; 617 uint32_t sum_deposit_frac = 0; 618 uint64_t s_refund_val = 0; 619 uint32_t s_refund_frac = 0; 620 uint64_t sum_dep_fee_val = 0; 621 uint32_t sum_dep_fee_frac = 0; 622 uint64_t norm_refund_val = 0; 623 uint32_t norm_refund_frac = 0; 624 uint64_t sum_refund_val = 0; 625 uint32_t sum_refund_frac = 0; 626 /* Define variables to store the Tuptable */ 627 SPITupleTable *dep_res; 628 SPITupleTable *ref_res; 629 SPITupleTable *ref_by_coin_res; 630 SPITupleTable *norm_ref_by_coin_res; 631 SPITupleTable *fully_refunded_coins_res; 632 SPITupleTable *fees_res; 633 SPITupleTable *dummys_res; 634 /* Define variable to update */ 635 Datum values_refund[2]; 636 Datum values_deposit[3]; 637 Datum values_fees[2]; 638 Datum values_dummys[2]; 639 TupleDesc tupdesc; 640 /* Define variables to replace some tables */ 641 bytea *ref_by_coin_coin_pub; 642 int64 ref_by_coin_deposit_serial_id = 0; 643 bytea *norm_ref_by_coin_coin_pub; 644 int64_t norm_ref_by_coin_deposit_serial_id = 0; 645 bytea *new_dep_coin_pub = NULL; 646 int res = SPI_connect (); 647 648 /* Connect to SPI */ 649 if (res < 0) 650 { 651 elog (ERROR, "Could not connect to SPI manager"); 652 } 653 if (deposit_plan == NULL) 654 { 655 const char *dep_sql; 656 SPIPlanPtr new_plan; 657 658 // Execute first query and store results in variables 659 dep_sql = 660 "UPDATE deposits SET done=TRUE " 661 "WHERE NOT (done OR policy_blocked) " 662 "AND refund_deadline=$1 " 663 "AND merchant_pub=$2 " 664 "AND wire_target_h_payto=$3 " 665 "RETURNING " 666 "deposit_serial_id," 667 "coin_pub," 668 "amount_with_fee_val," 669 "amount_with_fee_frac;"; 670 fprintf (stderr, "dep sql %d\n", 1); 671 new_plan = 672 SPI_prepare (dep_sql, 4,(Oid[]){INT8OID, BYTEAOID, BYTEAOID}); 673 fprintf (stderr, "dep sql %d\n", 2); 674 if (new_plan == NULL) 675 elog (ERROR, "SPI_prepare failed for dep \n"); 676 deposit_plan = SPI_saveplan (new_plan); 677 if (deposit_plan == NULL) 678 elog (ERROR, "SPI_saveplan failed for dep \n"); 679 } 680 fprintf (stdout, "dep sql %d\n", 3); 681 682 values_deposit[0] = Int64GetDatum (refund_deadline); 683 values_deposit[1] = PointerGetDatum (merchant_pub); 684 values_deposit[2] = PointerGetDatum (wire_target_h_payto); 685 686 res = SPI_execute_plan (deposit_plan, 687 values_deposit, 688 NULL, 689 true, 690 0); 691 fprintf (stdout, "dep sql %d\n", 4); 692 if (res != SPI_OK_UPDATE) 693 { 694 elog (ERROR, "Failed to execute subquery 1 \n"); 695 } 696 // STORE TUPTABLE deposit 697 dep_res = SPI_tuptable; 698 699 for (unsigned int i = 0; i < SPI_processed; i++) 700 { 701 int64 dep_deposit_serial_ids = DatumGetInt64 (SPI_getbinval ( 702 SPI_tuptable->vals[i], 703 SPI_tuptable->tupdesc, 1, 704 &is_null)); 705 bytea *dep_coin_pub = DatumGetByteaP (SPI_getbinval (SPI_tuptable->vals[i], 706 SPI_tuptable->tupdesc, 707 2, &is_null)); 708 int64 dep_amount_val = DatumGetInt64 (SPI_getbinval (SPI_tuptable->vals[i], 709 SPI_tuptable->tupdesc, 710 3, &is_null)); 711 int32 dep_amount_frac = DatumGetInt32 (SPI_getbinval (SPI_tuptable->vals[i], 712 SPI_tuptable->tupdesc, 713 4, &is_null)); 714 715 if (is_null) 716 elog (ERROR, "Failed to retrieve data from deposit \n"); 717 if (ref_plan == NULL) 718 { 719 // Execute second query with parameters from first query and store results in variables 720 const char *ref_sql = 721 "SELECT amount_with_fee_val, amount_with_fee_frac, coin_pub, deposit_serial_id " 722 "FROM refunds " 723 "WHERE coin_pub=$1 " 724 "AND deposit_serial_id=$2;"; 725 SPIPlanPtr new_plan = SPI_prepare (ref_sql, 3, (Oid[]){BYTEAOID, 726 INT8OID}); 727 if (new_plan == NULL) 728 elog (ERROR, "SPI_prepare failed for refund\n"); 729 ref_plan = SPI_saveplan (new_plan); 730 if (ref_plan == NULL) 731 elog (ERROR, "SPI_saveplan failed for refund\n"); 732 } 733 values_refund[0] = PointerGetDatum (dep_coin_pub); 734 values_refund[1] = Int64GetDatum (dep_deposit_serial_ids); 735 res = SPI_execute_plan (ref_plan, 736 values_refund, 737 NULL, 738 false, 739 0); 740 if (res != SPI_OK_SELECT) 741 elog (ERROR, "Failed to execute subquery 2\n"); 742 // STORE TUPTABLE refund 743 ref_res = SPI_tuptable; 744 for (unsigned int j = 0; j < SPI_processed; j++) 745 { 746 int64 ref_refund_val = DatumGetInt64 (SPI_getbinval ( 747 SPI_tuptable->vals[j], 748 SPI_tuptable->tupdesc, 1, 749 &is_null)); 750 int32 ref_refund_frac = DatumGetInt32 (SPI_getbinval ( 751 SPI_tuptable->vals[j], 752 SPI_tuptable->tupdesc, 2, 753 &is_null)); 754 bytea *ref_coin_pub = DatumGetByteaP (SPI_getbinval ( 755 SPI_tuptable->vals[j], 756 SPI_tuptable->tupdesc, 3, 757 &is_null)); 758 int64 ref_deposit_serial_id = DatumGetInt64 (SPI_getbinval ( 759 SPI_tuptable->vals[j], 760 SPI_tuptable->tupdesc, 4, 761 &is_null)); 762 // Execute third query with parameters from second query and store results in variables 763 ref_by_coin_coin_pub = ref_coin_pub; 764 ref_by_coin_deposit_serial_id = ref_deposit_serial_id; 765 // LOOP TO GET THE SUM FROM REFUND BY COIN 766 for (unsigned int i = 0; i<SPI_processed; i++) 767 { 768 if ((ref_by_coin_coin_pub == 769 DatumGetByteaP (SPI_getbinval (SPI_tuptable->vals[i], 770 SPI_tuptable->tupdesc, 1, 771 &is_null))) 772 && 773 (ref_by_coin_deposit_serial_id == 774 DatumGetUInt64 (SPI_getbinval (SPI_tuptable->vals[i], 775 SPI_tuptable->tupdesc, 2, 776 &is_null))) 777 ) 778 { 779 sum_refund_val += ref_refund_val; 780 sum_refund_frac += ref_refund_frac; 781 norm_ref_by_coin_coin_pub = ref_by_coin_coin_pub; 782 norm_ref_by_coin_deposit_serial_id = ref_by_coin_deposit_serial_id; 783 } 784 }// END SUM CALCULATION 785 // NORMALIZE REFUND VAL FRAC 786 norm_refund_val = 787 (sum_refund_val + sum_refund_frac) / 100000000; 788 norm_refund_frac = 789 sum_refund_frac % 100000000; 790 // Get refund values 791 s_refund_val += sum_refund_val; 792 s_refund_frac = sum_refund_frac; 793 }// END REFUND 794 if (norm_ref_by_coin_coin_pub == dep_coin_pub 795 && ref_by_coin_deposit_serial_id == dep_deposit_serial_ids 796 && norm_refund_val == dep_amount_val 797 && norm_refund_frac == dep_amount_frac) 798 { 799 new_dep_coin_pub = dep_coin_pub; 800 } 801 // Ensure we get the fee for each coin and not only once per denomination 802 if (fees_plan == NULL) 803 { 804 const char *fees_sql = 805 "SELECT " 806 " denom.fee_deposit_val AS fee_val, " 807 " denom.fee_deposit_frac AS fee_frac, " 808 "FROM known_coins kc" 809 "JOIN denominations denom USING (denominations_serial) " 810 "WHERE kc.coin_pub = $1 AND kc.coin_pub != $2;"; 811 SPIPlanPtr new_plan = SPI_prepare (fees_sql, 3, (Oid[]){BYTEAOID, 812 BYTEAOID}); 813 if (new_plan == NULL) 814 { 815 elog (ERROR, "SPI_prepare for fees failed ! \n"); 816 } 817 fees_plan = SPI_saveplan (new_plan); 818 if (fees_plan == NULL) 819 { 820 elog (ERROR, "SPI_saveplan for fees failed ! \n"); 821 } 822 } 823 values_fees[0] = PointerGetDatum (dep_coin_pub); 824 values_fees[1] = PointerGetDatum (new_dep_coin_pub); 825 res = SPI_execute_plan (fees_plan, values_fees, NULL, false, 0); 826 if (res != SPI_OK_SELECT) 827 elog (ERROR, "SPI_execute_plan failed for fees \n"); 828 fees_res = SPI_tuptable; 829 tupdesc = fees_res->tupdesc; 830 for (unsigned int i = 0; i<SPI_processed; i++) 831 { 832 HeapTuple tuple = fees_res->vals[i]; 833 bool is_null; 834 uint64_t fee_val = DatumGetUInt64 (SPI_getbinval (tuple, tupdesc, 1, 835 &is_null)); 836 uint32_t fee_frac = DatumGetUInt32 (SPI_getbinval (tuple, tupdesc, 2, 837 &is_null)); 838 uint64_t fees_deposit_serial_id = DatumGetUInt64 (SPI_getbinval (tuple, 839 tupdesc, 840 3, 841 &is_null)); 842 if (dummy_plan == NULL) 843 { 844 const char *insert_dummy_sql = 845 "INSERT INTO " 846 "aggregation_tracking(deposit_serial_id, wtid_raw)" 847 " VALUES ($1, $2)"; 848 849 SPIPlanPtr new_plan = SPI_prepare (insert_dummy_sql, 2, (Oid[]){INT8OID, 850 BYTEAOID}); 851 if (new_plan == NULL) 852 elog (ERROR, "FAILED to prepare aggregation tracking \n"); 853 dummy_plan = SPI_saveplan (new_plan); 854 if (dummy_plan == NULL) 855 elog (ERROR, "FAILED to saveplan aggregation tracking\n"); 856 } 857 values_dummys[0] = Int64GetDatum (dep_deposit_serial_ids); 858 values_dummys[1] = PointerGetDatum (wtid_raw); 859 res = SPI_execute_plan (dummy_plan, values_dummys, NULL, false, 0); 860 if (res != SPI_OK_INSERT) 861 elog (ERROR, "Failed to insert dummy\n"); 862 dummys_res = SPI_tuptable; 863 // Calculation of deposit fees for not fully refunded deposits 864 sum_dep_fee_val += fee_val; 865 sum_dep_fee_frac += fee_frac; 866 } 867 // Get deposit values 868 sum_deposit_val += dep_amount_val; 869 sum_deposit_frac += dep_amount_frac; 870 }// END DEPOSIT 871 SPI_finish (); 872 PG_RETURN_VOID (); 873 }