char *
PGTYPESdate_to_asc(date dDate)
{
struct tm tt,
*tm = &tt;
char buf[MAXDATELEN + 1];
int DateStyle = 1;
bool EuroDates = FALSE;
j2date(dDate + date2j(2000, 1, 1), &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
EncodeDateOnly(tm, DateStyle, buf, EuroDates);
return pgtypes_strdup(buf);
}
static void date_to_json(Datum val, StringInfo dst)
{
struct pg_tm tm;
char buf[MAXDATELEN + 1];
DateADT date = DatumGetDateADT(val);
if (DATE_NOT_FINITE(date)) {
EncodeSpecialDate(date, buf);
} else {
j2date(date + POSTGRES_EPOCH_JDATE, &(tm.tm_year), &(tm.tm_mon), &(tm.tm_mday));
EncodeDateOnly(&tm, USE_XSD_DATES, buf);
}
appendStringInfo(dst, "\"%s\"", buf);
}
/* date_out()
* Given internal format date, convert to text string.
*/
char *
date_out(DateADT date)
{
char *result;
struct tm tt,
*tm = &tt;
char buf[MAXDATELEN + 1];
if (DATE_NOT_FINITE(date))
EncodeSpecialDate(date, buf);
else
{
j2date(date + POSTGRES_EPOCH_JDATE,
&(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
EncodeDateOnly(tm, DateStyle, buf);
}
result = strdup(buf);
return result;
}
/*
* Turn a Datum into jsonb, adding it to the result JsonbInState.
*
* tcategory and outfuncoid are from a previous call to json_categorize_type,
* except that if is_null is true then they can be invalid.
*
* If key_scalar is true, the value is stored as a key, so insist
* it's of an acceptable type, and force it to be a jbvString.
*/
static void
datum_to_jsonb(Datum val, bool is_null, JsonbInState *result,
JsonbTypeCategory tcategory, Oid outfuncoid,
bool key_scalar)
{
char *outputstr;
bool numeric_error;
JsonbValue jb;
bool scalar_jsonb = false;
check_stack_depth();
/* Convert val to a JsonbValue in jb (in most cases) */
if (is_null)
{
Assert(!key_scalar);
jb.type = jbvNull;
}
else if (key_scalar &&
(tcategory == JSONBTYPE_ARRAY ||
tcategory == JSONBTYPE_COMPOSITE ||
tcategory == JSONBTYPE_JSON ||
tcategory == JSONBTYPE_JSONB ||
tcategory == JSONBTYPE_JSONCAST))
{
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("key value must be scalar, not array, composite, or json")));
}
else
{
if (tcategory == JSONBTYPE_JSONCAST)
val = OidFunctionCall1(outfuncoid, val);
switch (tcategory)
{
case JSONBTYPE_ARRAY:
array_to_jsonb_internal(val, result);
break;
case JSONBTYPE_COMPOSITE:
composite_to_jsonb(val, result);
break;
case JSONBTYPE_BOOL:
if (key_scalar)
{
outputstr = DatumGetBool(val) ? "true" : "false";
jb.type = jbvString;
jb.val.string.len = strlen(outputstr);
jb.val.string.val = outputstr;
}
else
{
jb.type = jbvBool;
jb.val.boolean = DatumGetBool(val);
}
break;
case JSONBTYPE_NUMERIC:
outputstr = OidOutputFunctionCall(outfuncoid, val);
if (key_scalar)
{
/* always quote keys */
jb.type = jbvString;
jb.val.string.len = strlen(outputstr);
jb.val.string.val = outputstr;
}
else
{
/*
* Make it numeric if it's a valid JSON number, otherwise
* a string. Invalid numeric output will always have an
* 'N' or 'n' in it (I think).
*/
numeric_error = (strchr(outputstr, 'N') != NULL ||
strchr(outputstr, 'n') != NULL);
if (!numeric_error)
{
jb.type = jbvNumeric;
jb.val.numeric = DatumGetNumeric(DirectFunctionCall3(numeric_in, CStringGetDatum(outputstr), 0, -1));
pfree(outputstr);
}
else
{
jb.type = jbvString;
jb.val.string.len = strlen(outputstr);
jb.val.string.val = outputstr;
}
}
break;
case JSONBTYPE_DATE:
{
DateADT date;
struct pg_tm tm;
char buf[MAXDATELEN + 1];
date = DatumGetDateADT(val);
/* Same as date_out(), but forcing DateStyle */
if (DATE_NOT_FINITE(date))
EncodeSpecialDate(date, buf);
else
{
j2date(date + POSTGRES_EPOCH_JDATE,
&(tm.tm_year), &(tm.tm_mon), &(tm.tm_mday));
EncodeDateOnly(&tm, USE_XSD_DATES, buf);
}
jb.type = jbvString;
jb.val.string.len = strlen(buf);
jb.val.string.val = pstrdup(buf);
}
break;
case JSONBTYPE_TIMESTAMP:
{
Timestamp timestamp;
struct pg_tm tm;
fsec_t fsec;
char buf[MAXDATELEN + 1];
timestamp = DatumGetTimestamp(val);
/* Same as timestamp_out(), but forcing DateStyle */
if (TIMESTAMP_NOT_FINITE(timestamp))
EncodeSpecialTimestamp(timestamp, buf);
else if (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, NULL) == 0)
EncodeDateTime(&tm, fsec, false, 0, NULL, USE_XSD_DATES, buf);
else
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
jb.type = jbvString;
jb.val.string.len = strlen(buf);
jb.val.string.val = pstrdup(buf);
}
break;
case JSONBTYPE_TIMESTAMPTZ:
{
TimestampTz timestamp;
struct pg_tm tm;
int tz;
fsec_t fsec;
const char *tzn = NULL;
char buf[MAXDATELEN + 1];
timestamp = DatumGetTimestampTz(val);
/* Same as timestamptz_out(), but forcing DateStyle */
if (TIMESTAMP_NOT_FINITE(timestamp))
EncodeSpecialTimestamp(timestamp, buf);
else if (timestamp2tm(timestamp, &tz, &tm, &fsec, &tzn, NULL) == 0)
EncodeDateTime(&tm, fsec, true, tz, tzn, USE_XSD_DATES, buf);
else
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
jb.type = jbvString;
jb.val.string.len = strlen(buf);
jb.val.string.val = pstrdup(buf);
}
break;
case JSONBTYPE_JSONCAST:
case JSONBTYPE_JSON:
{
/* parse the json right into the existing result object */
JsonLexContext *lex;
JsonSemAction sem;
text *json = DatumGetTextP(val);
lex = makeJsonLexContext(json, true);
memset(&sem, 0, sizeof(sem));
sem.semstate = (void *) result;
sem.object_start = jsonb_in_object_start;
sem.array_start = jsonb_in_array_start;
sem.object_end = jsonb_in_object_end;
sem.array_end = jsonb_in_array_end;
sem.scalar = jsonb_in_scalar;
sem.object_field_start = jsonb_in_object_field_start;
pg_parse_json(lex, &sem);
}
break;
case JSONBTYPE_JSONB:
{
Jsonb *jsonb = DatumGetJsonb(val);
JsonbIterator *it;
it = JsonbIteratorInit(&jsonb->root);
if (JB_ROOT_IS_SCALAR(jsonb))
{
(void) JsonbIteratorNext(&it, &jb, true);
Assert(jb.type == jbvArray);
(void) JsonbIteratorNext(&it, &jb, true);
scalar_jsonb = true;
}
else
{
JsonbIteratorToken type;
while ((type = JsonbIteratorNext(&it, &jb, false))
!= WJB_DONE)
{
if (type == WJB_END_ARRAY || type == WJB_END_OBJECT ||
type == WJB_BEGIN_ARRAY || type == WJB_BEGIN_OBJECT)
result->res = pushJsonbValue(&result->parseState,
type, NULL);
else
result->res = pushJsonbValue(&result->parseState,
type, &jb);
}
}
}
break;
default:
outputstr = OidOutputFunctionCall(outfuncoid, val);
jb.type = jbvString;
jb.val.string.len = checkStringLen(strlen(outputstr));
jb.val.string.val = outputstr;
break;
}
}
/* Now insert jb into result, unless we did it recursively */
if (!is_null && !scalar_jsonb &&
tcategory >= JSONBTYPE_JSON && tcategory <= JSONBTYPE_JSONCAST)
{
/* work has been done recursively */
return;
}
else if (result->parseState == NULL)
{
/* single root scalar */
JsonbValue va;
va.type = jbvArray;
va.val.array.rawScalar = true;
va.val.array.nElems = 1;
result->res = pushJsonbValue(&result->parseState, WJB_BEGIN_ARRAY, &va);
result->res = pushJsonbValue(&result->parseState, WJB_ELEM, &jb);
result->res = pushJsonbValue(&result->parseState, WJB_END_ARRAY, NULL);
}
else
{
JsonbValue *o = &result->parseState->contVal;
switch (o->type)
{
case jbvArray:
result->res = pushJsonbValue(&result->parseState, WJB_ELEM, &jb);
break;
case jbvObject:
result->res = pushJsonbValue(&result->parseState,
key_scalar ? WJB_KEY : WJB_VALUE,
&jb);
break;
default:
elog(ERROR, "unexpected parent of nested structure");
}
}
}
/* Clause 3.3.1.3 */
Datum MarketWatchFrame1(PG_FUNCTION_ARGS)
{
int i;
int status = 0;
double old_mkt_cap = 0.0;
double new_mkt_cap = 0.0;
double pct_change = 0.0;
struct pg_tm tt, *tm = &tt;
int64 acct_id = PG_GETARG_INT64(0);
int64 cust_id = PG_GETARG_INT64(1);
int64 ending_co_id = PG_GETARG_INT64(2);
char *industry_name_p = (char *) PG_GETARG_TEXT_P(3);
DateADT start_date_p = PG_GETARG_DATEADT(4);
int64 starting_co_id = PG_GETARG_INT64(5);
int ret;
TupleDesc tupdesc;
SPITupleTable *tuptable = NULL;
HeapTuple tuple = NULL;
Datum result;
char buf[MAXDATELEN + 1];
char industry_name[IN_NAME_LEN + 1];
char sql[2048] = "";
j2date(start_date_p + POSTGRES_EPOCH_JDATE,
&(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
EncodeDateOnly(tm, DateStyle, buf);
strcpy(industry_name, DatumGetCString(DirectFunctionCall1(textout,
PointerGetDatum(industry_name_p))));
#ifdef DEBUG
dump_mwf1_inputs(acct_id, cust_id, ending_co_id, industry_name,
buf, starting_co_id);
#endif
SPI_connect();
if (cust_id != 0) {
sprintf(sql, MWF1_1, cust_id);
} else if (industry_name[0] != '\0') {
sprintf(sql, MWF1_2, industry_name, starting_co_id, ending_co_id);
} else if (acct_id != 0) {
sprintf(sql, MWF1_3, acct_id);
} else {
status = BAD_INPUT_DATA;
}
#ifdef DEBUG
elog(NOTICE, "SQL\n%s", sql);
#endif /* DEBUG */
ret = SPI_exec(sql, 0);
if (ret != SPI_OK_SELECT) {
dump_mwf1_inputs(acct_id, cust_id, ending_co_id, industry_name,
buf, starting_co_id);
FAIL_FRAME(sql);
}
if (status != BAD_INPUT_DATA) {
int count = SPI_processed;
TupleDesc tupdesc4;
SPITupleTable *tuptable4 = NULL;
HeapTuple tuple4 = NULL;
char *symbol;
char *new_price;
char *old_price;
char *s_num_out;
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
for (i = 0; i < count; i++) {
tuple = tuptable->vals[i];
symbol = SPI_getvalue(tuple, tupdesc, 1);
#ifdef DEBUG
elog(NOTICE, " symbol = '%s'", symbol);
#endif /* DEBUG */
sprintf(sql, MWF1_4, symbol);
#ifdef DEBUG
elog(NOTICE, "SQL\n%s", sql);
#endif /* DEBUG */
ret = SPI_exec(sql, 0);
if (ret != SPI_OK_SELECT || SPI_processed == 0) {
dump_mwf1_inputs(acct_id, cust_id, ending_co_id,
industry_name, buf, starting_co_id);
FAIL_FRAME(sql);
continue;
}
tupdesc4 = SPI_tuptable->tupdesc;
tuptable4 = SPI_tuptable;
tuple4 = tuptable4->vals[0];
new_price = SPI_getvalue(tuple4, tupdesc4, 1);
#ifdef DEBUG
elog(NOTICE, " new_price = %s", new_price);
elog(NOTICE, " new_price = %f", atof(new_price));
#endif /* DEBUG */
sprintf(sql, MWF1_5, symbol);
#ifdef DEBUG
elog(NOTICE, "SQL\n%s", sql);
#endif /* DEBUG */
ret = SPI_exec(sql, 0);
if (ret != SPI_OK_SELECT) {
dump_mwf1_inputs(acct_id, cust_id, ending_co_id,
industry_name, buf, starting_co_id);
elog(NOTICE, "ERROR: sql not ok = %d", ret);
}
tupdesc4 = SPI_tuptable->tupdesc;
tuptable4 = SPI_tuptable;
tuple4 = tuptable4->vals[0];
s_num_out = SPI_getvalue(tuple4, tupdesc4, 1);
#ifdef DEBUG
elog(NOTICE, " s_num_out = %s", s_num_out);
#endif /* DEBUG */
sprintf(sql, MWF1_6, symbol, pstrdup(buf));
#ifdef DEBUG
elog(NOTICE, "SQL\n%s", sql);
#endif /* DEBUG */
ret = SPI_exec(sql, 0);
if (ret != SPI_OK_SELECT) {
dump_mwf1_inputs(acct_id, cust_id, ending_co_id,
industry_name, buf, starting_co_id);
FAIL_FRAME(sql);
}
if (SPI_processed == 0) {
elog(NOTICE, "No rows returned for getting old_price.");
} else {
tupdesc4 = SPI_tuptable->tupdesc;
tuptable4 = SPI_tuptable;
tuple4 = tuptable4->vals[0];
old_price = SPI_getvalue(tuple4, tupdesc4, 1);
#ifdef DEBUG
elog(NOTICE, " old_price = %s", old_price);
elog(NOTICE, " old_price = %f", atof(old_price));
#endif /* DEBUG */
old_mkt_cap += atof(s_num_out) * atof(old_price);
}
new_mkt_cap += atof(s_num_out) * atof(new_price);
#ifdef DEBUG
elog(NOTICE, "old_mkt_cap = %f", old_mkt_cap);
elog(NOTICE, "new_mkt_cap = %f", new_mkt_cap);
#endif /* DEBUG */
}
pct_change = 100.0 * (new_mkt_cap / old_mkt_cap - 1.0);
#ifdef DEBUG
elog(NOTICE, "pct_change = %f", pct_change);
#endif /* DEBUG */
}
#ifdef DEBUG
elog(NOTICE, "MWF1 OUT: 1 %f", pct_change);
#endif /* DEBUG */
SPI_finish();
result = DirectFunctionCall1(float8_numeric, Float8GetDatum(pct_change));
PG_RETURN_NUMERIC(result);
}
