/* Clause 3.3.1.3 */ Datum MarketFeedFrame1(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; AttInMetadata *attinmeta; int call_cntr; int max_calls; int i, j, n; int num_updated = 0; int rows_sent; int send_len = 0; int count = 0; int nitems_pq; int *p_tq; char *p_s; char **values = NULL; /* Stuff done only on the first call of the function. */ if (SRF_IS_FIRSTCALL()) { MemoryContext oldcontext; ArrayType *price_quote_p = PG_GETARG_ARRAYTYPE_P(0); char *status_submitted_p = (char *) PG_GETARG_TEXT_P(1); ArrayType *symbol_p = PG_GETARG_ARRAYTYPE_P(2); ArrayType *trade_qty = PG_GETARG_ARRAYTYPE_P(3); char *type_limit_buy_p = (char *) PG_GETARG_TEXT_P(4); char *type_limit_sell_p = (char *) PG_GETARG_TEXT_P(5); char *type_stop_loss_p = (char *) PG_GETARG_TEXT_P(6); enum mff1 { i_num_updated=0, i_send_len, i_symbol, i_trade_id, i_price_quote, i_trade_qty, i_trade_type }; Datum *transdatums_pq; int16 typlen_s; bool typbyval_s; char typalign_s; int16 typlen_tq; bool typbyval_tq; char typalign_tq; int ret; TupleDesc tupdesc; SPITupleTable *tuptable = NULL; HeapTuple tuple = NULL; #ifdef DEBUG char sql[2048]; #endif Datum args[7]; char nulls[] = { ' ', ' ', ' ', ' ', ' ', ' ', ' ' }; char price_quote[S_PRICE_T_LEN + 1]; char status_submitted[ST_ID_LEN + 1]; char symbol[S_SYMB_LEN + 1]; char type_limit_buy[TT_ID_LEN + 1]; char type_limit_sell[TT_ID_LEN + 1]; char type_stop_loss[TT_ID_LEN + 1]; char *trade_id; char *req_price_quote; char *req_trade_type; char *req_trade_qty; /* * Prepare a values array for building the returned tuple. * This should be an array of C strings, which will * be processed later by the type input functions. */ values = (char **) palloc(sizeof(char *) * 7); values[i_num_updated] = (char *) palloc((INTEGER_LEN + 1) * sizeof(char)); values[i_send_len] = (char *) palloc((INTEGER_LEN + 1) * sizeof(char)); /* * FIXME: We don't know how many rows could be returned. The average * is supposed to be 4. Let's be prepared for 100, just to be safe. */ values[i_symbol] = (char *) palloc(((S_SYMB_LEN + 3) * 100 + 3) * sizeof(char)); values[i_trade_id] = (char *) palloc(((IDENT_T_LEN + 1) * 100 + 2) * sizeof(char)); values[i_price_quote] = (char *) palloc(((S_PRICE_T_LEN + 1) * 100 + 2) * sizeof(char)); values[i_trade_qty] = (char *) palloc(((INTEGER_LEN + 1) * 100 + 2) * sizeof(char)); values[i_trade_type] = (char *) palloc(((TT_ID_LEN + 3) * 100 + 3) * sizeof(char)); /* * This might be overkill since we always expect single dimensions * arrays. * Should probably check the count of all the arrays to make sure * they are the same... */ get_typlenbyvalalign(ARR_ELEMTYPE(symbol_p), &typlen_s, &typbyval_s, &typalign_s); p_s = ARR_DATA_PTR(symbol_p); get_typlenbyvalalign(ARR_ELEMTYPE(trade_qty), &typlen_tq, &typbyval_tq, &typalign_tq); p_tq = (int *) ARR_DATA_PTR(trade_qty); deconstruct_array(price_quote_p, NUMERICOID, -1, false, 'i', &transdatums_pq, NULL, &nitems_pq); strcpy(status_submitted, DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(status_submitted_p)))); strcpy(type_limit_buy, DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(type_limit_buy_p)))); strcpy(type_limit_sell, DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(type_limit_sell_p)))); strcpy(type_stop_loss, DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(type_stop_loss_p)))); #ifdef DEBUG dump_mff1_inputs(price_quote_p, status_submitted_p, symbol_p, trade_qty, type_limit_buy_p, type_limit_sell_p, type_stop_loss_p); #endif /* create a function context for cross-call persistence */ funcctx = SRF_FIRSTCALL_INIT(); /* switch to memory context appropriate for multiple function calls */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); SPI_connect(); plan_queries(MFF1_statements); strcpy(values[i_symbol], "{"); strcpy(values[i_trade_id], "{"); strcpy(values[i_price_quote], "{"); strcpy(values[i_trade_type], "{"); strcpy(values[i_trade_qty], "{"); for (i = 0; i < nitems_pq; i++) { rows_sent = 0; strcpy(price_quote, DatumGetCString(DirectFunctionCall1(numeric_out, transdatums_pq[i]))); strcpy(symbol, DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(p_s)))); /* FIXME: BEGIN/COMMIT statements not supported with SPI. */ /* ret = SPI_exec("BEGIN;", 0); if (ret == SPI_OK_SELECT) { } else { elog(NOTICE, "ERROR: BEGIN not ok = %d", ret); } */ #ifdef DEBUG sprintf(sql, SQLMFF1_1, DatumGetCString(DirectFunctionCall1(numeric_out, transdatums_pq[i])), p_tq[i], symbol); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ args[0] = Float8GetDatum(atof(price_quote)); args[1] = Int64GetDatum(p_tq[i]); args[2] = CStringGetTextDatum(symbol); ret = SPI_execute_plan(MFF1_1, args, nulls, false, 0); if (ret != SPI_OK_UPDATE) { dump_mff1_inputs(price_quote_p, status_submitted_p, symbol_p, trade_qty, type_limit_buy_p, type_limit_sell_p, type_stop_loss_p); FAIL_FRAME_SET(&funcctx->max_calls, MFF1_statements[0].sql); } num_updated += SPI_processed; #ifdef DEBUG elog(NOTICE, "%d row(s) updated", num_updated); sprintf(sql, SQLMFF1_2, symbol, type_stop_loss, price_quote, type_limit_sell, price_quote, type_limit_buy, price_quote); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ args[0] = CStringGetTextDatum(symbol); args[1] = CStringGetTextDatum(type_stop_loss); args[2] = Float8GetDatum(atof(price_quote)); args[3] = CStringGetTextDatum(type_limit_sell); args[4] = args[2]; args[5] = CStringGetTextDatum(type_limit_buy); args[6] = args[2]; ret = SPI_execute_plan(MFF1_2, args, nulls, true, 0); if (ret != SPI_OK_SELECT) { dump_mff1_inputs(price_quote_p, status_submitted_p, symbol_p, trade_qty, type_limit_buy_p, type_limit_sell_p, type_stop_loss_p); FAIL_FRAME_SET(&funcctx->max_calls, MFF1_statements[1].sql); continue; } #ifdef DEBUG elog(NOTICE, "%d row(s) returned", SPI_processed); #endif /* DEBUG */ tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; n = SPI_processed; for (j = 0; j < n; j++) { tuple = tuptable->vals[j]; trade_id = SPI_getvalue(tuple, tupdesc, 1); req_price_quote = SPI_getvalue(tuple, tupdesc, 2); req_trade_type = SPI_getvalue(tuple, tupdesc, 3); req_trade_qty = SPI_getvalue(tuple, tupdesc, 4); #ifdef DEBUG elog(NOTICE, "trade_id = %s", trade_id); sprintf(sql, SQLMFF1_3, status_submitted, trade_id); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ args[0] = CStringGetTextDatum(status_submitted); args[1] = Int64GetDatum(atoll(trade_id)); ret = SPI_execute_plan(MFF1_3, args, nulls, false, 0); if (ret != SPI_OK_UPDATE) { dump_mff1_inputs(price_quote_p, status_submitted_p, symbol_p, trade_qty, type_limit_buy_p, type_limit_sell_p, type_stop_loss_p); FAIL_FRAME_SET(&funcctx->max_calls, MFF1_statements[2].sql); } #ifdef DEBUG sprintf(sql, SQLMFF1_4, trade_id); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ args[0] = Int64GetDatum(atoll(trade_id)); ret = SPI_execute_plan(MFF1_4, args, nulls, false, 0); if (ret != SPI_OK_DELETE) { dump_mff1_inputs(price_quote_p, status_submitted_p, symbol_p, trade_qty, type_limit_buy_p, type_limit_sell_p, type_stop_loss_p); FAIL_FRAME_SET(&funcctx->max_calls, MFF1_statements[3].sql); } #ifdef DEBUG sprintf(sql, SQLMFF1_5, trade_id, status_submitted); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ args[1] = CStringGetTextDatum(status_submitted); ret = SPI_execute_plan(MFF1_5, args, nulls, false, 0); if (ret != SPI_OK_INSERT) { dump_mff1_inputs(price_quote_p, status_submitted_p, symbol_p, trade_qty, type_limit_buy_p, type_limit_sell_p, type_stop_loss_p); FAIL_FRAME_SET(&funcctx->max_calls, MFF1_statements[4].sql); } ++rows_sent; #ifdef DEBUG elog(NOTICE, "%d row(s) sent", rows_sent); #endif /* DEBUG */ if (count > 0) { strcat(values[i_symbol], ","); strcat(values[i_trade_id], ","); strcat(values[i_price_quote], ","); strcat(values[i_trade_type], ","); strcat(values[i_trade_qty], ","); } strcat(values[i_symbol], symbol); strcat(values[i_trade_id], trade_id); strcat(values[i_price_quote], req_price_quote); strcat(values[i_trade_type], req_trade_type); strcat(values[i_trade_qty], req_trade_qty); ++count; } /* FIXME: BEGIN/COMMIT statements not supported with SPI. */ /* ret = SPI_exec("COMMIT;", 0); if (ret == SPI_OK_SELECT) { } else { elog(NOTICE, "ERROR: COMMIT not ok = %d", ret); } */ send_len += rows_sent; p_s = att_addlength_pointer(p_s, typlen_s, p_s); p_s = (char *) att_align_nominal(p_s, typalign_s); } strcat(values[i_symbol], "}"); strcat(values[i_trade_id], "}"); strcat(values[i_price_quote], "}"); strcat(values[i_trade_qty], "}"); strcat(values[i_trade_type], "}"); sprintf(values[i_num_updated], "%d", num_updated); sprintf(values[i_send_len], "%d", send_len); funcctx->max_calls = 1; /* Build a tuple descriptor for our result type */ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) { ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("function returning record called in context " "that cannot accept type record"))); } /* * generate attribute metadata needed later to produce tuples from raw * C strings */ attinmeta = TupleDescGetAttInMetadata(tupdesc); funcctx->attinmeta = attinmeta; MemoryContextSwitchTo(oldcontext); } /* stuff done on every call of the function */ funcctx = SRF_PERCALL_SETUP(); call_cntr = funcctx->call_cntr; max_calls = funcctx->max_calls; attinmeta = funcctx->attinmeta; if (call_cntr < max_calls) { /* do when there is more left to send */ HeapTuple tuple; Datum result; #ifdef DEBUG for (i = 0; i < 7; i++) { elog(NOTICE, "MFF1 OUT: %d %s", i, values[i]); } #endif /* DEBUG */ /* Build a tuple. */ tuple = BuildTupleFromCStrings(attinmeta, values); /* Make the tuple into a datum. */ result = HeapTupleGetDatum(tuple); SRF_RETURN_NEXT(funcctx, result); } else { /* Do when there is no more left. */ SPI_finish(); SRF_RETURN_DONE(funcctx); } }
/* Clause 3.3.9.3 */ Datum TradeStatusFrame1(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; AttInMetadata *attinmeta; int call_cntr; int max_calls; int i; char **values = NULL; /* Stuff done only on the first call of the function. */ if (SRF_IS_FIRSTCALL()) { enum tsf1 { i_broker_name=0, i_charge, i_cust_f_name, i_cust_l_name, i_ex_name, i_exec_name, i_num_found, i_s_name, i_status_name, i_symbol, i_trade_dts, i_trade_id, i_trade_qty, i_type_name }; long acct_id = PG_GETARG_INT64(0); int ret; TupleDesc tupdesc; SPITupleTable *tuptable = NULL; HeapTuple tuple = NULL; #ifdef DEBUG char sql[2048]; #endif Datum args[1]; char nulls[1] = { ' ' }; /* * Prepare a values array for building the returned tuple. * This should be an array of C strings, which will * be processed later by the type input functions. */ values = (char **) palloc(sizeof(char *) * 14); values[i_charge] = (char *) palloc((VALUE_T_LEN + 1) * sizeof(char) * 50); values[i_ex_name] = (char *) palloc((EX_NAME_LEN + 3) * sizeof(char) * 50); values[i_exec_name] = (char *) palloc((T_EXEC_NAME_LEN + 3) * sizeof(char) * 50); values[i_num_found] = (char *) palloc((BIGINT_LEN + 1) * sizeof(char)); values[i_s_name] = (char *) palloc((S_NAME_LEN + 3) * sizeof(char) * 50); values[i_status_name] = (char *) palloc((ST_NAME_LEN + 3) * sizeof(char) * 50); values[i_symbol] = (char *) palloc((S_SYMB_LEN + 3) * sizeof(char) * 50); values[i_trade_dts] = (char *) palloc((MAXDATELEN + 1) * sizeof(char) * 50); values[i_trade_id] = (char *) palloc((BIGINT_LEN + 1) * sizeof(char) * 50); values[i_trade_qty] = (char *) palloc((INTEGER_LEN + 1) * sizeof(char) * 50); values[i_type_name] = (char *) palloc((TT_NAME_LEN + 3) * sizeof(char) * 50); values[i_cust_l_name] = NULL; values[i_cust_f_name] = NULL; values[i_broker_name] = NULL; #ifdef DEBUG dump_tsf1_inputs(acct_id); #endif /* create a function context for cross-call persistence */ funcctx = SRF_FIRSTCALL_INIT(); funcctx->max_calls = 1; /* switch to memory context appropriate for multiple function calls */ TSF1_savedcxt = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); SPI_connect(); plan_queries(TSF1_statements); #ifdef DEBUG sprintf(sql, SQLTSF1_1, acct_id); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ args[0] = Int64GetDatum(acct_id); ret = SPI_execute_plan(TSF1_1, args, nulls, true, 0); if (ret == SPI_OK_SELECT) { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; } else { FAIL_FRAME_SET(&funcctx->max_calls, TSF1_statements[0].sql); dump_tsf1_inputs(acct_id); } sprintf(values[i_num_found], "%d", SPI_processed); strcpy(values[i_trade_id], "{"); strcpy(values[i_trade_dts], "{"); strcpy(values[i_status_name], "{"); strcpy(values[i_type_name], "{"); strcpy(values[i_symbol], "{"); strcpy(values[i_trade_qty], "{"); strcpy(values[i_exec_name], "{"); strcpy(values[i_charge], "{"); strcpy(values[i_s_name], "{"); strcpy(values[i_ex_name], "{"); for (i = 0; i < SPI_processed; i++) { tuple = tuptable->vals[i]; if (i > 0) { strcat(values[i_trade_id], ","); strcat(values[i_trade_dts], ","); strcat(values[i_status_name], ","); strcat(values[i_type_name], ","); strcat(values[i_symbol], ","); strcat(values[i_trade_qty], ","); strcat(values[i_exec_name], ","); strcat(values[i_charge], ","); strcat(values[i_s_name], ","); strcat(values[i_ex_name], ","); } strcat(values[i_trade_id], SPI_getvalue(tuple, tupdesc, 1)); strcat(values[i_trade_dts], SPI_getvalue(tuple, tupdesc, 2)); strcat(values[i_status_name], "\""); strcat(values[i_status_name], SPI_getvalue(tuple, tupdesc, 3)); strcat(values[i_status_name], "\""); strcat(values[i_type_name], "\""); strcat(values[i_type_name], SPI_getvalue(tuple, tupdesc, 4)); strcat(values[i_type_name], "\""); strcat(values[i_symbol], "\""); strcat(values[i_symbol], SPI_getvalue(tuple, tupdesc, 5)); strcat(values[i_symbol], "\""); strcat(values[i_trade_qty], SPI_getvalue(tuple, tupdesc, 6)); strcat(values[i_exec_name], "\""); strcat(values[i_exec_name], SPI_getvalue(tuple, tupdesc, 7)); strcat(values[i_exec_name], "\""); strcat(values[i_charge], SPI_getvalue(tuple, tupdesc, 8)); strcat(values[i_s_name], "\""); strcat(values[i_s_name], SPI_getvalue(tuple, tupdesc, 9)); strcat(values[i_s_name], "\""); strcat(values[i_ex_name], "\""); strcat(values[i_ex_name], SPI_getvalue(tuple, tupdesc, 10)); strcat(values[i_ex_name], "\""); } strcat(values[i_trade_id], "}"); strcat(values[i_trade_dts], "}"); strcat(values[i_status_name], "}"); strcat(values[i_type_name], "}"); strcat(values[i_symbol], "}"); strcat(values[i_trade_qty], "}"); strcat(values[i_exec_name], "}"); strcat(values[i_charge], "}"); strcat(values[i_s_name], "}"); strcat(values[i_ex_name], "}"); #ifdef DEBUG sprintf(sql, SQLTSF1_2, acct_id); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ ret = SPI_execute_plan(TSF1_2, args, nulls, true, 0); if (ret == SPI_OK_SELECT) { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; if (SPI_processed > 0) { tuple = tuptable->vals[0]; values[i_cust_l_name] = SPI_getvalue(tuple, tupdesc, 1); values[i_cust_f_name] = SPI_getvalue(tuple, tupdesc, 2); values[i_broker_name] = SPI_getvalue(tuple, tupdesc, 3); } } else { FAIL_FRAME_SET(&funcctx->max_calls, TSF1_statements[1].sql); dump_tsf1_inputs(acct_id); } /* Build a tuple descriptor for our result type */ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) { ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("function returning record called in context " "that cannot accept type record"))); } /* * generate attribute metadata needed later to produce tuples from raw * C strings */ attinmeta = TupleDescGetAttInMetadata(tupdesc); funcctx->attinmeta = attinmeta; MemoryContextSwitchTo(TSF1_savedcxt); } /* stuff done on every call of the function */ funcctx = SRF_PERCALL_SETUP(); call_cntr = funcctx->call_cntr; max_calls = funcctx->max_calls; attinmeta = funcctx->attinmeta; if (call_cntr < max_calls) { /* do when there is more left to send */ HeapTuple tuple; Datum result; #ifdef DEBUG for (i = 0; i < 14; i++) { elog(NOTICE, "TSF1 OUT: %d %s", i, values[i]); } #endif /* DEBUG */ /* Build a tuple. */ tuple = BuildTupleFromCStrings(attinmeta, values); /* Make the tuple into a datum. */ result = HeapTupleGetDatum(tuple); SRF_RETURN_NEXT(funcctx, result); } else { /* Do when there is no more left. */ SPI_finish(); if (TSF1_savedcxt) MemoryContextSwitchTo(TSF1_savedcxt); SRF_RETURN_DONE(funcctx); } }
Datum payment(PG_FUNCTION_ARGS) { /* Input variables. */ int32 w_id = PG_GETARG_INT32(0); int32 d_id = PG_GETARG_INT32(1); int32 c_id = PG_GETARG_INT32(2); int32 c_w_id = PG_GETARG_INT32(3); int32 c_d_id = PG_GETARG_INT32(4); text *c_last = PG_GETARG_TEXT_P(5); float4 h_amount = PG_GETARG_FLOAT4(6); TupleDesc tupdesc; SPITupleTable *tuptable; HeapTuple tuple; int ret; char *w_name = NULL; char *w_street_1 = NULL; char *w_street_2 = NULL; char *w_city = NULL; char *w_state = NULL; char *w_zip = NULL; char *d_name = NULL; char *d_street_1 = NULL; char *d_street_2 = NULL; char *d_city = NULL; char *d_state = NULL; char *d_zip = NULL; char *tmp_c_id = NULL; int my_c_id = 0; int count; char *c_first = NULL; char *c_middle = NULL; char *my_c_last = NULL; char *c_street_1 = NULL; char *c_street_2 = NULL; char *c_city = NULL; char *c_state = NULL; char *c_zip = NULL; char *c_phone = NULL; char *c_since = NULL; char *c_credit = NULL; char *c_credit_lim = NULL; char *c_discount = NULL; char *c_balance = NULL; char *c_data = NULL; char *c_ytd_payment = NULL; Datum args[8]; char nulls[8] = { ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ' }; elog(DEBUG1, "w_id = %d", w_id); elog(DEBUG1, "d_id = %d", d_id); elog(DEBUG1, "c_id = %d", c_id); elog(DEBUG1, "c_w_id = %d", c_w_id); elog(DEBUG1, "c_d_id = %d", c_d_id); elog(DEBUG1, "c_last = %s", DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(c_last)))); elog(DEBUG1, "h_amount = %f", h_amount); SPI_connect(); plan_queries(statements); args[0] = Int32GetDatum(w_id); ret = SPI_execute_plan(PAYMENT_1, args, nulls, true, 0); if (ret == SPI_OK_SELECT && SPI_processed > 0) { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; tuple = tuptable->vals[0]; w_name = SPI_getvalue(tuple, tupdesc, 1); w_street_1 = SPI_getvalue(tuple, tupdesc, 2); w_street_2 = SPI_getvalue(tuple, tupdesc, 3); w_city = SPI_getvalue(tuple, tupdesc, 4); w_state = SPI_getvalue(tuple, tupdesc, 5); w_zip = SPI_getvalue(tuple, tupdesc, 6); elog(DEBUG1, "w_name = %s", w_name); elog(DEBUG1, "w_street_1 = %s", w_street_1); elog(DEBUG1, "w_street_2 = %s", w_street_2); elog(DEBUG1, "w_city = %s", w_city); elog(DEBUG1, "w_state = %s", w_state); elog(DEBUG1, "w_zip = %s", w_zip); } else { SPI_finish(); PG_RETURN_INT32(-1); } args[0] = Float4GetDatum(h_amount); args[1] = Int32GetDatum(w_id); ret = SPI_execute_plan(PAYMENT_2, args, nulls, false, 0); if (ret != SPI_OK_UPDATE) { SPI_finish(); PG_RETURN_INT32(-1); } args[0] = Int32GetDatum(d_id); args[1] = Int32GetDatum(w_id); ret = SPI_execute_plan(PAYMENT_3, args, nulls, true, 0); if (ret == SPI_OK_SELECT && SPI_processed > 0) { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; tuple = tuptable->vals[0]; d_name = SPI_getvalue(tuple, tupdesc, 1); d_street_1 = SPI_getvalue(tuple, tupdesc, 2); d_street_2 = SPI_getvalue(tuple, tupdesc, 3); d_city = SPI_getvalue(tuple, tupdesc, 4); d_state = SPI_getvalue(tuple, tupdesc, 5); d_zip = SPI_getvalue(tuple, tupdesc, 6); elog(DEBUG1, "d_name = %s", d_name); elog(DEBUG1, "d_street_1 = %s", d_street_1); elog(DEBUG1, "d_street_2 = %s", d_street_2); elog(DEBUG1, "d_city = %s", d_city); elog(DEBUG1, "d_state = %s", d_state); elog(DEBUG1, "d_zip = %s", d_zip); } else { SPI_finish(); PG_RETURN_INT32(-1); } args[0] = Float4GetDatum(h_amount); args[1] = Int32GetDatum(d_id); args[2] = Int32GetDatum(w_id); ret = SPI_execute_plan(PAYMENT_4, args, nulls, false, 0); if (ret != SPI_OK_UPDATE) { SPI_finish(); PG_RETURN_INT32(-1); } if (c_id == 0) { args[0] = Int32GetDatum(w_id); args[1] = Int32GetDatum(d_id); args[2] = PointerGetDatum(c_last); ret = SPI_execute_plan(PAYMENT_5, args, nulls, true, 0); count = SPI_processed; if (ret == SPI_OK_SELECT && SPI_processed > 0) { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; tuple = tuptable->vals[count / 2]; tmp_c_id = SPI_getvalue(tuple, tupdesc, 1); elog(DEBUG1, "c_id = %s, %d total, selected %d", tmp_c_id, count, count / 2); my_c_id = atoi(tmp_c_id); } else { SPI_finish(); PG_RETURN_INT32(-1); } } else { my_c_id = c_id; } args[0] = Int32GetDatum(c_w_id); args[1] = Int32GetDatum(c_d_id); args[2] = Int32GetDatum(my_c_id); ret = SPI_execute_plan(PAYMENT_6, args, nulls, true, 0); if (ret == SPI_OK_SELECT && SPI_processed > 0) { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; tuple = tuptable->vals[0]; c_first = SPI_getvalue(tuple, tupdesc, 1); c_middle = SPI_getvalue(tuple, tupdesc, 2); my_c_last = SPI_getvalue(tuple, tupdesc, 3); c_street_1 = SPI_getvalue(tuple, tupdesc, 4); c_street_2 = SPI_getvalue(tuple, tupdesc, 5); c_city = SPI_getvalue(tuple, tupdesc, 6); c_state = SPI_getvalue(tuple, tupdesc, 7); c_zip = SPI_getvalue(tuple, tupdesc, 8); c_phone = SPI_getvalue(tuple, tupdesc, 9); c_since = SPI_getvalue(tuple, tupdesc, 10); c_credit = SPI_getvalue(tuple, tupdesc, 11); c_credit_lim = SPI_getvalue(tuple, tupdesc, 12); c_discount = SPI_getvalue(tuple, tupdesc, 13); c_balance = SPI_getvalue(tuple, tupdesc, 14); c_data = SPI_getvalue(tuple, tupdesc, 15); c_ytd_payment = SPI_getvalue(tuple, tupdesc, 16); elog(DEBUG1, "c_first = %s", c_first); elog(DEBUG1, "c_middle = %s", c_middle); elog(DEBUG1, "c_last = %s", my_c_last); elog(DEBUG1, "c_street_1 = %s", c_street_1); elog(DEBUG1, "c_street_2 = %s", c_street_2); elog(DEBUG1, "c_city = %s", c_city); elog(DEBUG1, "c_state = %s", c_state); elog(DEBUG1, "c_zip = %s", c_zip); elog(DEBUG1, "c_phone = %s", c_phone); elog(DEBUG1, "c_since = %s", c_since); elog(DEBUG1, "c_credit = %s", c_credit); elog(DEBUG1, "c_credit_lim = %s", c_credit_lim); elog(DEBUG1, "c_discount = %s", c_discount); elog(DEBUG1, "c_balance = %s", c_balance); elog(DEBUG1, "c_data = %s", c_data); elog(DEBUG1, "c_ytd_payment = %s", c_ytd_payment); } else { SPI_finish(); PG_RETURN_INT32(-1); } /* It's either "BC" or "GC". */ if (c_credit[0] == 'G') { args[0] = Float4GetDatum(h_amount); args[1] = Int32GetDatum(my_c_id); args[2] = Int32GetDatum(c_w_id); args[3] = Int32GetDatum(c_d_id); ret = SPI_execute_plan(PAYMENT_7_GC, args, nulls, false, 0); if (ret != SPI_OK_UPDATE) { SPI_finish(); PG_RETURN_INT32(-1); } } else { char my_c_data[1000]; sprintf(my_c_data, "%d %d %d %d %d %f ", my_c_id, c_d_id, c_w_id, d_id, w_id, h_amount); args[0] = Float4GetDatum(h_amount); args[1] = CStringGetTextDatum(my_c_data); args[2] = Int32GetDatum(my_c_id); args[3] = Int32GetDatum(c_w_id); args[4] = Int32GetDatum(c_d_id); ret = SPI_execute_plan(PAYMENT_7_BC, args, nulls, false, 0); if (ret != SPI_OK_UPDATE) { SPI_finish(); PG_RETURN_INT32(-1); } } args[0] = Int32GetDatum(my_c_id); args[1] = Int32GetDatum(c_d_id); args[2] = Int32GetDatum(c_w_id); args[3] = Int32GetDatum(d_id); args[4] = Int32GetDatum(w_id); args[5] = Float4GetDatum(h_amount); args[6] = CStringGetTextDatum(w_name); args[7] = CStringGetTextDatum(d_name); ret = SPI_execute_plan(PAYMENT_8, args, nulls, false, 0); if (ret != SPI_OK_INSERT) { SPI_finish(); PG_RETURN_INT32(-1); } SPI_finish(); PG_RETURN_INT32(1); }
Datum stock_level(PG_FUNCTION_ARGS) { /* Input variables. */ int32 w_id = PG_GETARG_INT32(0); int32 d_id = PG_GETARG_INT32(1); int32 threshold = PG_GETARG_INT32(2); TupleDesc tupdesc; SPITupleTable *tuptable; HeapTuple tuple; int d_next_o_id = 0; int low_stock = 0; int ret; char *buf; Datum args[5]; char nulls[5] = { ' ', ' ', ' ', ' ', ' ' }; SPI_connect(); plan_queries(statements); args[0] = Int32GetDatum(w_id); args[1] = Int32GetDatum(d_id); ret = SPI_execute_plan(STOCK_LEVEL_1, args, nulls, true, 0); if (ret == SPI_OK_SELECT && SPI_processed > 0) { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; tuple = tuptable->vals[0]; buf = SPI_getvalue(tuple, tupdesc, 1); elog(DEBUG1, "d_next_o_id = %s", buf); d_next_o_id = atoi(buf); } else { SPI_finish(); PG_RETURN_INT32(-1); } args[0] = Int32GetDatum(w_id); args[1] = Int32GetDatum(d_id); args[2] = Int32GetDatum(threshold); args[3] = Int32GetDatum(d_next_o_id - 20); args[4] = Int32GetDatum(d_next_o_id - 1); ret = SPI_execute_plan(STOCK_LEVEL_2, args, nulls, true, 0); if (ret == SPI_OK_SELECT && SPI_processed > 0) { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; tuple = tuptable->vals[0]; buf = SPI_getvalue(tuple, tupdesc, 1); elog(DEBUG1, "low_stock = %s", buf); low_stock = atoi(buf); } else { SPI_finish(); PG_RETURN_INT32(-1); } SPI_finish(); PG_RETURN_INT32(low_stock); }
/* Clause 3.3.9.4 */ Datum TradeUpdateFrame2(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; AttInMetadata *attinmeta; int call_cntr; int max_calls; int i; int j; char **values = NULL; /* Stuff done only on the first call of the function. */ if (SRF_IS_FIRSTCALL()) { MemoryContext oldcontext; enum tuf2 { i_bid_price=0, i_cash_transaction_amount, i_cash_transaction_dts, i_cash_transaction_name, i_exec_name, i_is_cash, i_num_found, i_num_updated, i_settlement_amount, i_settlement_cash_due_date, i_settlement_cash_type, i_trade_history_dts, i_trade_history_status_id, i_trade_list, i_trade_price }; long acct_id = PG_GETARG_INT64(0); Timestamp end_trade_dts_ts = PG_GETARG_TIMESTAMP(1); int max_trades = PG_GETARG_INT32(2); int max_updates = PG_GETARG_INT32(3); Timestamp start_trade_dts_ts = PG_GETARG_TIMESTAMP(4); int ret; TupleDesc tupdesc; SPITupleTable *tuptable = NULL; HeapTuple tuple = NULL; struct pg_tm tt, *tm = &tt; fsec_t fsec; char *tzn = NULL; #ifdef DEBUG char sql[2048]; #endif Datum args[4]; char nulls[4] = { ' ', ' ', ' ', ' ' }; char end_trade_dts[MAXDATELEN + 1]; char start_trade_dts[MAXDATELEN + 1]; int num_found; int num_updated = 0; int num_cash = 0; if (timestamp2tm(end_trade_dts_ts, NULL, tm, &fsec, NULL, NULL) == 0) { EncodeDateTimeM(tm, fsec, tzn, end_trade_dts); } if (timestamp2tm(start_trade_dts_ts, NULL, tm, &fsec, NULL, NULL) == 0) { EncodeDateTimeM(tm, fsec, tzn, start_trade_dts); } #ifdef DEBUG dump_tuf2_inputs(acct_id, end_trade_dts, max_trades, max_updates, start_trade_dts); #endif /* * Prepare a values array for building the returned tuple. * This should be an array of C strings, which will * be processed later by the type input functions. * Don't forget to factor in commas (,) and braces ({}) for the arrays. */ values = (char **) palloc(sizeof(char *) * 15); values[i_bid_price] = (char *) palloc(((S_PRICE_T_LEN + 1) * 20 + 2) * sizeof(char)); values[i_cash_transaction_amount] = (char *) palloc(((VALUE_T_LEN + 1) * 20 + 2) * sizeof(char)); values[i_cash_transaction_dts] = (char *) palloc(((MAXDATELEN + 1) * 20 + 2) * sizeof(char)); values[i_cash_transaction_name] = (char *) palloc(((CT_NAME_LEN + 3) * 20 + 2) * sizeof(char)); values[i_exec_name] = (char *) palloc(((T_EXEC_NAME_LEN + 3) * 20 + 2) * sizeof(char)); values[i_is_cash] = (char *) palloc(((SMALLINT_LEN + 1) * 20 + 2) * sizeof(char)); values[i_num_found] = (char *) palloc((INTEGER_LEN + 1) * sizeof(char)); values[i_num_updated] = (char *) palloc((INTEGER_LEN + 1) * sizeof(char)); values[i_settlement_amount] = (char *) palloc(((VALUE_T_LEN + 1) * 20 + 2) * sizeof(char)); values[i_settlement_cash_due_date] = (char *) palloc(((MAXDATELEN + 1) * 20 + 2) * sizeof(char)); values[i_settlement_cash_type] = (char *) palloc(((SE_CASH_TYPE_LEN + 3) * 20 + 2) * sizeof(char)); values[i_trade_history_dts] = (char *) palloc((((MAXDATELEN + 2) * 3 + 2) * 20 + 5) * sizeof(char)); values[i_trade_history_status_id] = (char *) palloc((((ST_ID_LEN + 2) * 3 + 2) * 20 + 5) * sizeof(char)); values[i_trade_list] = (char *) palloc(((BIGINT_LEN + 1) * 20 + 2) * sizeof(char)); values[i_trade_price] = (char *) palloc(((S_PRICE_T_LEN + 1) * 20 + 2) * sizeof(char)); /* create a function context for cross-call persistence */ funcctx = SRF_FIRSTCALL_INIT(); funcctx->max_calls = 1; /* switch to memory context appropriate for multiple function calls */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); SPI_connect(); plan_queries(TUF2_statements); #ifdef DEBUG sprintf(sql, SQLTUF2_1, acct_id, start_trade_dts, end_trade_dts, max_trades); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ args[0] = Int64GetDatum(acct_id); args[1] = TimestampGetDatum(start_trade_dts_ts); args[2] = TimestampGetDatum(end_trade_dts_ts); args[3] = Int32GetDatum(max_trades); ret = SPI_execute_plan(TUF2_1, args, nulls, true, 0); if (ret == SPI_OK_SELECT && SPI_processed > 0) { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; } num_found = SPI_processed; strcpy(values[i_bid_price], "{"); strcpy(values[i_exec_name], "{"); strcpy(values[i_is_cash], "{"); strcpy(values[i_trade_list], "{"); strcpy(values[i_trade_price], "{"); strcpy(values[i_settlement_amount], "{"); strcpy(values[i_settlement_cash_due_date], "{"); strcpy(values[i_settlement_cash_type], "{"); strcpy(values[i_cash_transaction_amount], "{"); strcpy(values[i_cash_transaction_dts], "{"); strcpy(values[i_cash_transaction_name], "{"); strcpy(values[i_trade_history_dts], "{"); strcpy(values[i_trade_history_status_id], "{"); for (i = 0; i < num_found; i++) { TupleDesc l_tupdesc; SPITupleTable *l_tuptable = NULL; HeapTuple l_tuple = NULL; char *is_cash_str; char *trade_list; char cash_type[41]; tuple = tuptable->vals[i]; if (i > 0) { strcat(values[i_bid_price], ","); strcat(values[i_exec_name], ","); strcat(values[i_is_cash], ","); strcat(values[i_trade_list], ","); strcat(values[i_trade_price], ","); strcat(values[i_settlement_amount], ","); strcat(values[i_settlement_cash_due_date], ","); strcat(values[i_settlement_cash_type], ","); strcat(values[i_trade_history_dts], ","); strcat(values[i_trade_history_status_id], ","); } strcat(values[i_bid_price], SPI_getvalue(tuple, tupdesc, 1)); strcat(values[i_exec_name], "\""); strcat(values[i_exec_name], SPI_getvalue(tuple, tupdesc, 2)); strcat(values[i_exec_name], "\""); is_cash_str = SPI_getvalue(tuple, tupdesc, 3); strcat(values[i_is_cash], (is_cash_str[0] == 't' ? "0" : "1")); trade_list = SPI_getvalue(tuple, tupdesc, 4); strcat(values[i_trade_list], trade_list); strcat(values[i_trade_price], SPI_getvalue(tuple, tupdesc, 5)); if (num_updated < max_updates) { char *old_cash_type; #ifdef DEBUG sprintf(sql, SQLTUF2_2, trade_list); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ args[0] = Int64GetDatum(atoll(trade_list)); ret = SPI_execute_plan(TUF2_2, args, nulls, true, 0); if (ret == SPI_OK_SELECT && SPI_processed > 0) { l_tupdesc = SPI_tuptable->tupdesc; l_tuptable = SPI_tuptable; l_tuple = l_tuptable->vals[0]; old_cash_type = SPI_getvalue(l_tuple, l_tupdesc, 1); } else { FAIL_FRAME_SET(&funcctx->max_calls, TUF2_statements[1].sql); dump_tuf2_inputs(acct_id, end_trade_dts, max_trades, max_updates, start_trade_dts); continue; } #ifdef DEBUG elog(NOTICE, "cash_type = '%s'", old_cash_type); #endif /* DEBUG */ if (is_cash_str[0] == 't') { if (strcmp(old_cash_type, "Cash Account") == 0) { strcpy(cash_type, "Cash"); } else { strcpy(cash_type, "Cash Account"); } } else { if (strcmp(old_cash_type, "Margin Account") == 0) { strcpy(cash_type, "Margin"); } else { strcpy(cash_type, "Margin Account"); } } #ifdef DEBUG sprintf(sql, SQLTUF2_3, cash_type, trade_list); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ args[0] = CStringGetTextDatum(cash_type); args[1] = Int64GetDatum(atoll(trade_list)); ret = SPI_execute_plan(TUF2_3, args, nulls, false, 0); if (ret != SPI_OK_UPDATE) { FAIL_FRAME_SET(&funcctx->max_calls, TUF2_statements[2].sql); dump_tuf2_inputs(acct_id, end_trade_dts, max_trades, max_updates, start_trade_dts); continue; } num_updated += SPI_processed; } #ifdef DEBUG sprintf(sql, SQLTUF2_4, trade_list); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ args[0] = Int64GetDatum(atoll(trade_list)); ret = SPI_execute_plan(TUF2_4, args, nulls, true, 0); if (ret == SPI_OK_SELECT && SPI_processed > 0) { l_tupdesc = SPI_tuptable->tupdesc; l_tuptable = SPI_tuptable; l_tuple = l_tuptable->vals[0]; strcat(values[i_settlement_amount], SPI_getvalue(l_tuple, l_tupdesc, 1)); strcat(values[i_settlement_cash_due_date], SPI_getvalue(l_tuple, l_tupdesc, 2)); strcat(values[i_settlement_cash_type], "\""); strcat(values[i_settlement_cash_type], SPI_getvalue(l_tuple, l_tupdesc, 3)); strcat(values[i_settlement_cash_type], "\""); } else { FAIL_FRAME_SET(&funcctx->max_calls, TUF2_statements[3].sql); dump_tuf2_inputs(acct_id, end_trade_dts, max_trades, max_updates, start_trade_dts); continue; } if (is_cash_str[0] == 't') { #ifdef DEBUG sprintf(sql, SQLTUF2_5, trade_list); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ ret = SPI_execute_plan(TUF2_5, args, nulls, true, 0); if (ret == SPI_OK_SELECT && SPI_processed > 0) { l_tupdesc = SPI_tuptable->tupdesc; l_tuptable = SPI_tuptable; l_tuple = l_tuptable->vals[0]; if (num_cash > 0) { strcat(values[i_cash_transaction_amount], ","); strcat(values[i_cash_transaction_dts], ","); strcat(values[i_cash_transaction_name], ","); } strcat(values[i_cash_transaction_amount], SPI_getvalue(l_tuple, l_tupdesc, 1)); strcat(values[i_cash_transaction_dts], SPI_getvalue(l_tuple, l_tupdesc, 2)); strcat(values[i_cash_transaction_name], "\""); strcat(values[i_cash_transaction_name], SPI_getvalue(l_tuple, l_tupdesc, 3)); strcat(values[i_cash_transaction_name], "\""); ++num_cash; } else { FAIL_FRAME_SET(&funcctx->max_calls, TUF2_statements[4].sql); dump_tuf2_inputs(acct_id, end_trade_dts, max_trades, max_updates, start_trade_dts); continue; } } else { if (num_cash > 0) { strcat(values[i_cash_transaction_amount], ","); strcat(values[i_cash_transaction_dts], ","); strcat(values[i_cash_transaction_name], ","); } strcat(values[i_cash_transaction_amount], "0"); strcat(values[i_cash_transaction_dts], "1970-1-1 0:0:0"); strcat(values[i_cash_transaction_name], "\"\""); ++num_cash; } #ifdef DEBUG sprintf(sql, SQLTUF2_6, trade_list); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ ret = SPI_execute_plan(TUF2_6, args, nulls, true, 0); if (ret == SPI_OK_SELECT && SPI_processed > 0) { l_tupdesc = SPI_tuptable->tupdesc; l_tuptable = SPI_tuptable; } else { FAIL_FRAME_SET(&funcctx->max_calls, TUF2_statements[5].sql); dump_tuf2_inputs(acct_id, end_trade_dts, max_trades, max_updates, start_trade_dts); continue; } strcat(values[i_trade_history_dts], "{"); strcat(values[i_trade_history_status_id], "{"); for (j = 0; j < SPI_processed; j ++) { if (j > 0) { strcat(values[i_trade_history_dts], ","); strcat(values[i_trade_history_status_id], ","); } l_tuple = l_tuptable->vals[j]; strcat(values[i_trade_history_dts], SPI_getvalue(l_tuple, l_tupdesc, 1)); strcat(values[i_trade_history_status_id], "\""); strcat(values[i_trade_history_status_id], SPI_getvalue(l_tuple, l_tupdesc, 2)); strcat(values[i_trade_history_status_id], "\""); } for (j = SPI_processed; j < 3; j++) { if (j > 0) { strcat(values[i_trade_history_dts], ","); strcat(values[i_trade_history_status_id], ","); } strcat(values[i_trade_history_dts], "NULL"); strcat(values[i_trade_history_status_id], "\"\""); } strcat(values[i_trade_history_dts], "}"); strcat(values[i_trade_history_status_id], "}"); } strcat(values[i_bid_price], "}"); strcat(values[i_exec_name], "}"); strcat(values[i_is_cash], "}"); strcat(values[i_trade_list], "}"); strcat(values[i_trade_price], "}"); strcat(values[i_settlement_amount], "}"); strcat(values[i_settlement_cash_due_date], "}"); strcat(values[i_settlement_cash_type], "}"); strcat(values[i_cash_transaction_amount], "}"); strcat(values[i_cash_transaction_dts], "}"); strcat(values[i_cash_transaction_name], "}"); strcat(values[i_trade_history_dts], "}"); strcat(values[i_trade_history_status_id], "}"); sprintf(values[i_num_found], "%d", num_found); sprintf(values[i_num_updated], "%d", num_updated); /* Build a tuple descriptor for our result type */ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) { ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("function returning record called in context " "that cannot accept type record"))); } /* * generate attribute metadata needed later to produce tuples from raw * C strings */ attinmeta = TupleDescGetAttInMetadata(tupdesc); funcctx->attinmeta = attinmeta; MemoryContextSwitchTo(oldcontext); } /* stuff done on every call of the function */ funcctx = SRF_PERCALL_SETUP(); call_cntr = funcctx->call_cntr; max_calls = funcctx->max_calls; attinmeta = funcctx->attinmeta; if (call_cntr < max_calls) { /* do when there is more left to send */ HeapTuple tuple; Datum result; #ifdef DEBUG for (i = 0; i < 15; i++) { elog(NOTICE, "TUF2 OUT: %d %s", i, values[i]); } #endif /* DEBUG */ /* Build a tuple. */ tuple = BuildTupleFromCStrings(attinmeta, values); /* Make the tuple into a datum. */ result = HeapTupleGetDatum(tuple); SRF_RETURN_NEXT(funcctx, result); } else { /* Do when there is no more left. */ SPI_finish(); SRF_RETURN_DONE(funcctx); } }
/* Clause 3.3.9.3 */ Datum TradeUpdateFrame1(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; AttInMetadata *attinmeta; int call_cntr; int max_calls; int i; int j; char **values = NULL; /* Stuff done only on the first call of the function. */ if (SRF_IS_FIRSTCALL()) { MemoryContext oldcontext; enum tuf1 { i_bid_price=0, i_cash_transaction_amount, i_cash_transaction_dts, i_cash_transaction_name, i_exec_name, i_is_cash, i_is_market, i_num_found, i_num_updated, i_settlement_amount, i_settlement_cash_due_date, i_settlement_cash_type, i_trade_history_dts, i_trade_history_status_id, i_trade_price }; int max_trades = PG_GETARG_INT32(0); int max_updates = PG_GETARG_INT32(1); ArrayType *trade_id_p = PG_GETARG_ARRAYTYPE_P(2); int16 typlen; bool typbyval; char typalign; int ndim, nitems; int *dim; long *trade_id; int ret; TupleDesc tupdesc; SPITupleTable *tuptable = NULL; HeapTuple tuple = NULL; #ifdef DEBUG char sql[2048]; #endif Datum args[2]; char nulls[2] = { ' ', ' ' }; int num_found = max_trades; int num_updated = 0; int num_updated4 = 0; int num_updated5 = 0; int num_updated7 = 0; int num_cash = 0; ndim = ARR_NDIM(trade_id_p); dim = ARR_DIMS(trade_id_p); nitems = ArrayGetNItems(ndim, dim); get_typlenbyvalalign(ARR_ELEMTYPE(trade_id_p), &typlen, &typbyval, &typalign); trade_id = (long *) ARR_DATA_PTR(trade_id_p); /* * Prepare a values array for building the returned tuple. * This should be an array of C strings, which will * be processed later by the type input functions. * Don't forget to factor in commas (,) and braces ({}) for the arrays. */ values = (char **) palloc(sizeof(char *) * 15); values[i_bid_price] = (char *) palloc(((S_PRICE_T_LEN + 1) * 20 + 2) * sizeof(char)); values[i_cash_transaction_amount] = (char *) palloc(((VALUE_T_LEN + 1) * 20 + 2) * sizeof(char)); values[i_cash_transaction_dts] = (char *) palloc(((MAXDATELEN + 1) * 20 + 2) * sizeof(char)); values[i_cash_transaction_name] = (char *) palloc(((CT_NAME_LEN + 3) * 20 + 2) * sizeof(char)); values[i_exec_name] = (char *) palloc(((T_EXEC_NAME_LEN + 3) * 20 + 2) * sizeof(char)); values[i_is_cash] = (char *) palloc(((SMALLINT_LEN + 1) * 20 + 2) * sizeof(char)); values[i_is_market] = (char *) palloc(((SMALLINT_LEN + 1) * 20 + 2) * sizeof(char)); values[i_num_found] = (char *) palloc((INTEGER_LEN + 1) * sizeof(char)); values[i_num_updated] = (char *) palloc((INTEGER_LEN + 1) * sizeof(char)); values[i_settlement_amount] = (char *) palloc(((VALUE_T_LEN + 1) * 20 + 2) * sizeof(char)); values[i_settlement_cash_due_date] = (char *) palloc(((MAXDATELEN + 1) * 20 + 2) * sizeof(char)); values[i_settlement_cash_type] = (char *) palloc(((SE_CASH_TYPE_LEN + 3) * 20 + 2) * sizeof(char)); values[i_trade_history_dts] = (char *) palloc(((MAXDATELEN * 3 + 4) * 20 + 22) * sizeof(char)); values[i_trade_history_status_id] = (char *) palloc((((ST_ID_LEN + 2) * 3 + 4) * 20 + 22) * sizeof(char)); values[i_trade_price] = (char *) palloc(((S_PRICE_T_LEN + 1) * 20 + 2) * sizeof(char)); #ifdef DEBUG dump_tuf1_inputs(max_trades, max_updates, trade_id); #endif /* create a function context for cross-call persistence */ funcctx = SRF_FIRSTCALL_INIT(); funcctx->max_calls = 1; /* switch to memory context appropriate for multiple function calls */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); SPI_connect(); plan_queries(TUF1_statements); strcpy(values[i_bid_price], "{"); strcpy(values[i_exec_name], "{"); strcpy(values[i_is_cash], "{"); strcpy(values[i_is_market], "{"); strcpy(values[i_trade_price], "{"); strcpy(values[i_settlement_amount], "{"); strcpy(values[i_settlement_cash_due_date], "{"); strcpy(values[i_settlement_cash_type], "{"); strcpy(values[i_cash_transaction_amount], "{"); strcpy(values[i_cash_transaction_dts], "{"); strcpy(values[i_cash_transaction_name], "{"); strcpy(values[i_trade_history_dts], "{"); strcpy(values[i_trade_history_status_id], "{"); for (i = 0; i < max_trades; i++) { char *is_cash_str = NULL; char *is_market_str; if (num_updated < max_updates) { char *ex_name; #ifdef DEBUG sprintf(sql, SQLTUF1_1, trade_id[i]); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ args[0] = Int64GetDatum(trade_id[i]); ret = SPI_execute_plan(TUF1_1, args, nulls, true, 0); if (ret == SPI_OK_SELECT && SPI_processed > 0) { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; tuple = tuptable->vals[0]; ex_name = SPI_getvalue(tuple, tupdesc, 1); } else { continue; } #ifdef DEBUG elog(NOTICE, "ex_name = %s", ex_name); if (strstr(ex_name, " X ")) { sprintf(sql, SQLTUF1_2a, ex_name); } else { sprintf(sql, SQLTUF1_2b, ex_name); } elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ args[0] = CStringGetTextDatum(ex_name); if (strstr(ex_name, " X ")) { ret = SPI_execute_plan(TUF1_2a, args, nulls, true, 0); } else { ret = SPI_execute_plan(TUF1_2b, args, nulls, true, 0); } if (ret == SPI_OK_SELECT && SPI_processed > 0) { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; tuple = tuptable->vals[0]; ex_name = SPI_getvalue(tuple, tupdesc, 1); } else { FAIL_FRAME_SET(&funcctx->max_calls, strstr(ex_name, " X ")? TUF1_statements[1].sql: TUF1_statements[2].sql); dump_tuf1_inputs(max_trades, max_updates, trade_id); continue; } #ifdef DEBUG elog(NOTICE, "ex_name = %s", ex_name); sprintf(sql, SQLTUF1_3, ex_name, trade_id[i]); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ args[1] = Int64GetDatum(trade_id[i]); ret = SPI_execute_plan(TUF1_3, args, nulls, false, 0); if (ret != SPI_OK_UPDATE) { FAIL_FRAME_SET(&funcctx->max_calls, TUF1_statements[4].sql); dump_tuf1_inputs(max_trades, max_updates, trade_id); continue; } num_updated += SPI_processed; } #ifdef DEBUG sprintf(sql, SQLTUF1_4, trade_id[i]); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ args[0] = Int64GetDatum(trade_id[i]); ret = SPI_execute_plan(TUF1_4, args, nulls, true, 0); if (ret != SPI_OK_SELECT) { FAIL_FRAME_SET(&funcctx->max_calls, TUF1_statements[5].sql); dump_tuf1_inputs(max_trades, max_updates, trade_id); continue; } tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; for (j = 0; j < SPI_processed; j++) { char *trade_price; if (num_updated4 > 0) { strcat(values[i_bid_price], ","); strcat(values[i_exec_name], ","); strcat(values[i_is_cash], ","); strcat(values[i_is_market], ","); strcat(values[i_trade_price], ","); } tuple = tuptable->vals[j]; strcat(values[i_bid_price], SPI_getvalue(tuple, tupdesc, 1)); strcat(values[i_exec_name], "\""); strcat(values[i_exec_name], SPI_getvalue(tuple, tupdesc, 2)); strcat(values[i_exec_name], "\""); is_cash_str = SPI_getvalue(tuple, tupdesc, 3); strcat(values[i_is_cash], (is_cash_str[0] == 't' ? "1": "0")); is_market_str = SPI_getvalue(tuple, tupdesc, 4); strcat(values[i_is_market], (is_market_str[0] == 't' ? "0" : "1")); trade_price = SPI_getvalue(tuple, tupdesc, 5); if (trade_price != NULL) strcat(values[i_trade_price], SPI_getvalue(tuple, tupdesc, 5)); else strcat(values[i_trade_price], "NULL"); num_updated4++; } #ifdef DEBUG sprintf(sql, SQLTUF1_5, trade_id[i]); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ ret = SPI_execute_plan(TUF1_5, args, nulls, true, 0); if (ret != SPI_OK_SELECT) { FAIL_FRAME_SET(&funcctx->max_calls, TUF1_statements[6].sql); dump_tuf1_inputs(max_trades, max_updates, trade_id); continue; } tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; for (j = 0; j < SPI_processed; j++) { if (num_updated5 > 0) { strcat(values[i_settlement_amount], ","); strcat(values[i_settlement_cash_due_date], ","); strcat(values[i_settlement_cash_type], ","); } tuple = tuptable->vals[j]; strcat(values[i_settlement_amount], SPI_getvalue(tuple, tupdesc, 1)); strcat(values[i_settlement_cash_due_date], SPI_getvalue(tuple, tupdesc, 2)); strcat(values[i_settlement_cash_type], "\""); strcat(values[i_settlement_cash_type], SPI_getvalue(tuple, tupdesc, 3)); strcat(values[i_settlement_cash_type], "\""); num_updated5++; } if (is_cash_str[0] == 't') { if (num_cash > 0) { strcat(values[i_cash_transaction_amount], ","); strcat(values[i_cash_transaction_dts], ","); strcat(values[i_cash_transaction_name], ","); } #ifdef DEBUG sprintf(sql, SQLTUF1_6, trade_id[i]); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ ret = SPI_execute_plan(TUF1_6, args, nulls, true, 0); if (ret == SPI_OK_SELECT && SPI_processed > 0) { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; tuple = tuptable->vals[0]; strcat(values[i_cash_transaction_amount], SPI_getvalue(tuple, tupdesc, 1)); strcat(values[i_cash_transaction_dts], SPI_getvalue(tuple, tupdesc, 2)); strcat(values[i_cash_transaction_name], "\""); strcat(values[i_cash_transaction_name], SPI_getvalue(tuple, tupdesc, 3)); strcat(values[i_cash_transaction_name], "\""); ++num_cash; } else { FAIL_FRAME_SET(&funcctx->max_calls, TUF1_statements[7].sql); dump_tuf1_inputs(max_trades, max_updates, trade_id); continue; } } #ifdef DEBUG sprintf(sql, SQLTUF1_7, trade_id[i]); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ ret = SPI_execute_plan(TUF1_7, args, nulls, true, 0); if (ret == SPI_OK_SELECT) { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; } else { FAIL_FRAME_SET(&funcctx->max_calls, TUF1_statements[8].sql); dump_tuf1_inputs(max_trades, max_updates, trade_id); continue; } if (num_updated7 > 0) { strcat(values[i_trade_history_dts], ","); strcat(values[i_trade_history_status_id], ","); } strcat(values[i_trade_history_dts], "{"); strcat(values[i_trade_history_status_id], "{"); for (j = 0; j < SPI_processed; j++) { if (j > 0) { strcat(values[i_trade_history_dts], ","); strcat(values[i_trade_history_status_id], ","); } tuple = tuptable->vals[j]; strcat(values[i_trade_history_dts], SPI_getvalue(tuple, tupdesc, 1)); strcat(values[i_trade_history_status_id], "\""); strcat(values[i_trade_history_status_id], SPI_getvalue(tuple, tupdesc, 2)); strcat(values[i_trade_history_status_id], "\""); num_updated7++; } for (j = SPI_processed; j < 3; j++) { if (j > 0) { strcat(values[i_trade_history_dts], ","); strcat(values[i_trade_history_status_id], ","); } strcat(values[i_trade_history_dts], "NULL"); strcat(values[i_trade_history_status_id], "\"\""); num_updated7++; } strcat(values[i_trade_history_dts], "}"); strcat(values[i_trade_history_status_id], "}"); } strcat(values[i_bid_price], "}"); strcat(values[i_exec_name], "}"); strcat(values[i_is_cash], "}"); strcat(values[i_is_market], "}"); strcat(values[i_trade_price], "}"); strcat(values[i_settlement_amount], "}"); strcat(values[i_settlement_cash_due_date], "}"); strcat(values[i_settlement_cash_type], "}"); strcat(values[i_cash_transaction_amount], "}"); strcat(values[i_cash_transaction_dts], "}"); strcat(values[i_cash_transaction_name], "}"); strcat(values[i_trade_history_dts], "}"); strcat(values[i_trade_history_status_id], "}"); sprintf(values[i_num_found], "%d", num_found); sprintf(values[i_num_updated], "%d", num_updated); /* Build a tuple descriptor for our result type */ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) { ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("function returning record called in context " "that cannot accept type record"))); } /* * generate attribute metadata needed later to produce tuples from raw * C strings */ attinmeta = TupleDescGetAttInMetadata(tupdesc); funcctx->attinmeta = attinmeta; MemoryContextSwitchTo(oldcontext); } /* stuff done on every call of the function */ funcctx = SRF_PERCALL_SETUP(); call_cntr = funcctx->call_cntr; max_calls = funcctx->max_calls; attinmeta = funcctx->attinmeta; if (call_cntr < max_calls) { /* do when there is more left to send */ HeapTuple tuple; Datum result; #ifdef DEBUG for (i = 0; i < 15; i++) { elog(NOTICE, "TUF1 OUT: %d %s", i, values[i]); } #endif /* DEBUG */ /* Build a tuple. */ tuple = BuildTupleFromCStrings(attinmeta, values); /* Make the tuple into a datum. */ result = HeapTupleGetDatum(tuple); SRF_RETURN_NEXT(funcctx, result); } else { /* Do when there is no more left. */ SPI_finish(); SRF_RETURN_DONE(funcctx); } }
/* Clause 3.3.1.3 */ Datum BrokerVolumeFrame1(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; AttInMetadata *attinmeta; int call_cntr; int max_calls; int i; int ndim, nitems; int *dim; char *broker_list; char **values = NULL; /* Stuff done only on the first call of the function. */ if (SRF_IS_FIRSTCALL()) { MemoryContext oldcontext; ArrayType *broker_list_p = PG_GETARG_ARRAYTYPE_P(0); text *sector_name_p = PG_GETARG_TEXT_P(1); enum bvf1 { i_broker_name=0, i_list_len, i_volume }; int16 typlen; bool typbyval; char typalign; int ret; TupleDesc tupdesc; SPITupleTable *tuptable = NULL; HeapTuple tuple = NULL; #ifdef DEBUG char sql[2048]; #endif char broker_list_array[(B_NAME_LEN + 3) * 40 + 5] = "'{"; Datum args[2]; char nulls[2] = { ' ', ' ' }; /* * Prepare a values array for building the returned tuple. * This should be an array of C strings, which will * be processed later by the type input functions. */ values = (char **) palloc(sizeof(char *) * 3); values[i_list_len] = (char *) palloc((SMALLINT_LEN + 1) * sizeof(char)); /* * This might be overkill since we always expect single dimensions * arrays. */ ndim = ARR_NDIM(broker_list_p); dim = ARR_DIMS(broker_list_p); nitems = ArrayGetNItems(ndim, dim); get_typlenbyvalalign(ARR_ELEMTYPE(broker_list_p), &typlen, &typbyval, &typalign); broker_list = ARR_DATA_PTR(broker_list_p); /* Turn the broker_list input into an array format. */ if (nitems > 0) { strcat(broker_list_array, "\""); strcat(broker_list_array, DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(broker_list)))); broker_list = att_addlength_pointer(broker_list, typlen, broker_list); broker_list = (char *) att_align_nominal(broker_list, typalign); strcat(broker_list_array, "\""); } for (i = 1; i < nitems; i++) { strcat(broker_list_array, ",\""); strcat(broker_list_array, DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(broker_list)))); broker_list = att_addlength_pointer(broker_list, typlen, broker_list); broker_list = (char *) att_align_nominal(broker_list, typalign); strcat(broker_list_array, "\""); } strcat(broker_list_array, "}'"); #ifdef DEBUG dump_bvf1_inputs(broker_list_p, sector_name_p); #endif /* create a function context for cross-call persistence */ funcctx = SRF_FIRSTCALL_INIT(); funcctx->max_calls = 1; /* switch to memory context appropriate for multiple function calls */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); SPI_connect(); plan_queries(BVF1_statements); #ifdef DEBUG sprintf(sql, SQLBVF1_1, broker_list_array, DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(sector_name_p)))); elog(NOTICE, "SQL\n%s", sql); #endif /* DEBUG */ args[0] = PointerGetDatum(broker_list_p); args[1] = PointerGetDatum(sector_name_p); ret = SPI_execute_plan(BVF1_1, args, nulls, true, 0); if (ret == SPI_OK_SELECT) { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; tuple = tuptable->vals[0]; } else { dump_bvf1_inputs(broker_list_p, sector_name_p); FAIL_FRAME_SET(&funcctx->max_calls, BVF1_statements[0].sql); } sprintf(values[i_list_len], "%d", SPI_processed); values[i_broker_name] = (char *) palloc(((B_NAME_LEN + 2) * (SPI_processed + 1) + 3) * sizeof(char)); values[i_volume] = (char *) palloc((INTEGER_LEN * (SPI_processed + 1) + 3) * sizeof(char)); if (SPI_processed == 0) { strcpy(values[i_broker_name], "{}"); strcpy(values[i_volume], "{}"); } else { strcpy(values[i_broker_name], "{"); strcpy(values[i_volume], "{"); if (SPI_processed > 0) { strcat(values[i_broker_name], "\""); strcat(values[i_broker_name], SPI_getvalue(tuple, tupdesc, 1)); strcat(values[i_broker_name], "\""); strcat(values[i_volume], SPI_getvalue(tuple, tupdesc, 2)); } for (i = 1; i < SPI_processed; i++) { tuple = tuptable->vals[i]; strcat(values[i_broker_name], ","); strcat(values[i_broker_name], "\""); strcat(values[i_broker_name], SPI_getvalue(tuple, tupdesc, 1)); strcat(values[i_broker_name], "\""); strcat(values[i_volume], ","); strcat(values[i_volume], SPI_getvalue(tuple, tupdesc, 2)); } strcat(values[i_broker_name], "}"); strcat(values[i_volume], "}"); } /* Build a tuple descriptor for our result type */ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) { ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("function returning record called in context " "that cannot accept type record"))); } /* * generate attribute metadata needed later to produce tuples from raw * C strings */ attinmeta = TupleDescGetAttInMetadata(tupdesc); funcctx->attinmeta = attinmeta; MemoryContextSwitchTo(oldcontext); } /* stuff done on every call of the function */ funcctx = SRF_PERCALL_SETUP(); call_cntr = funcctx->call_cntr; max_calls = funcctx->max_calls; attinmeta = funcctx->attinmeta; if (call_cntr < max_calls) { /* do when there is more left to send */ HeapTuple tuple; Datum result; #ifdef DEBUG for (i = 0; i < 3; i++) { elog(NOTICE, "BVF1 OUT: %d %s", i, values[i]); } #endif /* DEBUG */ /* Build a tuple. */ tuple = BuildTupleFromCStrings(attinmeta, values); /* Make the tuple into a datum. */ result = HeapTupleGetDatum(tuple); SRF_RETURN_NEXT(funcctx, result); } else { /* Do when there is no more left. */ SPI_finish(); SRF_RETURN_DONE(funcctx); } }