Datum
add_months(PG_FUNCTION_ARGS)
{
DateADT day = PG_GETARG_DATEADT(0);
int n = PG_GETARG_INT32(1);
int y, m, d;
int days;
DateADT result;
div_t v;
bool last_day;
j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);
last_day = (d == days_of_month(y, m));
v = div(y * 12 + m - 1 + n, 12);
y = v.quot;
if (y < 0)
y += 1; /* offset because of year 0 */
m = v.rem + 1;
days = days_of_month(y, m);
if (last_day || d > days)
d = days;
result = date2j(y, m, d) - POSTGRES_EPOCH_JDATE;
PG_RETURN_DATEADT (result);
}
/* Decode a date type */
static int
decode_date(const char *buffer, unsigned int buff_size, unsigned int *out_size)
{
const char *new_buffer = (const char *) TYPEALIGN(sizeof(int32), (uintptr_t) buffer);
unsigned int delta = (unsigned int) ((uintptr_t) new_buffer - (uintptr_t) buffer);
int32 jd,
year,
month,
day;
if (buff_size < delta)
return -1;
buff_size -= delta;
buffer = new_buffer;
if (buff_size < sizeof(int32))
return -2;
*out_size = sizeof(int32) + delta;
jd = *(int32 *) buffer + POSTGRES_EPOCH_JDATE;
j2date(jd, &year, &month, &day);
CopyAppendFmt("%04d-%02d-%02d%s", (year <= 0) ? -year + 1 : year, month, day, (year <= 0) ? " BC" : "");
return 0;
}
Datum
plvdate_isbizday (PG_FUNCTION_ARGS)
{
DateADT day = PG_GETARG_DATEADT(0);
int y, m, d;
holiday_desc hd;
if (0 != ((1 << j2day(day+POSTGRES_EPOCH_JDATE)) & nonbizdays))
return false;
if (NULL != bsearch(&day, exceptions, exceptions_c,
sizeof(DateADT), dateadt_comp))
return false;
j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);
hd.month = m; hd.day = d;
if (use_easter && (m == 3 || m == 4))
{
easter_sunday(y, &d, &m);
if (m == hd.month && (d == hd.day || d+1 == hd.day))
return false;
}
PG_RETURN_BOOL (NULL == bsearch(&hd, holidays, holidays_c,
sizeof(holiday_desc), holiday_desc_comp));
}
static int
ora_diff_bizdays(DateADT day1, DateADT day2)
{
int d, days;
int y, m, auxd;
holiday_desc hd;
int cycle_c = 0;
bool start_is_bizday = false;
DateADT aux_day;
if (day1 > day2)
{
aux_day = day1;
day1 = day2; day2 = aux_day;
}
d = j2day(day1+POSTGRES_EPOCH_JDATE);
days = 0;
while (day1 <= day2)
{
++ cycle_c;
d = (d+1) % 7;
d = (d < 0) ? 6:d;
day1 += 1;
if ((1 << d) & nonbizdays)
continue;
if (NULL != bsearch(&day1, exceptions, exceptions_c,
sizeof(DateADT), dateadt_comp))
continue;
j2date(day1 + POSTGRES_EPOCH_JDATE, &y, &m, &auxd);
hd.day = (char) auxd;
hd.month = (char) m;
if (use_easter && (m == 3 || m == 4))
{
easter_sunday(y, &auxd, &m);
if (m == hd.month && (auxd == hd.day || d+1 == hd.day))
continue;
}
if (NULL != bsearch(&hd, holidays, holidays_c,
sizeof(holiday_desc), holiday_desc_comp))
continue;
days += 1;
if (cycle_c == 1)
start_is_bizday = true;
}
if (include_start && start_is_bizday && days >= 1)
days -= 1;
return days;
}
Datum
last_day(PG_FUNCTION_ARGS)
{
DateADT day = PG_GETARG_DATEADT(0);
DateADT result;
int y, m, d;
j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);
result = date2j(y, m+1, 1) - POSTGRES_EPOCH_JDATE;
PG_RETURN_DATEADT(result - 1);
}
Datum
plvdate_set_nonbizday_day (PG_FUNCTION_ARGS)
{
DateADT arg1 = PG_GETARG_DATEADT(0);
bool arg2 = PG_GETARG_BOOL(1);
int y, m, d;
holiday_desc hd;
if (arg2)
{
if (holidays_c == MAX_holidays)
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("nonbizday registeration error"),
errdetail("Too much registered nonbizdays."),
errhint("Increase MAX_holidays in 'plvdate.c'.")));
j2date(arg1 + POSTGRES_EPOCH_JDATE, &y, &m, &d);
hd.month = m; hd.day = d;
if (NULL != bsearch(&hd, holidays, holidays_c, sizeof(holiday_desc), holiday_desc_comp))
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_OBJECT),
errmsg("nonbizday registeration error"),
errdetail("Date is registered.")));
holidays[holidays_c].month = m;
holidays[holidays_c].day = d;
holidays_c += 1;
qsort(holidays, holidays_c, sizeof(holiday_desc), holiday_desc_comp);
}
else
{
if (exceptions_c == MAX_EXCEPTIONS)
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("nonbizday registeration error"),
errdetail("Too much registered nonrepeated nonbizdays."),
errhint("Increase MAX_EXCEPTIONS in 'plvdate.c'.")));
if (NULL != bsearch(&arg1, exceptions, exceptions_c, sizeof(DateADT), dateadt_comp))
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_OBJECT),
errmsg("nonbizday registeration error"),
errdetail("Date is registered.")));
exceptions[exceptions_c++] = arg1;
qsort(exceptions, exceptions_c, sizeof(DateADT), dateadt_comp);
}
PG_RETURN_VOID();
}
Datum
plvdate_isleapyear(PG_FUNCTION_ARGS)
{
DateADT day = PG_GETARG_DATEADT(0);
int y, m, d;
bool result;
j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);
result = ((( y % 4) == 0) && ((y % 100) != 0)) || ((y / 400) == 0);
PG_RETURN_BOOL(result);
}
void
PGTYPESdate_julmdy(date jd, int *mdy)
{
int y,
m,
d;
j2date((int) (jd + date2j(2000, 1, 1)), &y, &m, &d);
mdy[0] = m;
mdy[1] = d;
mdy[2] = y;
}
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);
}
Datum
plvdate_days_inmonth(PG_FUNCTION_ARGS)
{
DateADT day = PG_GETARG_DATEADT(0);
int result;
int y, m, d;
j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);
result = date2j(y, m+1, 1) - date2j(y, m, 1);
PG_RETURN_INT32(result);
}
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);
}
Datum
months_between(PG_FUNCTION_ARGS)
{
DateADT date1 = PG_GETARG_DATEADT(0);
DateADT date2 = PG_GETARG_DATEADT(1);
int y1, m1, d1;
int y2, m2, d2;
float8 result;
j2date(date1 + POSTGRES_EPOCH_JDATE, &y1, &m1, &d1);
j2date(date2 + POSTGRES_EPOCH_JDATE, &y2, &m2, &d2);
/* Ignore day components for last days, or based on a 31-day month. */
if (d1 == days_of_month(y1, m1) && d2 == days_of_month(y2, m2))
result = (y1 - y2) * 12 + (m1 - m2);
else
result = (y1 - y2) * 12 + (m1 - m2) + (d1 - d2) / 31.0;
PG_RETURN_NUMERIC(
DirectFunctionCall1(float8_numeric, Float8GetDatumFast(result)));
}
Datum get_semester(PG_FUNCTION_ARGS) {
pg_tm current_date;
GetCurrentDateTime(¤t_date);
DateADT entering_date = DatumGetDateADT(PG_GETARG_DATUM(0));
int entering_year, entering_month, entering_day;
j2date(entering_date + date2j(2000, 1, 1), &entering_year, &entering_month, &entering_day);
int semester = (current_date.tm_year - entering_year) * 2;
if (current_date.tm_mon > 6)
semester++;
PG_RETURN_INT32(semester);
}
/* Decode a timestamp type */
static int
decode_timestamp(const char *buffer, unsigned int buff_size, unsigned int *out_size)
{
const char *new_buffer = (const char *) TYPEALIGN(sizeof(int64), (uintptr_t) buffer);
unsigned int delta = (unsigned int) ((uintptr_t) new_buffer - (uintptr_t) buffer);
int64 timestamp,
timestamp_sec;
int32 jd,
year,
month,
day;
if (buff_size < delta)
return -1;
buff_size -= delta;
buffer = new_buffer;
if (buff_size < sizeof(int64))
return -2;
*out_size = sizeof(int64) + delta;
timestamp = *(int64 *) buffer;
jd = timestamp / USECS_PER_DAY;
if (jd != 0)
timestamp -= jd * USECS_PER_DAY;
if (timestamp < INT64CONST(0))
{
timestamp += USECS_PER_DAY;
jd -= 1;
}
/* add offset to go from J2000 back to standard Julian date */
jd += POSTGRES_EPOCH_JDATE;
j2date(jd, &year, &month, &day);
timestamp_sec = timestamp / 1000000;
CopyAppendFmt("%04d-%02d-%02d %02ld:%02ld:%02ld.%06ld%s",
(year <= 0) ? -year + 1 : year, month, day,
timestamp_sec / 60 / 60, (timestamp_sec / 60) % 60, timestamp_sec % 60,
timestamp % 1000000,
(year <= 0) ? " BC" : "");
return 0;
}
static DateADT
_ora_date_trunc(DateADT day, int f)
{
int y, m, d;
DateADT result;
j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);
switch (f)
{
CASE_fmt_CC
if (y > 0)
result = DATE2J((y/100)*100+1,1,1);
else
result = DATE2J(-((99 - (y - 1)) / 100) * 100 + 1,1,1);
break;
CASE_fmt_YYYY
result = DATE2J(y,1,1);
break;
CASE_fmt_IYYY
result = iso_year(y,m,d);
break;
CASE_fmt_MON
result = DATE2J(y,m,1);
break;
CASE_fmt_WW
result = day - (day - DATE2J(y,1,1)) % 7;
break;
CASE_fmt_IW
result = day - (day - iso_year(y,m,d)) % 7;
break;
CASE_fmt_W
result = day - (day - DATE2J(y,m,1)) % 7;
break;
CASE_fmt_DAY
result = day - J2DAY(day);
break;
CASE_fmt_Q
result = DATE2J(y,((m-1)/3)*3+1,1);
break;
default:
result = day;
}
return result;
}
Datum
plvdate_unset_nonbizday_day (PG_FUNCTION_ARGS)
{
DateADT arg1 = PG_GETARG_DATEADT(0);
bool arg2 = PG_GETARG_BOOL(1);
int y, m, d;
bool found = false;
int i;
if (arg2)
{
j2date(arg1 + POSTGRES_EPOCH_JDATE, &y, &m, &d);
for (i = 0; i < holidays_c; i++)
{
if (!found && holidays[i].month == m && holidays[i].day == d)
found = true;
else if (found)
{
holidays[i-1].month = holidays[i].month;
holidays[i-1].day = holidays[i].day;
}
}
if (found)
holidays_c -= 1;
}
else
{
for (i = 0; i < exceptions_c; i++)
if (!found && exceptions[i] == arg1)
found = true;
else if (found)
exceptions[i-1] = exceptions[i];
if (found)
exceptions_c -= 1;
}
if (!found)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("nonbizday unregisteration error"),
errdetail("Nonbizday not found.")));
PG_RETURN_VOID();
}
/* 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;
}
static DateADT
ora_add_bizdays(DateADT day, int days)
{
int d, dx;
int y, m, auxd;
holiday_desc hd;
d = j2day(day+POSTGRES_EPOCH_JDATE);
dx = days > 0? 1 : -1;
while (days != 0)
{
d = (d+dx) % 7;
d = (d < 0) ? 6:d;
day += dx;
if ((1 << d) & nonbizdays)
continue;
if (NULL != bsearch(&day, exceptions, exceptions_c,
sizeof(DateADT), dateadt_comp))
continue;
j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &auxd);
hd.day = (char) auxd;
hd.month = (char) m;
if (use_easter && (m == 3 || m == 4))
{
easter_sunday(y, &auxd, &m);
if (m == hd.month && (auxd == hd.day || d+1 == hd.day))
continue;
}
if (NULL != bsearch(&hd, holidays, holidays_c,
sizeof(holiday_desc), holiday_desc_comp))
continue;
days -= dx;
}
return day;
}
/*
* redotz is used only for timestamp with time zone
*/
static void
tm_trunc(struct pg_tm *tm, text *fmt, bool *redotz)
{
int f;
f = ora_seq_search(VARDATA_ANY(fmt), date_fmt, VARSIZE_ANY_EXHDR(fmt));
CHECK_SEQ_SEARCH(f, "round/trunc format string");
tm->tm_sec = 0;
switch (f)
{
CASE_fmt_IYYY
CASE_fmt_WW
CASE_fmt_W
CASE_fmt_IW
CASE_fmt_DAY
CASE_fmt_CC
j2date(_ora_date_trunc(DATE2J(tm->tm_year, tm->tm_mon, tm->tm_mday), f)
+ POSTGRES_EPOCH_JDATE,
&tm->tm_year, &tm->tm_mon, &tm->tm_mday);
tm->tm_hour = 0;
tm->tm_min = 0;
*redotz = true;
break;
CASE_fmt_YYYY
tm->tm_mon = 1;
CASE_fmt_Q
tm->tm_mon = (3*((tm->tm_mon - 1)/3)) + 1;
CASE_fmt_MON
tm->tm_mday = 1;
CASE_fmt_DDD
tm->tm_hour = 0;
*redotz = true; /* for all cases >= DAY */
CASE_fmt_HH
tm->tm_min = 0;
}
}
int
PGTYPESdate_fmt_asc(date dDate, char *fmtstring, char *outbuf)
{
static struct
{
char *format;
int component;
} mapping[] =
{
/*
* format items have to be sorted according to their length, since the
* first pattern that matches gets replaced by its value
*/
{
"ddd", PGTYPES_FMTDATE_DOW_LITERAL_SHORT
},
{
"dd", PGTYPES_FMTDATE_DAY_DIGITS_LZ
},
{
"mmm", PGTYPES_FMTDATE_MONTH_LITERAL_SHORT
},
{
"mm", PGTYPES_FMTDATE_MONTH_DIGITS_LZ
},
{
"yyyy", PGTYPES_FMTDATE_YEAR_DIGITS_LONG
},
{
"yy", PGTYPES_FMTDATE_YEAR_DIGITS_SHORT
},
{
NULL, 0
}
};
union un_fmt_comb replace_val;
int replace_type;
int i;
int dow;
char *start_pattern;
struct tm tm;
/* copy the string over */
strcpy(outbuf, fmtstring);
/* get the date */
j2date(dDate + date2j(2000, 1, 1), &(tm.tm_year), &(tm.tm_mon), &(tm.tm_mday));
dow = PGTYPESdate_dayofweek(dDate);
for (i = 0; mapping[i].format != NULL; i++)
{
while ((start_pattern = strstr(outbuf, mapping[i].format)) != NULL)
{
switch (mapping[i].component)
{
case PGTYPES_FMTDATE_DOW_LITERAL_SHORT:
replace_val.str_val = pgtypes_date_weekdays_short[dow];
replace_type = PGTYPES_TYPE_STRING_CONSTANT;
break;
case PGTYPES_FMTDATE_DAY_DIGITS_LZ:
replace_val.uint_val = tm.tm_mday;
replace_type = PGTYPES_TYPE_UINT_2_LZ;
break;
case PGTYPES_FMTDATE_MONTH_LITERAL_SHORT:
replace_val.str_val = months[tm.tm_mon - 1];
replace_type = PGTYPES_TYPE_STRING_CONSTANT;
break;
case PGTYPES_FMTDATE_MONTH_DIGITS_LZ:
replace_val.uint_val = tm.tm_mon;
replace_type = PGTYPES_TYPE_UINT_2_LZ;
break;
case PGTYPES_FMTDATE_YEAR_DIGITS_LONG:
replace_val.uint_val = tm.tm_year;
replace_type = PGTYPES_TYPE_UINT_4_LZ;
break;
case PGTYPES_FMTDATE_YEAR_DIGITS_SHORT:
replace_val.uint_val = tm.tm_year % 100;
replace_type = PGTYPES_TYPE_UINT_2_LZ;
break;
default:
/*
* should not happen, set something anyway
*/
replace_val.str_val = " ";
replace_type = PGTYPES_TYPE_STRING_CONSTANT;
}
switch (replace_type)
{
case PGTYPES_TYPE_STRING_MALLOCED:
case PGTYPES_TYPE_STRING_CONSTANT:
strncpy(start_pattern, replace_val.str_val,
strlen(replace_val.str_val));
if (replace_type == PGTYPES_TYPE_STRING_MALLOCED)
free(replace_val.str_val);
break;
case PGTYPES_TYPE_UINT:
{
char *t = pgtypes_alloc(PGTYPES_DATE_NUM_MAX_DIGITS);
if (!t)
return -1;
snprintf(t, PGTYPES_DATE_NUM_MAX_DIGITS,
"%u", replace_val.uint_val);
strncpy(start_pattern, t, strlen(t));
free(t);
}
break;
case PGTYPES_TYPE_UINT_2_LZ:
{
char *t = pgtypes_alloc(PGTYPES_DATE_NUM_MAX_DIGITS);
if (!t)
return -1;
snprintf(t, PGTYPES_DATE_NUM_MAX_DIGITS,
"%02u", replace_val.uint_val);
strncpy(start_pattern, t, strlen(t));
free(t);
}
break;
case PGTYPES_TYPE_UINT_4_LZ:
{
char *t = pgtypes_alloc(PGTYPES_DATE_NUM_MAX_DIGITS);
if (!t)
return -1;
snprintf(t, PGTYPES_DATE_NUM_MAX_DIGITS,
"%04u", replace_val.uint_val);
strncpy(start_pattern, t, strlen(t));
free(t);
}
break;
default:
/*
* doesn't happen (we set replace_type to
* PGTYPES_TYPE_STRING_CONSTANT in case of an error above)
*/
break;
}
}
}
return 0;
}
/* 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);
}
/* timestamptz_pl_interval()
* Add a interval to a timestamp with time zone data type.
* Note that interval has provisions for qualitative year/month
* units, so try to do the right thing with them.
* To add a month, increment the month, and use the same day of month.
* Then, if the next month has fewer days, set the day of month
* to the last day of month.
* Lastly, add in the "quantitative time".
*/
TimestampTz
timestamptz_pl_interval(TimestampTz timestamp, Interval *span)
{
TimestampTz result;
int tz;
if (TIMESTAMP_NOT_FINITE(timestamp))
result = timestamp;
else
{
if (span->month != 0)
{
struct tm tt,
*tm = &tt;
fsec_t fsec;
if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
warnx("timestamp out of range");
tm->tm_mon += span->month;
if (tm->tm_mon > MONTHS_PER_YEAR)
{
tm->tm_year += (tm->tm_mon - 1) / MONTHS_PER_YEAR;
tm->tm_mon = ((tm->tm_mon - 1) % MONTHS_PER_YEAR) + 1;
}
else if (tm->tm_mon < 1)
{
tm->tm_year += tm->tm_mon / MONTHS_PER_YEAR - 1;
tm->tm_mon = tm->tm_mon % MONTHS_PER_YEAR + MONTHS_PER_YEAR;
}
/* adjust for end of month boundary problems... */
if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
tm->tm_mday = (day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);
tz = DetermineTimeZoneOffset(tm, session_timezone);
if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
warnx("timestamp out of range");
}
if (span->day != 0)
{
struct tm tt,
*tm = &tt;
fsec_t fsec;
int julian;
if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
warnx("timestamp out of range");
/* Add days by converting to and from julian */
julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day;
j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
tz = DetermineTimeZoneOffset(tm, session_timezone);
if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
warnx("timestamp out of range");
}
timestamp += span->time;
result = timestamp;
}
return result;
}
/* timestamp2tm()
* Convert timestamp data type to POSIX time structure.
* Note that year is _not_ 1900-based, but is an explicit full value.
* Also, month is one-based, _not_ zero-based.
* Returns:
* 0 on success
* -1 on out of range
*
* For dates within the system-supported time_t range, convert to the
* local time zone. If out of this range, leave as GMT. - tgl 97/05/27
*/
static int
timestamp2tm(timestamp dt, int *tzp, struct tm * tm, fsec_t *fsec, char **tzn)
{
#ifdef HAVE_INT64_TIMESTAMP
int64 dDate,
date0;
int64 time;
#else
double dDate,
date0;
double time;
#endif
time_t utime;
#if defined(HAVE_TM_ZONE) || defined(HAVE_INT_TIMEZONE)
struct tm *tx;
#endif
date0 = date2j(2000, 1, 1);
#ifdef HAVE_INT64_TIMESTAMP
time = dt;
TMODULO(time, dDate, USECS_PER_DAY);
if (time < INT64CONST(0))
{
time += USECS_PER_DAY;
dDate -= 1;
}
/* add offset to go from J2000 back to standard Julian date */
dDate += date0;
/* Julian day routine does not work for negative Julian days */
if (dDate < 0 || dDate > (timestamp) INT_MAX)
return -1;
j2date((int) dDate, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
dt2time(time, &tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
#else
time = dt;
TMODULO(time, dDate, (double) SECS_PER_DAY);
if (time < 0)
{
time += SECS_PER_DAY;
dDate -= 1;
}
/* add offset to go from J2000 back to standard Julian date */
dDate += date0;
recalc_d:
/* Julian day routine does not work for negative Julian days */
if (dDate < 0 || dDate > (timestamp) INT_MAX)
return -1;
j2date((int) dDate, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
recalc_t:
dt2time(time, &tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
*fsec = TSROUND(*fsec);
/* roundoff may need to propagate to higher-order fields */
if (*fsec >= 1.0)
{
time = ceil(time);
if (time >= (double) SECS_PER_DAY)
{
time = 0;
dDate += 1;
goto recalc_d;
}
goto recalc_t;
}
#endif
if (tzp != NULL)
{
/*
* Does this fall within the capabilities of the localtime()
* interface? Then use this to rotate to the local time zone.
*/
if (IS_VALID_UTIME(tm->tm_year, tm->tm_mon, tm->tm_mday))
{
#ifdef HAVE_INT64_TIMESTAMP
utime = dt / USECS_PER_SEC +
((date0 - date2j(1970, 1, 1)) * INT64CONST(86400));
#else
utime = dt + (date0 - date2j(1970, 1, 1)) * SECS_PER_DAY;
#endif
#if defined(HAVE_TM_ZONE) || defined(HAVE_INT_TIMEZONE)
tx = localtime(&utime);
tm->tm_year = tx->tm_year + 1900;
tm->tm_mon = tx->tm_mon + 1;
tm->tm_mday = tx->tm_mday;
tm->tm_hour = tx->tm_hour;
tm->tm_min = tx->tm_min;
tm->tm_isdst = tx->tm_isdst;
#if defined(HAVE_TM_ZONE)
tm->tm_gmtoff = tx->tm_gmtoff;
tm->tm_zone = tx->tm_zone;
*tzp = -tm->tm_gmtoff; /* tm_gmtoff is Sun/DEC-ism */
if (tzn != NULL)
*tzn = (char *) tm->tm_zone;
#elif defined(HAVE_INT_TIMEZONE)
*tzp = (tm->tm_isdst > 0) ? TIMEZONE_GLOBAL - SECS_PER_HOUR : TIMEZONE_GLOBAL;
if (tzn != NULL)
*tzn = TZNAME_GLOBAL[(tm->tm_isdst > 0)];
#endif
#else /* not (HAVE_TM_ZONE || HAVE_INT_TIMEZONE) */
*tzp = 0;
/* Mark this as *no* time zone available */
tm->tm_isdst = -1;
if (tzn != NULL)
*tzn = NULL;
#endif
dt = dt2local(dt, *tzp);
}
else
{
*tzp = 0;
/* Mark this as *no* time zone available */
tm->tm_isdst = -1;
if (tzn != NULL)
*tzn = NULL;
}
}
else
{
tm->tm_isdst = -1;
if (tzn != NULL)
*tzn = NULL;
}
return 0;
} /* timestamp2tm() */
static void
tm_round(struct pg_tm *tm, text *fmt, bool *redotz)
{
int f;
bool rounded = true;
f = ora_seq_search(VARDATA_ANY(fmt), date_fmt, VARSIZE_ANY_EXHDR(fmt));
CHECK_SEQ_SEARCH(f, "round/trunc format string");
/* set rounding rule */
switch (f)
{
CASE_fmt_IYYY
NOT_ROUND_MDAY(tm->tm_mday < 8 && tm->tm_mon == 1);
NOT_ROUND_MDAY(tm->tm_mday == 30 && tm->tm_mon == 6);
if (tm->tm_mday >= 28 && tm->tm_mon == 12 && tm->tm_hour >= 12)
{
DateADT isoyear = iso_year(tm->tm_year+1, 1, 8);
DateADT day0 = DATE2J(tm->tm_year+1,1,1);
DateADT dayc = DATE2J(tm->tm_year, tm->tm_mon, tm->tm_mday);
if ((isoyear <= day0) || (day0 <= dayc + 2))
{
rounded = false;
}
}
break;
CASE_fmt_YYYY
NOT_ROUND_MDAY(tm->tm_mday == 30 && tm->tm_mon == 6);
break;
CASE_fmt_MON
NOT_ROUND_MDAY(tm->tm_mday == 15);
break;
CASE_fmt_Q
NOT_ROUND_MDAY(tm->tm_mday == 15 && tm->tm_mon == ((tm->tm_mon-1)/3)*3+2);
break;
CASE_fmt_WW
CASE_fmt_IW
/* last day in year */
NOT_ROUND_MDAY(DATE2J(tm->tm_year, tm->tm_mon, tm->tm_mday) ==
(DATE2J(tm->tm_year+1, 1,1) - 1));
break;
CASE_fmt_W
/* last day in month */
NOT_ROUND_MDAY(DATE2J(tm->tm_year, tm->tm_mon, tm->tm_mday) ==
(DATE2J(tm->tm_year, tm->tm_mon+1,1) - 1));
break;
}
switch (f)
{
/* easier convert to date */
CASE_fmt_IW
CASE_fmt_DAY
CASE_fmt_IYYY
CASE_fmt_WW
CASE_fmt_W
CASE_fmt_CC
CASE_fmt_MON
CASE_fmt_YYYY
CASE_fmt_Q
ROUND_MDAY(tm);
j2date(_ora_date_round(DATE2J(tm->tm_year, tm->tm_mon, tm->tm_mday), f)
+ POSTGRES_EPOCH_JDATE,
&tm->tm_year, &tm->tm_mon, &tm->tm_mday);
tm->tm_hour = 0;
tm->tm_min = 0;
*redotz = true;
break;
CASE_fmt_DDD
tm->tm_mday += (tm->tm_hour >= 12)?1:0;
tm->tm_hour = 0;
tm->tm_min = 0;
*redotz = true;
break;
CASE_fmt_MI
tm->tm_min += (tm->tm_sec >= 30)?1:0;
break;
CASE_fmt_HH
tm->tm_hour += (tm->tm_min >= 30)?1:0;
tm->tm_min = 0;
break;
}
tm->tm_sec = 0;
}
static DateADT
_ora_date_round(DateADT day, int f)
{
int y, m, d, z;
DateADT result;
j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);
switch (f)
{
CASE_fmt_CC
if (y > 0)
result = DATE2J((y/100)*100+(day < DATE2J((y/100)*100+50,1,1) ?1:101),1,1);
else
result = DATE2J((y/100)*100+(day < DATE2J((y/100)*100-50+1,1,1) ?-99:1),1,1);
break;
CASE_fmt_YYYY
result = DATE2J(y+(day<DATE2J(y,7,1)?0:1),1,1);
break;
CASE_fmt_IYYY
{
if (day < DATE2J(y,7,1))
{
result = iso_year(y, m, d);
}
else
{
DateADT iy1 = iso_year(y+1, 1, 8);
result = iy1;
if (((day - DATE2J(y,1,1)) / 7 + 1) >= 52)
{
bool overl = ((date2j(y+2,1,1)-date2j(y+1,1,1)) == 366);
bool isSaturday = (J2DAY(day) == 6);
DateADT iy2 = iso_year(y+2, 1, 8);
DateADT day1 = DATE2J(y+1,1,1);
/* exception saturdays */
if (iy1 >= (day1) && day >= day1 - 2 && isSaturday)
{
result = overl?iy2:iy1;
}
/* iso year stars in last year and day >= iso year */
else if (iy1 <= (day1) && day >= iy1 - 3)
{
DateADT cmp = iy1 - (iy1 < day1?0:1);
int d = J2DAY(day1);
/* some exceptions */
if ((day >= cmp - 2) && (!(d == 3 && overl)))
{
/* if year don't starts in thursday */
if ((d < 4 && J2DAY(day) != 5 && !isSaturday)
||(d == 2 && isSaturday && overl))
{
result = iy2;
}
}
}
}
}
break;
}
CASE_fmt_MON
result = DATE2J(y,m+(day<DATE2J(y,m,16)?0:1),1);
break;
CASE_fmt_WW
z = (day - DATE2J(y,1,1)) % 7;
result = day - z + (z < 4?0:7);
break;
CASE_fmt_IW
{
z = (day - iso_year(y,m,d)) % 7;
result = day - z + (z < 4?0:7);
if (((day - DATE2J(y,1,1)) / 7 + 1) >= 52)
{
/* only for last iso week */
DateADT isoyear = iso_year(y+1, 1, 8);
if (isoyear > (DATE2J(y+1,1,1)-1))
if (day > isoyear - 7)
{
int d = J2DAY(day);
result -= (d == 0 || d > 4?7:0);
}
}
break;
}
CASE_fmt_W
z = (day - DATE2J(y,m,1)) % 7;
result = day - z + (z < 4?0:7);
break;
CASE_fmt_DAY
z = J2DAY(day);
if (y > 0)
result = day - z + (z < 4?0:7);
else
result = day + (5 - (z>0?(z>1?z:z+7):7));
break;
CASE_fmt_Q
result = DATE2J(y,((m-1)/3)*3+(day<(DATE2J(y,((m-1)/3)*3+2,16))?1:4),1);
break;
default:
result = day;
}
return result;
}
/* DecodeDateTime()
* Interpret previously parsed fields for general date and time.
* Return 0 if full date, 1 if only time, and negative DTERR code if problems.
* (Currently, all callers treat 1 as an error return too.)
*
* External format(s):
* "<weekday> <month>-<day>-<year> <hour>:<minute>:<second>"
* "Fri Feb-7-1997 15:23:27"
* "Feb-7-1997 15:23:27"
* "2-7-1997 15:23:27"
* "1997-2-7 15:23:27"
* "1997.038 15:23:27" (day of year 1-366)
* Also supports input in compact time:
* "970207 152327"
* "97038 152327"
* "20011225T040506.789-07"
*
* Use the system-provided functions to get the current time zone
* if not specified in the input string.
*
* If the date is outside the range of pg_time_t (in practice that could only
* happen if pg_time_t is just 32 bits), then assume UTC time zone - thomas
* 1997-05-27
*/
int
DecodeDateTime(char **field, int *ftype, int nf, int *dtype, struct tm *tm, fsec_t *fsec, int *tzp)
{
int fmask = 0,
tmask,
type;
int ptype = 0; /* "prefix type" for ISO y2001m02d04 format */
int i;
int val;
int dterr;
int mer = HR24;
bool haveTextMonth = FALSE;
bool isjulian = FALSE;
bool is2digits = FALSE;
bool bc = FALSE;
pg_tz *namedTz = NULL;
struct tm cur_tm;
/*
* We'll insist on at least all of the date fields, but initialize the
* remaining fields in case they are not set later...
*/
*dtype = DTK_DATE;
tm->tm_hour = 0;
tm->tm_min = 0;
tm->tm_sec = 0;
*fsec = 0;
/* don't know daylight savings time status apriori */
tm->tm_isdst = -1;
if (tzp != NULL)
*tzp = 0;
for (i = 0; i < nf; i++)
{
switch (ftype[i])
{
case DTK_DATE:
/***
* Integral julian day with attached time zone?
* All other forms with JD will be separated into
* distinct fields, so we handle just this case here.
***/
if (ptype == DTK_JULIAN)
{
char *cp;
int val;
if (tzp == NULL)
return DTERR_BAD_FORMAT;
errno = 0;
val = strtoi(field[i], &cp, 10);
if (errno == ERANGE || val < 0)
return DTERR_FIELD_OVERFLOW;
j2date(val, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
isjulian = TRUE;
/* Get the time zone from the end of the string */
dterr = DecodeTimezone(cp, tzp);
if (dterr)
return dterr;
tmask = DTK_DATE_M | DTK_TIME_M | DTK_M(TZ);
ptype = 0;
break;
}
/***
* Already have a date? Then this might be a time zone name
* with embedded punctuation (e.g. "America/New_York") or a
* run-together time with trailing time zone (e.g. hhmmss-zz).
* - thomas 2001-12-25
*
* We consider it a time zone if we already have month & day.
* This is to allow the form "mmm dd hhmmss tz year", which
* we've historically accepted.
***/
else if (ptype != 0 ||
((fmask & (DTK_M(MONTH) | DTK_M(DAY))) ==
(DTK_M(MONTH) | DTK_M(DAY))))
{
/* No time zone accepted? Then quit... */
if (tzp == NULL)
return DTERR_BAD_FORMAT;
if (isdigit((unsigned char) *field[i]) || ptype != 0)
{
char *cp;
if (ptype != 0)
{
/* Sanity check; should not fail this test */
if (ptype != DTK_TIME)
return DTERR_BAD_FORMAT;
ptype = 0;
}
/*
* Starts with a digit but we already have a time
* field? Then we are in trouble with a date and time
* already...
*/
if ((fmask & DTK_TIME_M) == DTK_TIME_M)
return DTERR_BAD_FORMAT;
if ((cp = strchr(field[i], '-')) == NULL)
return DTERR_BAD_FORMAT;
/* Get the time zone from the end of the string */
dterr = DecodeTimezone(cp, tzp);
if (dterr)
return dterr;
*cp = '\0';
/*
* Then read the rest of the field as a concatenated
* time
*/
dterr = DecodeNumberField(strlen(field[i]), field[i],
fmask,
&tmask, tm,
fsec, &is2digits);
if (dterr < 0)
return dterr;
/*
* modify tmask after returning from
* DecodeNumberField()
*/
tmask |= DTK_M(TZ);
}
else
{
namedTz = pg_tzset(field[i]);
if (!namedTz)
{
/*
* We should return an error code instead of
* ereport'ing directly, but then there is no way
* to report the bad time zone name.
*/
warnx("time zone \"%s\" not recognized",
field[i]);
}
/* we'll apply the zone setting below */
tmask = DTK_M(TZ);
}
}
else
{
dterr = DecodeDate(field[i], fmask,
&tmask, &is2digits, tm);
if (dterr)
return dterr;
}
break;
case DTK_TIME:
/*
* This might be an ISO time following a "t" field.
*/
if (ptype != 0)
{
/* Sanity check; should not fail this test */
if (ptype != DTK_TIME)
return DTERR_BAD_FORMAT;
ptype = 0;
}
dterr = DecodeTime(field[i], fmask, INTERVAL_FULL_RANGE,
&tmask, tm, fsec);
if (dterr)
return dterr;
/*
* Check upper limit on hours; other limits checked in
* DecodeTime()
*/
/* test for > 24:00:00 */
if (tm->tm_hour > HOURS_PER_DAY ||
(tm->tm_hour == HOURS_PER_DAY &&
(tm->tm_min > 0 || tm->tm_sec > 0 || *fsec > 0)))
return DTERR_FIELD_OVERFLOW;
break;
case DTK_TZ:
{
int tz;
if (tzp == NULL)
return DTERR_BAD_FORMAT;
dterr = DecodeTimezone(field[i], &tz);
if (dterr)
return dterr;
*tzp = tz;
tmask = DTK_M(TZ);
}
break;
case DTK_NUMBER:
/*
* Was this an "ISO date" with embedded field labels? An
* example is "y2001m02d04" - thomas 2001-02-04
*/
if (ptype != 0)
{
char *cp;
int val;
errno = 0;
val = strtoi(field[i], &cp, 10);
if (errno == ERANGE)
return DTERR_FIELD_OVERFLOW;
/*
* only a few kinds are allowed to have an embedded
* decimal
*/
if (*cp == '.')
switch (ptype)
{
case DTK_JULIAN:
case DTK_TIME:
case DTK_SECOND:
break;
default:
return DTERR_BAD_FORMAT;
break;
}
else if (*cp != '\0')
return DTERR_BAD_FORMAT;
switch (ptype)
{
case DTK_YEAR:
tm->tm_year = val;
tmask = DTK_M(YEAR);
break;
case DTK_MONTH:
/*
* already have a month and hour? then assume
* minutes
*/
if ((fmask & DTK_M(MONTH)) != 0 &&
(fmask & DTK_M(HOUR)) != 0)
{
tm->tm_min = val;
tmask = DTK_M(MINUTE);
}
else
{
tm->tm_mon = val;
tmask = DTK_M(MONTH);
}
break;
case DTK_DAY:
tm->tm_mday = val;
tmask = DTK_M(DAY);
break;
case DTK_HOUR:
tm->tm_hour = val;
tmask = DTK_M(HOUR);
break;
case DTK_MINUTE:
tm->tm_min = val;
tmask = DTK_M(MINUTE);
break;
case DTK_SECOND:
tm->tm_sec = val;
tmask = DTK_M(SECOND);
if (*cp == '.')
{
dterr = ParseFractionalSecond(cp, fsec);
if (dterr)
return dterr;
tmask = DTK_ALL_SECS_M;
}
break;
case DTK_TZ:
tmask = DTK_M(TZ);
dterr = DecodeTimezone(field[i], tzp);
if (dterr)
return dterr;
break;
case DTK_JULIAN:
/* previous field was a label for "julian date" */
if (val < 0)
return DTERR_FIELD_OVERFLOW;
tmask = DTK_DATE_M;
j2date(val, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
isjulian = TRUE;
/* fractional Julian Day? */
if (*cp == '.')
{
double time;
errno = 0;
time = strtod(cp, &cp);
if (*cp != '\0' || errno != 0)
return DTERR_BAD_FORMAT;
#ifdef HAVE_INT64_TIMESTAMP
time *= USECS_PER_DAY;
#else
time *= SECS_PER_DAY;
#endif
dt2time(time,
&tm->tm_hour, &tm->tm_min,
&tm->tm_sec, fsec);
tmask |= DTK_TIME_M;
}
break;
case DTK_TIME:
/* previous field was "t" for ISO time */
dterr = DecodeNumberField(strlen(field[i]), field[i],
(fmask | DTK_DATE_M),
&tmask, tm,
fsec, &is2digits);
if (dterr < 0)
return dterr;
if (tmask != DTK_TIME_M)
return DTERR_BAD_FORMAT;
break;
default:
return DTERR_BAD_FORMAT;
break;
}
ptype = 0;
*dtype = DTK_DATE;
}
else
{
char *cp;
int flen;
flen = strlen(field[i]);
cp = strchr(field[i], '.');
/* Embedded decimal and no date yet? */
if (cp != NULL && !(fmask & DTK_DATE_M))
{
dterr = DecodeDate(field[i], fmask,
&tmask, &is2digits, tm);
if (dterr)
return dterr;
}
/* embedded decimal and several digits before? */
else if (cp != NULL && flen - strlen(cp) > 2)
{
/*
* Interpret as a concatenated date or time Set the
* type field to allow decoding other fields later.
* Example: 20011223 or 040506
*/
dterr = DecodeNumberField(flen, field[i], fmask,
&tmask, tm,
fsec, &is2digits);
if (dterr < 0)
return dterr;
}
else if (flen > 4)
{
dterr = DecodeNumberField(flen, field[i], fmask,
&tmask, tm,
fsec, &is2digits);
if (dterr < 0)
return dterr;
}
/* otherwise it is a single date/time field... */
else
{
dterr = DecodeNumber(flen, field[i],
haveTextMonth, fmask,
&tmask, tm,
fsec, &is2digits);
if (dterr)
return dterr;
}
}
break;
case DTK_STRING:
case DTK_SPECIAL:
type = DecodeSpecial(i, field[i], &val);
if (type == IGNORE_DTF)
continue;
tmask = DTK_M(type);
switch (type)
{
case RESERV:
switch (val)
{
case DTK_CURRENT:
warnx("date/time value \"current\" is no longer supported");
return DTERR_BAD_FORMAT;
break;
case DTK_NOW:
tmask = (DTK_DATE_M | DTK_TIME_M | DTK_M(TZ));
*dtype = DTK_DATE;
GetCurrentTimeUsec(tm, fsec, tzp);
break;
case DTK_YESTERDAY:
tmask = DTK_DATE_M;
*dtype = DTK_DATE;
GetCurrentDateTime(&cur_tm);
j2date(date2j(cur_tm.tm_year, cur_tm.tm_mon, cur_tm.tm_mday) - 1,
&tm->tm_year, &tm->tm_mon, &tm->tm_mday);
break;
case DTK_TODAY:
tmask = DTK_DATE_M;
*dtype = DTK_DATE;
GetCurrentDateTime(&cur_tm);
tm->tm_year = cur_tm.tm_year;
tm->tm_mon = cur_tm.tm_mon;
tm->tm_mday = cur_tm.tm_mday;
break;
case DTK_TOMORROW:
tmask = DTK_DATE_M;
*dtype = DTK_DATE;
GetCurrentDateTime(&cur_tm);
j2date(date2j(cur_tm.tm_year, cur_tm.tm_mon, cur_tm.tm_mday) + 1,
&tm->tm_year, &tm->tm_mon, &tm->tm_mday);
break;
case DTK_ZULU:
tmask = (DTK_TIME_M | DTK_M(TZ));
*dtype = DTK_DATE;
tm->tm_hour = 0;
tm->tm_min = 0;
tm->tm_sec = 0;
if (tzp != NULL)
*tzp = 0;
break;
default:
*dtype = val;
}
break;
case MONTH:
/*
* already have a (numeric) month? then see if we can
* substitute...
*/
if ((fmask & DTK_M(MONTH)) && !haveTextMonth &&
!(fmask & DTK_M(DAY)) && tm->tm_mon >= 1 &&
tm->tm_mon <= 31)
{
tm->tm_mday = tm->tm_mon;
tmask = DTK_M(DAY);
}
haveTextMonth = TRUE;
tm->tm_mon = val;
break;
case DTZMOD:
/*
* daylight savings time modifier (solves "MET DST"
* syntax)
*/
tmask |= DTK_M(DTZ);
tm->tm_isdst = 1;
if (tzp == NULL)
return DTERR_BAD_FORMAT;
*tzp += val * MINS_PER_HOUR;
break;
case DTZ:
/*
* set mask for TZ here _or_ check for DTZ later when
* getting default timezone
*/
tmask |= DTK_M(TZ);
tm->tm_isdst = 1;
if (tzp == NULL)
return DTERR_BAD_FORMAT;
*tzp = val * MINS_PER_HOUR;
break;
case TZ:
tm->tm_isdst = 0;
if (tzp == NULL)
return DTERR_BAD_FORMAT;
*tzp = val * MINS_PER_HOUR;
break;
case IGNORE_DTF:
break;
case AMPM:
mer = val;
break;
case ADBC:
bc = (val == BC);
break;
case DOW:
tm->tm_wday = val;
break;
case UNITS:
tmask = 0;
ptype = val;
break;
case ISOTIME:
/*
* This is a filler field "t" indicating that the next
* field is time. Try to verify that this is sensible.
*/
tmask = 0;
/* No preceding date? Then quit... */
if ((fmask & DTK_DATE_M) != DTK_DATE_M)
return DTERR_BAD_FORMAT;
/***
* We will need one of the following fields:
* DTK_NUMBER should be hhmmss.fff
* DTK_TIME should be hh:mm:ss.fff
* DTK_DATE should be hhmmss-zz
***/
if (i >= nf - 1 ||
(ftype[i + 1] != DTK_NUMBER &&
ftype[i + 1] != DTK_TIME &&
ftype[i + 1] != DTK_DATE))
return DTERR_BAD_FORMAT;
ptype = val;
break;
case UNKNOWN_FIELD:
/*
* Before giving up and declaring error, check to see
* if it is an all-alpha timezone name.
*/
namedTz = pg_tzset(field[i]);
if (!namedTz)
return DTERR_BAD_FORMAT;
/* we'll apply the zone setting below */
tmask = DTK_M(TZ);
break;
default:
return DTERR_BAD_FORMAT;
}
break;
default:
return DTERR_BAD_FORMAT;
}
if (tmask & fmask)
return DTERR_BAD_FORMAT;
fmask |= tmask;
} /* end loop over fields */
/* do final checking/adjustment of Y/M/D fields */
dterr = ValidateDate(fmask, isjulian, is2digits, bc, tm);
if (dterr)
return dterr;
/* handle AM/PM */
if (mer != HR24 && tm->tm_hour > HOURS_PER_DAY / 2)
return DTERR_FIELD_OVERFLOW;
if (mer == AM && tm->tm_hour == HOURS_PER_DAY / 2)
tm->tm_hour = 0;
else if (mer == PM && tm->tm_hour != HOURS_PER_DAY / 2)
tm->tm_hour += HOURS_PER_DAY / 2;
/* do additional checking for full date specs... */
if (*dtype == DTK_DATE)
{
if ((fmask & DTK_DATE_M) != DTK_DATE_M)
{
if ((fmask & DTK_TIME_M) == DTK_TIME_M)
return 1;
return DTERR_BAD_FORMAT;
}
/*
* If we had a full timezone spec, compute the offset (we could not do
* it before, because we need the date to resolve DST status).
*/
if (namedTz != NULL)
{
/* daylight savings time modifier disallowed with full TZ */
if (fmask & DTK_M(DTZMOD))
return DTERR_BAD_FORMAT;
*tzp = DetermineTimeZoneOffset(tm, namedTz);
}
/* timezone not specified? then find local timezone if possible */
if (tzp != NULL && !(fmask & DTK_M(TZ)))
{
/*
* daylight savings time modifier but no standard timezone? then
* error
*/
if (fmask & DTK_M(DTZMOD))
return DTERR_BAD_FORMAT;
*tzp = DetermineTimeZoneOffset(tm, session_timezone);
}
}
return 0;
}
/*
* 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");
}
}
}
/*
* timestamp2tm() - Convert timestamp data type to POSIX time structure.
*
* Note that year is _not_ 1900-based, but is an explicit full value.
* Also, month is one-based, _not_ zero-based.
* Returns:
* 0 on success
* -1 on out of range
*
* If attimezone is NULL, the global timezone (including possibly brute forced
* timezone) will be used.
*/
int
timestamp2tm(Timestamp dt, int *tzp, struct tm *tm, fsec_t *fsec, const char **tzn, pg_tz *attimezone)
{
Timestamp date;
Timestamp time;
pg_time_t utime;
/*
* If HasCTZSet is true then we have a brute force time zone specified. Go
* ahead and rotate to the local time zone since we will later bypass any
* calls which adjust the tm fields.
*/
if (attimezone == NULL && HasCTZSet && tzp != NULL)
{
#ifdef HAVE_INT64_TIMESTAMP
dt -= CTimeZone * USECS_PER_SEC;
#else
dt -= CTimeZone;
#endif
}
#ifdef HAVE_INT64_TIMESTAMP
time = dt;
TMODULO(time, date, USECS_PER_DAY);
if (time < INT64CONST(0))
{
time += USECS_PER_DAY;
date -= 1;
}
/* add offset to go from J2000 back to standard Julian date */
date += POSTGRES_EPOCH_JDATE;
/* Julian day routine does not work for negative Julian days */
if (date < 0 || date > (Timestamp) INT_MAX)
return -1;
j2date((int) date, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
dt2time(time, &tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
#else
time = dt;
TMODULO(time, date, (double) SECS_PER_DAY);
if (time < 0)
{
time += SECS_PER_DAY;
date -= 1;
}
/* add offset to go from J2000 back to standard Julian date */
date += POSTGRES_EPOCH_JDATE;
recalc_d:
/* Julian day routine does not work for negative Julian days */
if (date < 0 || date > (Timestamp) INT_MAX)
return -1;
j2date((int) date, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
recalc_t:
dt2time(time, &tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
*fsec = TSROUND(*fsec);
/* roundoff may need to propagate to higher-order fields */
if (*fsec >= 1.0)
{
time = ceil(time);
if (time >= (double) SECS_PER_DAY)
{
time = 0;
date += 1;
goto recalc_d;
}
goto recalc_t;
}
#endif
/* Done if no TZ conversion wanted */
if (tzp == NULL)
{
tm->tm_isdst = -1;
tm->tm_gmtoff = 0;
tm->tm_zone = NULL;
if (tzn != NULL)
*tzn = NULL;
return 0;
}
/*
* We have a brute force time zone per SQL99? Then use it without change
* since we have already rotated to the time zone.
*/
if (attimezone == NULL && HasCTZSet)
{
*tzp = CTimeZone;
tm->tm_isdst = 0;
tm->tm_gmtoff = CTimeZone;
tm->tm_zone = NULL;
if (tzn != NULL)
*tzn = NULL;
return 0;
}
/*
* If the time falls within the range of pg_time_t, use pg_localtime() to
* rotate to the local time zone.
*
* First, convert to an integral timestamp, avoiding possibly
* platform-specific roundoff-in-wrong-direction errors, and adjust to
* Unix epoch. Then see if we can convert to pg_time_t without loss. This
* coding avoids hardwiring any assumptions about the width of pg_time_t,
* so it should behave sanely on machines without int64_t.
*/
#ifdef HAVE_INT64_TIMESTAMP
dt = (dt - *fsec) / USECS_PER_SEC +
(POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY;
#else
dt = rint(dt - *fsec +
(POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY);
#endif
utime = (pg_time_t) dt;
if ((Timestamp) utime == dt)
{
struct tm *tx = pg_localtime(&utime,
attimezone ? attimezone : session_timezone);
tm->tm_year = tx->tm_year + 1900;
tm->tm_mon = tx->tm_mon + 1;
tm->tm_mday = tx->tm_mday;
tm->tm_hour = tx->tm_hour;
tm->tm_min = tx->tm_min;
tm->tm_sec = tx->tm_sec;
tm->tm_isdst = tx->tm_isdst;
tm->tm_gmtoff = tx->tm_gmtoff;
tm->tm_zone = tx->tm_zone;
*tzp = -tm->tm_gmtoff;
if (tzn != NULL)
*tzn = tm->tm_zone;
}
else
{
/*
* When out of range of pg_time_t, treat as GMT
*/
*tzp = 0;
/* Mark this as *no* time zone available */
tm->tm_isdst = -1;
tm->tm_gmtoff = 0;
tm->tm_zone = NULL;
if (tzn != NULL)
*tzn = NULL;
}
return 0;
}
static int
ora_diff_bizdays(DateADT day1, DateADT day2)
{
int d, days;
int y, m, auxd;
holiday_desc hd;
int loops = 0;
bool start_is_bizday = false;
DateADT aux_day;
if (day1 > day2)
{
aux_day = day1;
day1 = day2; day2 = aux_day;
}
/* d is incremented on start of cycle, so now I have to decrease one */
d = j2day(day1+POSTGRES_EPOCH_JDATE-1);
days = 0;
while (day1 <= day2)
{
loops++;
day1 += 1;
d = (d+1) % 7;
if ((1 << d) & nonbizdays)
continue;
if (NULL != bsearch(&day1, exceptions, exceptions_c,
sizeof(DateADT), dateadt_comp))
continue;
j2date(day1 + POSTGRES_EPOCH_JDATE, &y, &m, &auxd);
hd.day = (char) auxd;
hd.month = (char) m;
if (easter_holidays(day1, y, m))
continue;
if (NULL != bsearch(&hd, holidays, holidays_c,
sizeof(holiday_desc), holiday_desc_comp))
continue;
/* now the day have to be bizday, remember if first day was bizday */
if (loops == 1)
start_is_bizday = true;
days += 1;
}
/*
* decrease result when first day was bizday, but we don't want
* calculate first day.
*/
if ( start_is_bizday && !include_start && days > 0)
days -= 1;
return days;
}
/* timestamp2tm()
* Convert timestamp data type to POSIX time structure.
* Note that year is _not_ 1900-based, but is an explicit full value.
* Also, month is one-based, _not_ zero-based.
* Returns:
* 0 on success
* -1 on out of range
*
* For dates within the system-supported time_t range, convert to the
* local time zone. If out of this range, leave as GMT. - tgl 97/05/27
*/
static int
timestamp2tm(timestamp dt, int *tzp, struct tm * tm, fsec_t *fsec, char **tzn)
{
#ifdef HAVE_INT64_TIMESTAMP
int dDate, date0;
int64 time;
#else
double dDate, date0;
double time;
#endif
time_t utime;
#if defined(HAVE_TM_ZONE) || defined(HAVE_INT_TIMEZONE)
struct tm *tx;
#endif
date0 = date2j(2000, 1, 1);
time = dt;
#ifdef HAVE_INT64_TIMESTAMP
TMODULO(time, dDate, INT64CONST(86400000000));
if (time < INT64CONST(0))
{
time += INT64CONST(86400000000);
dDate -= 1;
}
#else
TMODULO(time, dDate, 86400e0);
if (time < 0)
{
time += 86400;
dDate -= 1;
}
#endif
/* Julian day routine does not work for negative Julian days */
if (dDate < -date0)
return -1;
/* add offset to go from J2000 back to standard Julian date */
dDate += date0;
j2date((int) dDate, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
dt2time(time, &tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
if (tzp != NULL)
{
/*
* Does this fall within the capabilities of the localtime()
* interface? Then use this to rotate to the local time zone.
*/
if (IS_VALID_UTIME(tm->tm_year, tm->tm_mon, tm->tm_mday))
{
#ifdef HAVE_INT64_TIMESTAMP
utime = ((dt / INT64CONST(1000000))
+ ((date0 - date2j(1970, 1, 1)) * INT64CONST(86400)));
#else
utime = (dt + ((date0 - date2j(1970, 1, 1)) * 86400));
#endif
#if defined(HAVE_TM_ZONE) || defined(HAVE_INT_TIMEZONE)
tx = localtime(&utime);
tm->tm_year = tx->tm_year + 1900;
tm->tm_mon = tx->tm_mon + 1;
tm->tm_mday = tx->tm_mday;
tm->tm_hour = tx->tm_hour;
tm->tm_min = tx->tm_min;
tm->tm_isdst = tx->tm_isdst;
#if defined(HAVE_TM_ZONE)
tm->tm_gmtoff = tx->tm_gmtoff;
tm->tm_zone = tx->tm_zone;
*tzp = -(tm->tm_gmtoff); /* tm_gmtoff is Sun/DEC-ism */
if (tzn != NULL)
*tzn = (char *) tm->tm_zone;
#elif defined(HAVE_INT_TIMEZONE)
*tzp = ((tm->tm_isdst > 0) ? (TIMEZONE_GLOBAL - 3600) : TIMEZONE_GLOBAL);
if (tzn != NULL)
*tzn = tzname[(tm->tm_isdst > 0)];
#endif
#else /* not (HAVE_TM_ZONE || HAVE_INT_TIMEZONE) */
*tzp = 0;
/* Mark this as *no* time zone available */
tm->tm_isdst = -1;
if (tzn != NULL)
*tzn = NULL;
#endif
dt = dt2local(dt, *tzp);
}
else
{
*tzp = 0;
/* Mark this as *no* time zone available */
tm->tm_isdst = -1;
if (tzn != NULL)
*tzn = NULL;
}
}
else
{
tm->tm_isdst = -1;
if (tzn != NULL)
*tzn = NULL;
}
return 0;
} /* timestamp2tm() */
