/* 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);
}
}
