pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedlibpq ERROR: overflow on numeric ABS(value) ...
7+ messages / 2 participants
[nested] [flat]
* libpq ERROR: overflow on numeric ABS(value) ...
@ 2003-08-07 21:25 markw@osdl.org
0 siblings, 1 reply; 7+ messages in thread
From: markw@osdl.org @ 2003-08-07 21:25 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: osdldbt-general@lists.sourceforge.net
Hello, I need some help understanding an error message. I've got a
program using libpq where I do a:
DECLARE mycursor CURSOR FOR SELECT new_order(...)
FETCH ALL IN mycursor
and I get the resulting error from the FETCH more than half the time:
ERROR: overflow on numeric ABS(value) >= 10^4 for field with precision 4 scale 0
My new_order function is a C library that returns an integer that
returns a number from 0 to 30.
Any ideas or suggestions?
Thanks!
--
Mark Wong - - markw@osdl.org
Open Source Development Lab Inc - A non-profit corporation
12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005
(503)-626-2455 x 32 (office)
(503)-626-2436 (fax)
http://www.osdl.org/archive/markw/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: libpq ERROR: overflow on numeric ABS(value) ...
@ 2003-08-07 21:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: markw@osdl.org
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2003-08-07 21:49 UTC (permalink / raw)
To: markw@osdl.org; +Cc: pgsql-interfaces; osdldbt-general@lists.sourceforge.net
markw@osdl.org writes:
> Hello, I need some help understanding an error message. I've got a
> program using libpq where I do a:
> DECLARE mycursor CURSOR FOR SELECT new_order(...)
> FETCH ALL IN mycursor
> and I get the resulting error from the FETCH more than half the time:
> ERROR: overflow on numeric ABS(value) >= 10^4 for field with precision 4 scale 0
> My new_order function is a C library that returns an integer that
> returns a number from 0 to 30.
I'm betting on a bug in your C code. But we can't guess what without
more details.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: libpq ERROR: overflow on numeric ABS(value) ...
@ 2003-08-07 21:56 markw@osdl.org
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: markw@osdl.org @ 2003-08-07 21:56 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-interfaces; osdldbt-general@lists.sourceforge.net
On 7 Aug, Tom Lane wrote:
> markw@osdl.org writes:
>> Hello, I need some help understanding an error message. I've got a
>> program using libpq where I do a:
>> DECLARE mycursor CURSOR FOR SELECT new_order(...)
>> FETCH ALL IN mycursor
>
>> and I get the resulting error from the FETCH more than half the time:
>> ERROR: overflow on numeric ABS(value) >= 10^4 for field with precision 4 scale 0
>
>> My new_order function is a C library that returns an integer that
>> returns a number from 0 to 30.
>
> I'm betting on a bug in your C code. But we can't guess what without
> more details.
>
Does it make sense that is throws the error on the FETCH instead of the
previous statement? I've attached the table definitions, create
function statement, and the C stored function. Does that help?
Thanks,
Mark
create table warehouse ( w_id numeric(9), w_name varchar(10), w_street_1 varchar(20), w_street_2 varchar(20), w_city varchar(20), w_state char(2), w_zip char(9), w_tax numeric(8, 4), w_ytd numeric(24, 12), constraint pk_warehouse primary key (w_id) );
create table district ( d_id numeric(2), d_w_id numeric(9), d_name varchar(10), d_street_1 varchar(20), d_street_2 varchar(20), d_city varchar(20), d_state char(2), d_zip char(9), d_tax numeric(8, 4), d_ytd numeric(24, 12), d_next_o_id numeric(8), constraint pk_district primary key (d_w_id, d_id) );
create table customer ( c_id numeric(5), c_d_id numeric(2), c_w_id numeric(9), c_first varchar(16), c_middle char(2), c_last varchar(16), c_street_1 varchar(20), c_street_2 varchar(20), c_city varchar(20), c_state char(2), c_zip char(9), c_phone char(16), c_since timestamp, c_credit char(2), c_credit_lim numeric(24, 12), c_discount numeric(8, 4), c_balance numeric(24, 12), c_ytd_payment numeric(24, 12), c_payment_cnt numeric(4), c_delivery_cnt numeric(4), c_data varchar(500), constraint pk_customer primary key (c_w_id, c_d_id, c_id) );
create table history ( h_c_id numeric(5), h_c_d_id numeric(2), h_c_w_id numeric(9), h_d_id numeric(2), h_w_id numeric(9), h_date timestamp, h_amount numeric(12, 6), h_data varchar(24) );
create table new_order ( no_o_id numeric(8), no_d_id numeric(2), no_w_id numeric(9), constraint pk_new_order primary key (no_w_id, no_d_id, no_o_id) );
create table orders ( o_id numeric(8), o_d_id numeric(2), o_w_id numeric(9), o_c_id numeric(5), o_entry_d timestamp, o_carrier_id numeric(2), o_ol_cnt numeric(2), o_all_local numeric(1), constraint pk_orders primary key (o_w_id, o_d_id, o_id) );
create table order_line ( ol_o_id numeric(8), ol_d_id numeric(2), ol_w_id numeric(9), ol_number numeric(2), ol_i_id numeric(6), ol_supply_w_id numeric(9), ol_delivery_d timestamp, ol_quantity numeric(4), ol_amount numeric (12, 6), ol_dist_info varchar(24), constraint pk_order_line primary key (ol_w_id, ol_d_id, ol_o_id, ol_number) );
create table item ( i_id numeric(6), i_im_id numeric(6), i_name varchar(24), i_price numeric(10, 5), i_data varchar(50), constraint pk_item primary key (i_id) );
create table stock ( s_i_id numeric(6), s_w_id numeric(9), s_quantity numeric(4), s_dist_01 varchar(24), s_dist_02 varchar(24), s_dist_03 varchar(24), s_dist_04 varchar(24), s_dist_05 varchar(24), s_dist_06 varchar(24), s_dist_07 varchar(24), s_dist_08 varchar(24), s_dist_09 varchar(24), s_dist_10 varchar(24), s_ytd numeric(16, 8), s_order_cnt numeric(8, 4), s_remote_cnt numeric(8, 4), s_data varchar(50), constraint pk_stock primary key (s_w_id, s_i_id, s_quantity) );
/*
* This file is released under the terms of the Artistic License. Please see
* the file LICENSE, included in this package, for details.
*
* Copyright (C) 2003 Mark Wong & Open Source Development Lab, Inc.
*/
CREATE OR REPLACE FUNCTION new_order (INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) RETURNS INTEGER AS '/s1/tmp/dbt2/storedproc/pgsql/c/funcs' LANGUAGE 'c';
/*
* This file is released under the terms of the Artistic License. Please see
* the file LICENSE, included in this package, for details.
*
* Copyright (C) 2003 Mark Wong & Open Source Development Lab, Inc.
*
* Based on TPC-C Standard Specification Revision 5.0 Clause 2.8.2.
*/
#include <postgres.h>
#include <fmgr.h>
#include <executor/spi.h>
/*
#define DEBUG
*/
#define DELIVERY_1 \
"SELECT no_o_id\n" \
"FROM new_order\n" \
"WHERE no_w_id = %d\n" \
" AND no_d_id = %d"
#define DELIVERY_2 \
"DELETE FROM new_order\n" \
"WHERE no_o_id = %s\n" \
" AND no_w_id = %d\n" \
" AND no_d_id = %d"
#define DELIVERY_3 \
"SELECT o_c_id\n" \
"FROM orders\n" \
"WHERE o_id = %s\n" \
" AND o_w_id = %d\n" \
" AND o_d_id = %d"
#define DELIVERY_4 \
"UPDATE orders\n" \
"SET o_carrier_id = %d\n" \
"WHERE o_id = %s\n" \
" AND o_w_id = %d\n" \
" AND o_d_id = %d"
#define DELIVERY_5 \
"UPDATE order_line\n" \
"SET ol_delivery_d = current_timestamp\n" \
"WHERE ol_o_id = %s\n" \
" AND ol_w_id = %d\n" \
" AND ol_d_id = %d"
#define DELIVERY_6 \
"SELECT SUM(ol_amount * ol_quantity)\n" \
"FROM order_line\n" \
"WHERE ol_o_id = %s\n" \
" AND ol_w_id = %d\n" \
" AND ol_d_id = %d"
#define DELIVERY_7 \
"UPDATE customer\n" \
"SET c_delivery_cnt = c_delivery_cnt + 1,\n" \
" c_balance = c_balance + %s\n" \
"WHERE c_id = %s\n" \
" AND c_w_id = %d\n" \
" AND c_d_id = %d"
#define NEW_ORDER_1 \
"SELECT w_tax\n" \
"FROM warehouse\n" \
"WHERE w_id = %d"
#define NEW_ORDER_2 \
"SELECT d_tax, d_next_o_id\n" \
"FROM district \n" \
"WHERE d_w_id = %d\n" \
" AND d_id = %d"
#define NEW_ORDER_3 \
"UPDATE district\n" \
"SET d_next_o_id = d_next_o_id + 1\n" \
"WHERE d_w_id = %d\n" \
" AND d_id = %d"
#define NEW_ORDER_4 \
"SELECT c_discount, c_last, c_credit\n" \
"FROM customer\n" \
"WHERE c_w_id = %d\n" \
" AND c_d_id = %d\n" \
" AND c_id = %d"
#define NEW_ORDER_5 \
"INSERT INTO new_order (no_o_id, no_d_id, no_w_id)\n" \
"VALUES (%s, %d, %d)"
#define NEW_ORDER_6 \
"INSERT INTO orders (o_id, o_d_id, o_w_id, o_c_id, o_entry_d,\n" \
" o_carrier_id, o_ol_cnt, o_all_local)\n" \
"VALUES (%s, %d, %d, %d, current_timestamp, NULL, %d, %d)"
#define NEW_ORDER_7 \
"SELECT i_price, i_name, i_data\n" \
"FROM item\n" \
"WHERE i_id = %d"
#define NEW_ORDER_8 \
"SELECT s_quantity, %s, s_data\n" \
"FROM stock\n" \
"WHERE s_i_id = %d\n" \
" AND s_w_id = %d"
#define NEW_ORDER_9 \
"UPDATE stock "\
"SET s_quantity = s_quantity - %d\n" \
"WHERE s_i_id = %d\n" \
" AND s_w_id = %d"
#define NEW_ORDER_10 \
"INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number,\n" \
" ol_i_id, ol_supply_w_id, ol_delivery_d,\n" \
" ol_quantity, ol_amount, ol_dist_info)\n" \
"VALUES (%s, %d, %d, %d, %d, %d, NULL, %d, %f, '%s')"
#define ORDER_STATUS_1 \
"SELECT c_id\n" \
"FROM customer\n" \
"WHERE c_w_id = %d\n" \
" AND c_d_id = %d\n" \
" AND c_last = '%s'\n" \
"ORDER BY c_first ASC"
#define ORDER_STATUS_2 \
"SELECT c_first, c_middle, c_last, c_balance\n" \
"FROM customer\n" \
"WHERE c_w_id = %d\n" \
" AND c_d_id = %d\n" \
" AND c_id = %d"
#define ORDER_STATUS_3 \
"SELECT o_id, o_carrier_id, o_entry_d, o_ol_cnt\n" \
"FROM orders\n" \
"WHERE o_w_id = %d\n" \
" AND o_d_id = %d \n" \
" AND o_c_id = %d\n" \
"ORDER BY o_id DESC"
#define ORDER_STATUS_4 \
"SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount,\n" \
" ol_delivery_d\n" \
"FROM order_line\n" \
"WHERE ol_w_id = %d\n" \
" AND ol_d_id = %d\n" \
" AND ol_o_id = %s"
#define PAYMENT_1 \
"SELECT w_name, w_street_1, w_street_2, w_city, w_state, w_zip\n" \
"FROM warehouse\n" \
"WHERE w_id = %d"
#define PAYMENT_2 \
"UPDATE warehouse\n" \
"SET w_ytd = w_ytd + %f\n" \
"WHERE w_id = %d"
#define PAYMENT_3 \
"SELECT d_name, d_street_1, d_street_2, d_city, d_state, d_zip\n" \
"FROM district\n" \
"WHERE d_id = %d\n" \
" AND d_w_id = %d"
#define PAYMENT_4 \
"UPDATE district\n" \
"SET d_ytd = d_ytd + %f\n" \
"WHERE d_id = %d\n" \
" AND d_w_id = %d"
#define PAYMENT_5 \
"SELECT c_id\n" \
"FROM customer\n" \
"WHERE c_w_id = %d\n" \
" AND c_d_id = %d\n" \
" AND c_last = '%s'\n" \
"ORDER BY c_first ASC"
#define PAYMENT_6 \
"SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city,\n" \
" c_state, c_zip, c_phone, c_since, c_credit,\n" \
" c_credit_lim, c_discount, c_balance, c_data, c_ytd_payment\n" \
"FROM customer\n" \
"WHERE c_w_id = %d\n" \
" AND c_d_id = %d\n" \
" AND c_id = %d"
#define PAYMENT_7_GC \
"UPDATE customer\n" \
"SET c_balance = c_balance - %f,\n" \
" c_ytd_payment = c_ytd_payment + 1\n" \
"WHERE c_id = %d\n" \
" AND c_w_id = %d\n" \
" AND c_d_id = %d"
#define PAYMENT_7_BC \
"UPDATE customer\n" \
"SET c_balance = c_balance - %f,\n" \
" c_ytd_payment = c_ytd_payment + 1,\n" \
" c_data = '%s'\n" \
"WHERE c_id = %d\n" \
" AND c_w_id = %d\n" \
" AND c_d_id = %d"
#define PAYMENT_8 \
"INSERT INTO history (h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id,\n" \
" h_date, h_amount, h_data)\n" \
"VALUES (%d, %d, %d, %d, %d, current_timestamp, %f, '%s %s')"
#define STOCK_LEVEL_1 \
"SELECT d_next_o_id\n" \
"FROM district\n" \
"WHERE d_w_id = %d\n" \
"AND d_id = %d"
#define STOCK_LEVEL_2 \
"SELECT count(*)\n" \
"FROM order_line, stock, district\n" \
"WHERE d_id = %d\n" \
" AND d_w_id = %d\n" \
" AND d_id = ol_d_id\n" \
" AND d_w_id = ol_w_id\n" \
" AND ol_i_id = s_i_id\n" \
" AND ol_w_id = s_w_id\n" \
" AND s_quantity < %d\n" \
" AND ol_o_id BETWEEN (%d)\n" \
" AND (%d)"
/* Prototypes to prevent potential gcc warnings. */
Datum delivery(PG_FUNCTION_ARGS);
Datum new_order(PG_FUNCTION_ARGS);
Datum order_status(PG_FUNCTION_ARGS);
Datum payment(PG_FUNCTION_ARGS);
Datum stock_level(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(delivery);
PG_FUNCTION_INFO_V1(new_order);
PG_FUNCTION_INFO_V1(order_status);
PG_FUNCTION_INFO_V1(payment);
PG_FUNCTION_INFO_V1(stock_level);
const char s_dist[10][11] = {
"s_dist_01", "s_dist_02", "s_dist_03", "s_dist_04", "s_dist_05",
"s_dist_06", "s_dist_07", "s_dist_08", "s_dist_09", "s_dist_10"
};
void escape_str(char *orig_str, char *esc_str)
{
int i, j;
for (i = 0, j = 0; i < strlen(orig_str); i++) {
if (orig_str[i] == '\'') {
esc_str[j++] = '\'';
} else if (orig_str[i] == '\\') {
esc_str[j++] = '\\';
} else if (orig_str[i] == ')') {
esc_str[j++] = '\\';
}
esc_str[j++] = orig_str[i];
}
esc_str[j] = '\0';
}
Datum delivery(PG_FUNCTION_ARGS)
{
/* Input variables. */
int32 w_id = PG_GETARG_INT32(0);
int32 o_carrier_id = PG_GETARG_INT32(1);
TupleDesc tupdesc;
SPITupleTable *tuptable;
HeapTuple tuple;
char query[256];
int d_id;
int ret;
char *no_o_id = NULL;
char *o_c_id = NULL;
char *ol_amount = NULL;
SPI_connect();
for (d_id = 1; d_id <= 10; d_id++) {
sprintf(query, DELIVERY_1, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
no_o_id = SPI_getvalue(tuple, tupdesc, 1);
#ifdef DEBUG
elog(NOTICE, "no_o_id = %s", no_o_id);
#endif /* DEBUG */
} else {
/* Nothing to delivery for this district, try next. */
continue;
}
sprintf(query, DELIVERY_2, no_o_id, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_DELETE) {
PG_RETURN_INT32(-1);
}
sprintf(query, DELIVERY_3, no_o_id, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
o_c_id = SPI_getvalue(tuple, tupdesc, 1);
#ifdef DEBUG
elog(NOTICE, "o_c_id = %s", no_o_id);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(-1);
}
sprintf(query, DELIVERY_4, o_carrier_id, no_o_id, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(-1);
}
sprintf(query, DELIVERY_5, no_o_id, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(-1);
}
sprintf(query, DELIVERY_6, no_o_id, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
ol_amount = SPI_getvalue(tuple, tupdesc, 1);
#ifdef DEBUG
elog(NOTICE, "ol_amount = %s", no_o_id);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(-1);
}
sprintf(query, DELIVERY_7, ol_amount, o_c_id, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(-1);
}
}
SPI_finish();
PG_RETURN_INT32(1);
}
Datum new_order(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 o_all_local = PG_GETARG_INT32(3);
int32 o_ol_cnt = PG_GETARG_INT32(4);
TupleDesc tupdesc;
SPITupleTable *tuptable;
HeapTuple tuple;
int32 ol_i_id[15];
int32 ol_supply_w_id[15];
int32 ol_quantity[15];
int i, j;
int ret;
char query[1024];
char *w_tax = NULL;
char *d_tax = NULL;
char *d_next_o_id = NULL;
char *c_discount = NULL;
char *c_last = NULL;
char *c_credit = NULL;
float order_amount = 0.0;
char *i_price[15];
char *i_name[15];
char *i_data[15];
float ol_amount[15];
char *s_quantity[15];
char *my_s_dist[15];
char *s_data[15];
/* Loop through the last set of parameters. */
for (i = 0, j = 5; i < 15; i++) {
ol_i_id[i] = PG_GETARG_INT32(j++);
ol_supply_w_id[i] = PG_GETARG_INT32(j++);
ol_quantity[i] = PG_GETARG_INT32(j++);
}
#ifdef DEBUG
elog(NOTICE, "w_id = %d", w_id);
elog(NOTICE, "d_id = %d", d_id);
elog(NOTICE, "c_id = %d", c_id);
elog(NOTICE, "o_all_local = %d", o_all_local);
elog(NOTICE, "o_ol_cnt = %d", o_ol_cnt);
elog(NOTICE, "## ol_i_id ol_supply_w_id ol_quantity");
elog(NOTICE, "-- ------- -------------- -----------");
for (i = 0; i < o_ol_cnt; i++) {
elog(NOTICE, "%2d %7d %14d %11d",
i + 1, ol_i_id[i], ol_supply_w_id[i], ol_quantity[i]);
}
#endif /* DEBUG */
SPI_connect();
sprintf(query, NEW_ORDER_1, w_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
w_tax = SPI_getvalue(tuple, tupdesc, 1);
#ifdef DEBUG
elog(NOTICE, "w_tax = %s", w_tax);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(10);
}
sprintf(query, NEW_ORDER_2, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
d_tax = SPI_getvalue(tuple, tupdesc, 1);
d_next_o_id = SPI_getvalue(tuple, tupdesc, 2);
#ifdef DEBUG
elog(NOTICE, "d_tax = %s", d_tax);
elog(NOTICE, "d_next_o_id = %s", d_next_o_id);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(11);
}
sprintf(query, NEW_ORDER_3, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(12);
}
sprintf(query, NEW_ORDER_4, w_id, d_id, c_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
c_discount = SPI_getvalue(tuple, tupdesc, 1);
c_last = SPI_getvalue(tuple, tupdesc, 2);
c_credit = SPI_getvalue(tuple, tupdesc, 3);
#ifdef DEBUG
elog(NOTICE, "c_discount = %s", c_discount);
elog(NOTICE, "c_last = %s", c_last);
elog(NOTICE, "c_credit = %s", c_credit);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(13);
}
sprintf(query, NEW_ORDER_5, d_next_o_id, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_INSERT) {
PG_RETURN_INT32(14);
}
sprintf(query, NEW_ORDER_6, d_next_o_id, d_id, w_id, c_id, o_ol_cnt,
o_all_local);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_INSERT) {
PG_RETURN_INT32(15);
}
for (i = 0; i < o_ol_cnt; i++) {
sprintf(query, NEW_ORDER_7, ol_i_id[i]);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
/*
* Shouldn't have to check if ol_i_id is 0, but if the row
* doesn't exist, the query still passes.
*/
if (ol_i_id[i] != 0 &&
ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
i_price[i] = SPI_getvalue(tuple, tupdesc, 1);
i_name[i] = SPI_getvalue(tuple, tupdesc, 2);
i_data[i] = SPI_getvalue(tuple, tupdesc, 3);
#ifdef DEBUG
elog(NOTICE, "i_price[%d] = %s", i, i_price[i]);
elog(NOTICE, "i_name[%d] = %s", i, i_name[i]);
elog(NOTICE, "i_data[%d] = %s", i, i_data[i]);
#endif /* DEBUG */
} else {
/* Item doesn't exist, rollback transaction. */
PG_RETURN_INT32(2);
}
ol_amount[i] = atof(i_price[i]) * (float) ol_quantity[i];
sprintf(query, NEW_ORDER_8, s_dist[d_id - 1], ol_i_id[i], w_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
s_quantity[i] = SPI_getvalue(tuple, tupdesc, 1);
my_s_dist[i] = SPI_getvalue(tuple, tupdesc, 2);
s_data[i] = SPI_getvalue(tuple, tupdesc, 3);
#ifdef DEBUG
elog(NOTICE, "s_quantity[%d] = %s", i, s_quantity[i]);
elog(NOTICE, "my_s_dist[%d] = %s", i, my_s_dist[i]);
elog(NOTICE, "s_data[%d] = %s", i, s_data[i]);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(16);
}
order_amount += ol_amount[i];
if (atoi(s_quantity[i]) > ol_quantity[i] + 10) {
sprintf(query, NEW_ORDER_9, ol_quantity[i],
ol_i_id[i], w_id);
} else {
sprintf(query, NEW_ORDER_9, ol_quantity[i] + 91,
ol_i_id[i], w_id);
}
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(17);
}
sprintf(query, NEW_ORDER_10, d_next_o_id, d_id, w_id, i + 1,
ol_i_id[i], ol_supply_w_id[i], ol_quantity[i],
ol_amount[i], my_s_dist[i]);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_INSERT) {
PG_RETURN_INT32(18);
}
}
SPI_finish();
PG_RETURN_INT32(0);
}
Datum order_status(PG_FUNCTION_ARGS)
{
/* Input variables. */
int32 c_id = PG_GETARG_INT32(0);
int32 c_w_id = PG_GETARG_INT32(1);
int32 c_d_id = PG_GETARG_INT32(2);
text *c_last = PG_GETARG_TEXT_P(3);
TupleDesc tupdesc;
SPITupleTable *tuptable;
HeapTuple tuple;
int ret;
int count;
int j;
char query[512];
char *tmp_c_id;
int my_c_id = 0;
char *c_first = NULL;
char *c_middle = NULL;
char *my_c_last = NULL;
char *c_balance = NULL;
char *o_id = NULL;
char *o_carrier_id = NULL;
char *o_entry_d = NULL;
char *o_ol_cnt = NULL;
char *ol_i_id[15];
char *ol_supply_w_id[15];
char *ol_quantity[15];
char *ol_amount[15];
char *ol_delivery_d[15];
SPI_connect();
if (c_id == 0) {
sprintf(query, ORDER_STATUS_1, c_w_id, c_d_id,
DatumGetCString(DirectFunctionCall1(textout,
PointerGetDatum(c_last))));
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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);
#ifdef DEBUG
elog(NOTICE, "c_id = %s, %d total, selected %d",
tmp_c_id, count, count / 2);
#endif /* DEBUG */
my_c_id = atoi(tmp_c_id);
} else {
PG_RETURN_INT32(-1);
}
} else {
my_c_id = c_id;
}
sprintf(query, ORDER_STATUS_2, c_w_id, c_d_id, my_c_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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_balance = SPI_getvalue(tuple, tupdesc, 4);
#ifdef DEBUG
elog(NOTICE, "c_first = %s", c_first);
elog(NOTICE, "c_middle = %s", c_middle);
elog(NOTICE, "c_last = %s", my_c_last);
elog(NOTICE, "c_balance = %s", c_balance);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(-1);
}
/* Maybe this should be a join with the previous query. */
sprintf(query, ORDER_STATUS_3, c_w_id, c_d_id, my_c_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
o_id = SPI_getvalue(tuple, tupdesc, 1);
o_carrier_id = SPI_getvalue(tuple, tupdesc, 2);
o_entry_d = SPI_getvalue(tuple, tupdesc, 3);
o_ol_cnt = SPI_getvalue(tuple, tupdesc, 4);
#ifdef DEBUG
elog(NOTICE, "o_id = %s", o_id);
elog(NOTICE, "o_carrier_id = %s", o_carrier_id);
elog(NOTICE, "o_entry_d = %s", o_entry_d);
elog(NOTICE, "o_ol_cnt = %s", o_ol_cnt);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(-1);
}
sprintf(query, ORDER_STATUS_4, c_w_id, c_d_id, o_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
count = SPI_processed;
#ifdef DEBUG
elog(NOTICE, "## ol_i_id ol_supply_w_id ol_quantity ol_amount ol_delivery_d");
elog(NOTICE, "-- ------- -------------- ----------- --------- -------------");
#endif /* DEBUG */
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
for (j = 0; j < count; j++) {
tuple = tuptable->vals[j];
ol_i_id[j] = SPI_getvalue(tuple, tupdesc, 1);
ol_supply_w_id[j] = SPI_getvalue(tuple, tupdesc, 2);
ol_quantity[j] = SPI_getvalue(tuple, tupdesc, 3);
ol_amount[j] = SPI_getvalue(tuple, tupdesc, 4);
ol_delivery_d[j] = SPI_getvalue(tuple, tupdesc, 5);
#ifdef DEBUG
elog(NOTICE, "%2d %7s %14s %11s %9.2f %13s",
j + 1, ol_i_id[j], ol_supply_w_id[j],
ol_quantity[j], atof(ol_amount[j]),
ol_delivery_d[j]);
#endif /* DEBUG */
}
} else {
PG_RETURN_INT32(-1);
}
SPI_finish();
PG_RETURN_INT32(1);
}
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 query[4096];
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;
char my_w_name[20];
char my_d_name[20];
#ifdef DEBUG
elog(NOTICE, "w_id = %d", w_id);
elog(NOTICE, "d_id = %d", d_id);
elog(NOTICE, "c_id = %d", c_id);
elog(NOTICE, "c_w_id = %d", c_w_id);
elog(NOTICE, "c_d_id = %d", c_d_id);
elog(NOTICE, "c_last = %s",
DatumGetCString(DirectFunctionCall1(textout,
PointerGetDatum(c_last))));
elog(NOTICE, "h_amount = %f", h_amount);
#endif /* DEBUG */
SPI_connect();
sprintf(query, PAYMENT_1, w_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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);
#ifdef DEBUG
elog(NOTICE, "w_name = %s", w_name);
elog(NOTICE, "w_street_1 = %s", w_street_1);
elog(NOTICE, "w_street_2 = %s", w_street_2);
elog(NOTICE, "w_city = %s", w_city);
elog(NOTICE, "w_state = %s", w_state);
elog(NOTICE, "w_zip = %s", w_zip);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(-1);
}
sprintf(query, PAYMENT_2, h_amount, w_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(-1);
}
sprintf(query, PAYMENT_3, d_id, w_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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);
#ifdef DEBUG
elog(NOTICE, "d_name = %s", d_name);
elog(NOTICE, "d_street_1 = %s", d_street_1);
elog(NOTICE, "d_street_2 = %s", d_street_2);
elog(NOTICE, "d_city = %s", d_city);
elog(NOTICE, "d_state = %s", d_state);
elog(NOTICE, "d_zip = %s", d_zip);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(-1);
}
sprintf(query, PAYMENT_4, h_amount, d_id, w_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(-1);
}
if (c_id == 0) {
sprintf(query, PAYMENT_5, w_id, d_id,
DatumGetCString(DirectFunctionCall1(textout,
PointerGetDatum(c_last))));
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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);
#ifdef DEBUG
elog(NOTICE, "c_id = %s, %d total, selected %d",
tmp_c_id, count, count / 2);
#endif /* DEBUG */
my_c_id = atoi(tmp_c_id);
} else {
PG_RETURN_INT32(-1);
}
} else {
my_c_id = c_id;
}
sprintf(query, PAYMENT_6, c_w_id, c_d_id, my_c_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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);
#ifdef DEBUG
elog(NOTICE, "c_first = %s", c_first);
elog(NOTICE, "c_middle = %s", c_middle);
elog(NOTICE, "c_last = %s", my_c_last);
elog(NOTICE, "c_street_1 = %s", c_street_1);
elog(NOTICE, "c_street_2 = %s", c_street_2);
elog(NOTICE, "c_city = %s", c_city);
elog(NOTICE, "c_state = %s", c_state);
elog(NOTICE, "c_zip = %s", c_zip);
elog(NOTICE, "c_phone = %s", c_phone);
elog(NOTICE, "c_since = %s", c_since);
elog(NOTICE, "c_credit = %s", c_credit);
elog(NOTICE, "c_credit_lim = %s", c_credit_lim);
elog(NOTICE, "c_discount = %s", c_discount);
elog(NOTICE, "c_balance = %s", c_balance);
elog(NOTICE, "c_data = %s", c_data);
elog(NOTICE, "c_ytd_payment = %s", c_ytd_payment);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(-1);
}
/* It's either "BC" or "GC". */
if (c_credit[0] == 'G') {
sprintf(query, PAYMENT_7_GC, h_amount, my_c_id, c_w_id, c_d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
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);
/* Copy and escape all at once! */
escape_str(c_data, my_c_data);
sprintf(query, PAYMENT_7_BC, h_amount, my_c_data, my_c_id,
c_w_id, c_d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(-1);
}
}
/* Escape special characters. */
escape_str(w_name, my_w_name);
escape_str(d_name, my_d_name);
sprintf(query, PAYMENT_8, my_c_id, c_d_id, c_w_id, d_id, w_id,
h_amount, my_w_name, my_d_name);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_INSERT) {
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 query[256];
char *buf;
SPI_connect();
sprintf(query, STOCK_LEVEL_1, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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);
#ifdef DEBUG
elog(NOTICE, "d_next_o_id = %s", buf);
#endif /* DEBUG */
d_next_o_id = atoi(buf);
} else {
PG_RETURN_INT32(-1);
}
sprintf(query, STOCK_LEVEL_2, w_id, d_id, threshold, d_next_o_id - 20,
d_next_o_id - 1);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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);
#ifdef DEBUG
elog(NOTICE, "low_stock = %s", buf);
#endif /* DEBUG */
low_stock = atoi(buf);
} else {
PG_RETURN_INT32(-1);
}
SPI_finish();
PG_RETURN_INT32(low_stock);
}
Attachments:
[text/plain] create_tables.sql (2.6K, ../../200308072156.h77LuUo16238@mail.osdl.org/2-create_tables.sql)
download | inline:
create table warehouse ( w_id numeric(9), w_name varchar(10), w_street_1 varchar(20), w_street_2 varchar(20), w_city varchar(20), w_state char(2), w_zip char(9), w_tax numeric(8, 4), w_ytd numeric(24, 12), constraint pk_warehouse primary key (w_id) );
create table district ( d_id numeric(2), d_w_id numeric(9), d_name varchar(10), d_street_1 varchar(20), d_street_2 varchar(20), d_city varchar(20), d_state char(2), d_zip char(9), d_tax numeric(8, 4), d_ytd numeric(24, 12), d_next_o_id numeric(8), constraint pk_district primary key (d_w_id, d_id) );
create table customer ( c_id numeric(5), c_d_id numeric(2), c_w_id numeric(9), c_first varchar(16), c_middle char(2), c_last varchar(16), c_street_1 varchar(20), c_street_2 varchar(20), c_city varchar(20), c_state char(2), c_zip char(9), c_phone char(16), c_since timestamp, c_credit char(2), c_credit_lim numeric(24, 12), c_discount numeric(8, 4), c_balance numeric(24, 12), c_ytd_payment numeric(24, 12), c_payment_cnt numeric(4), c_delivery_cnt numeric(4), c_data varchar(500), constraint pk_customer primary key (c_w_id, c_d_id, c_id) );
create table history ( h_c_id numeric(5), h_c_d_id numeric(2), h_c_w_id numeric(9), h_d_id numeric(2), h_w_id numeric(9), h_date timestamp, h_amount numeric(12, 6), h_data varchar(24) );
create table new_order ( no_o_id numeric(8), no_d_id numeric(2), no_w_id numeric(9), constraint pk_new_order primary key (no_w_id, no_d_id, no_o_id) );
create table orders ( o_id numeric(8), o_d_id numeric(2), o_w_id numeric(9), o_c_id numeric(5), o_entry_d timestamp, o_carrier_id numeric(2), o_ol_cnt numeric(2), o_all_local numeric(1), constraint pk_orders primary key (o_w_id, o_d_id, o_id) );
create table order_line ( ol_o_id numeric(8), ol_d_id numeric(2), ol_w_id numeric(9), ol_number numeric(2), ol_i_id numeric(6), ol_supply_w_id numeric(9), ol_delivery_d timestamp, ol_quantity numeric(4), ol_amount numeric (12, 6), ol_dist_info varchar(24), constraint pk_order_line primary key (ol_w_id, ol_d_id, ol_o_id, ol_number) );
create table item ( i_id numeric(6), i_im_id numeric(6), i_name varchar(24), i_price numeric(10, 5), i_data varchar(50), constraint pk_item primary key (i_id) );
create table stock ( s_i_id numeric(6), s_w_id numeric(9), s_quantity numeric(4), s_dist_01 varchar(24), s_dist_02 varchar(24), s_dist_03 varchar(24), s_dist_04 varchar(24), s_dist_05 varchar(24), s_dist_06 varchar(24), s_dist_07 varchar(24), s_dist_08 varchar(24), s_dist_09 varchar(24), s_dist_10 varchar(24), s_ytd numeric(16, 8), s_order_cnt numeric(8, 4), s_remote_cnt numeric(8, 4), s_data varchar(50), constraint pk_stock primary key (s_w_id, s_i_id, s_quantity) );
[text/plain] new_order.sql (776B, ../../200308072156.h77LuUo16238@mail.osdl.org/3-new_order.sql)
download | inline:
/*
* This file is released under the terms of the Artistic License. Please see
* the file LICENSE, included in this package, for details.
*
* Copyright (C) 2003 Mark Wong & Open Source Development Lab, Inc.
*/
CREATE OR REPLACE FUNCTION new_order (INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) RETURNS INTEGER AS '/s1/tmp/dbt2/storedproc/pgsql/c/funcs' LANGUAGE 'c';
[text/plain] funcs.c (27.0K, ../../200308072156.h77LuUo16238@mail.osdl.org/4-funcs.c)
download | inline:
/*
* This file is released under the terms of the Artistic License. Please see
* the file LICENSE, included in this package, for details.
*
* Copyright (C) 2003 Mark Wong & Open Source Development Lab, Inc.
*
* Based on TPC-C Standard Specification Revision 5.0 Clause 2.8.2.
*/
#include <postgres.h>
#include <fmgr.h>
#include <executor/spi.h>
/*
#define DEBUG
*/
#define DELIVERY_1 \
"SELECT no_o_id\n" \
"FROM new_order\n" \
"WHERE no_w_id = %d\n" \
" AND no_d_id = %d"
#define DELIVERY_2 \
"DELETE FROM new_order\n" \
"WHERE no_o_id = %s\n" \
" AND no_w_id = %d\n" \
" AND no_d_id = %d"
#define DELIVERY_3 \
"SELECT o_c_id\n" \
"FROM orders\n" \
"WHERE o_id = %s\n" \
" AND o_w_id = %d\n" \
" AND o_d_id = %d"
#define DELIVERY_4 \
"UPDATE orders\n" \
"SET o_carrier_id = %d\n" \
"WHERE o_id = %s\n" \
" AND o_w_id = %d\n" \
" AND o_d_id = %d"
#define DELIVERY_5 \
"UPDATE order_line\n" \
"SET ol_delivery_d = current_timestamp\n" \
"WHERE ol_o_id = %s\n" \
" AND ol_w_id = %d\n" \
" AND ol_d_id = %d"
#define DELIVERY_6 \
"SELECT SUM(ol_amount * ol_quantity)\n" \
"FROM order_line\n" \
"WHERE ol_o_id = %s\n" \
" AND ol_w_id = %d\n" \
" AND ol_d_id = %d"
#define DELIVERY_7 \
"UPDATE customer\n" \
"SET c_delivery_cnt = c_delivery_cnt + 1,\n" \
" c_balance = c_balance + %s\n" \
"WHERE c_id = %s\n" \
" AND c_w_id = %d\n" \
" AND c_d_id = %d"
#define NEW_ORDER_1 \
"SELECT w_tax\n" \
"FROM warehouse\n" \
"WHERE w_id = %d"
#define NEW_ORDER_2 \
"SELECT d_tax, d_next_o_id\n" \
"FROM district \n" \
"WHERE d_w_id = %d\n" \
" AND d_id = %d"
#define NEW_ORDER_3 \
"UPDATE district\n" \
"SET d_next_o_id = d_next_o_id + 1\n" \
"WHERE d_w_id = %d\n" \
" AND d_id = %d"
#define NEW_ORDER_4 \
"SELECT c_discount, c_last, c_credit\n" \
"FROM customer\n" \
"WHERE c_w_id = %d\n" \
" AND c_d_id = %d\n" \
" AND c_id = %d"
#define NEW_ORDER_5 \
"INSERT INTO new_order (no_o_id, no_d_id, no_w_id)\n" \
"VALUES (%s, %d, %d)"
#define NEW_ORDER_6 \
"INSERT INTO orders (o_id, o_d_id, o_w_id, o_c_id, o_entry_d,\n" \
" o_carrier_id, o_ol_cnt, o_all_local)\n" \
"VALUES (%s, %d, %d, %d, current_timestamp, NULL, %d, %d)"
#define NEW_ORDER_7 \
"SELECT i_price, i_name, i_data\n" \
"FROM item\n" \
"WHERE i_id = %d"
#define NEW_ORDER_8 \
"SELECT s_quantity, %s, s_data\n" \
"FROM stock\n" \
"WHERE s_i_id = %d\n" \
" AND s_w_id = %d"
#define NEW_ORDER_9 \
"UPDATE stock "\
"SET s_quantity = s_quantity - %d\n" \
"WHERE s_i_id = %d\n" \
" AND s_w_id = %d"
#define NEW_ORDER_10 \
"INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number,\n" \
" ol_i_id, ol_supply_w_id, ol_delivery_d,\n" \
" ol_quantity, ol_amount, ol_dist_info)\n" \
"VALUES (%s, %d, %d, %d, %d, %d, NULL, %d, %f, '%s')"
#define ORDER_STATUS_1 \
"SELECT c_id\n" \
"FROM customer\n" \
"WHERE c_w_id = %d\n" \
" AND c_d_id = %d\n" \
" AND c_last = '%s'\n" \
"ORDER BY c_first ASC"
#define ORDER_STATUS_2 \
"SELECT c_first, c_middle, c_last, c_balance\n" \
"FROM customer\n" \
"WHERE c_w_id = %d\n" \
" AND c_d_id = %d\n" \
" AND c_id = %d"
#define ORDER_STATUS_3 \
"SELECT o_id, o_carrier_id, o_entry_d, o_ol_cnt\n" \
"FROM orders\n" \
"WHERE o_w_id = %d\n" \
" AND o_d_id = %d \n" \
" AND o_c_id = %d\n" \
"ORDER BY o_id DESC"
#define ORDER_STATUS_4 \
"SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount,\n" \
" ol_delivery_d\n" \
"FROM order_line\n" \
"WHERE ol_w_id = %d\n" \
" AND ol_d_id = %d\n" \
" AND ol_o_id = %s"
#define PAYMENT_1 \
"SELECT w_name, w_street_1, w_street_2, w_city, w_state, w_zip\n" \
"FROM warehouse\n" \
"WHERE w_id = %d"
#define PAYMENT_2 \
"UPDATE warehouse\n" \
"SET w_ytd = w_ytd + %f\n" \
"WHERE w_id = %d"
#define PAYMENT_3 \
"SELECT d_name, d_street_1, d_street_2, d_city, d_state, d_zip\n" \
"FROM district\n" \
"WHERE d_id = %d\n" \
" AND d_w_id = %d"
#define PAYMENT_4 \
"UPDATE district\n" \
"SET d_ytd = d_ytd + %f\n" \
"WHERE d_id = %d\n" \
" AND d_w_id = %d"
#define PAYMENT_5 \
"SELECT c_id\n" \
"FROM customer\n" \
"WHERE c_w_id = %d\n" \
" AND c_d_id = %d\n" \
" AND c_last = '%s'\n" \
"ORDER BY c_first ASC"
#define PAYMENT_6 \
"SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city,\n" \
" c_state, c_zip, c_phone, c_since, c_credit,\n" \
" c_credit_lim, c_discount, c_balance, c_data, c_ytd_payment\n" \
"FROM customer\n" \
"WHERE c_w_id = %d\n" \
" AND c_d_id = %d\n" \
" AND c_id = %d"
#define PAYMENT_7_GC \
"UPDATE customer\n" \
"SET c_balance = c_balance - %f,\n" \
" c_ytd_payment = c_ytd_payment + 1\n" \
"WHERE c_id = %d\n" \
" AND c_w_id = %d\n" \
" AND c_d_id = %d"
#define PAYMENT_7_BC \
"UPDATE customer\n" \
"SET c_balance = c_balance - %f,\n" \
" c_ytd_payment = c_ytd_payment + 1,\n" \
" c_data = '%s'\n" \
"WHERE c_id = %d\n" \
" AND c_w_id = %d\n" \
" AND c_d_id = %d"
#define PAYMENT_8 \
"INSERT INTO history (h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id,\n" \
" h_date, h_amount, h_data)\n" \
"VALUES (%d, %d, %d, %d, %d, current_timestamp, %f, '%s %s')"
#define STOCK_LEVEL_1 \
"SELECT d_next_o_id\n" \
"FROM district\n" \
"WHERE d_w_id = %d\n" \
"AND d_id = %d"
#define STOCK_LEVEL_2 \
"SELECT count(*)\n" \
"FROM order_line, stock, district\n" \
"WHERE d_id = %d\n" \
" AND d_w_id = %d\n" \
" AND d_id = ol_d_id\n" \
" AND d_w_id = ol_w_id\n" \
" AND ol_i_id = s_i_id\n" \
" AND ol_w_id = s_w_id\n" \
" AND s_quantity < %d\n" \
" AND ol_o_id BETWEEN (%d)\n" \
" AND (%d)"
/* Prototypes to prevent potential gcc warnings. */
Datum delivery(PG_FUNCTION_ARGS);
Datum new_order(PG_FUNCTION_ARGS);
Datum order_status(PG_FUNCTION_ARGS);
Datum payment(PG_FUNCTION_ARGS);
Datum stock_level(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(delivery);
PG_FUNCTION_INFO_V1(new_order);
PG_FUNCTION_INFO_V1(order_status);
PG_FUNCTION_INFO_V1(payment);
PG_FUNCTION_INFO_V1(stock_level);
const char s_dist[10][11] = {
"s_dist_01", "s_dist_02", "s_dist_03", "s_dist_04", "s_dist_05",
"s_dist_06", "s_dist_07", "s_dist_08", "s_dist_09", "s_dist_10"
};
void escape_str(char *orig_str, char *esc_str)
{
int i, j;
for (i = 0, j = 0; i < strlen(orig_str); i++) {
if (orig_str[i] == '\'') {
esc_str[j++] = '\'';
} else if (orig_str[i] == '\\') {
esc_str[j++] = '\\';
} else if (orig_str[i] == ')') {
esc_str[j++] = '\\';
}
esc_str[j++] = orig_str[i];
}
esc_str[j] = '\0';
}
Datum delivery(PG_FUNCTION_ARGS)
{
/* Input variables. */
int32 w_id = PG_GETARG_INT32(0);
int32 o_carrier_id = PG_GETARG_INT32(1);
TupleDesc tupdesc;
SPITupleTable *tuptable;
HeapTuple tuple;
char query[256];
int d_id;
int ret;
char *no_o_id = NULL;
char *o_c_id = NULL;
char *ol_amount = NULL;
SPI_connect();
for (d_id = 1; d_id <= 10; d_id++) {
sprintf(query, DELIVERY_1, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
no_o_id = SPI_getvalue(tuple, tupdesc, 1);
#ifdef DEBUG
elog(NOTICE, "no_o_id = %s", no_o_id);
#endif /* DEBUG */
} else {
/* Nothing to delivery for this district, try next. */
continue;
}
sprintf(query, DELIVERY_2, no_o_id, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_DELETE) {
PG_RETURN_INT32(-1);
}
sprintf(query, DELIVERY_3, no_o_id, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
o_c_id = SPI_getvalue(tuple, tupdesc, 1);
#ifdef DEBUG
elog(NOTICE, "o_c_id = %s", no_o_id);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(-1);
}
sprintf(query, DELIVERY_4, o_carrier_id, no_o_id, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(-1);
}
sprintf(query, DELIVERY_5, no_o_id, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(-1);
}
sprintf(query, DELIVERY_6, no_o_id, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
ol_amount = SPI_getvalue(tuple, tupdesc, 1);
#ifdef DEBUG
elog(NOTICE, "ol_amount = %s", no_o_id);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(-1);
}
sprintf(query, DELIVERY_7, ol_amount, o_c_id, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(-1);
}
}
SPI_finish();
PG_RETURN_INT32(1);
}
Datum new_order(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 o_all_local = PG_GETARG_INT32(3);
int32 o_ol_cnt = PG_GETARG_INT32(4);
TupleDesc tupdesc;
SPITupleTable *tuptable;
HeapTuple tuple;
int32 ol_i_id[15];
int32 ol_supply_w_id[15];
int32 ol_quantity[15];
int i, j;
int ret;
char query[1024];
char *w_tax = NULL;
char *d_tax = NULL;
char *d_next_o_id = NULL;
char *c_discount = NULL;
char *c_last = NULL;
char *c_credit = NULL;
float order_amount = 0.0;
char *i_price[15];
char *i_name[15];
char *i_data[15];
float ol_amount[15];
char *s_quantity[15];
char *my_s_dist[15];
char *s_data[15];
/* Loop through the last set of parameters. */
for (i = 0, j = 5; i < 15; i++) {
ol_i_id[i] = PG_GETARG_INT32(j++);
ol_supply_w_id[i] = PG_GETARG_INT32(j++);
ol_quantity[i] = PG_GETARG_INT32(j++);
}
#ifdef DEBUG
elog(NOTICE, "w_id = %d", w_id);
elog(NOTICE, "d_id = %d", d_id);
elog(NOTICE, "c_id = %d", c_id);
elog(NOTICE, "o_all_local = %d", o_all_local);
elog(NOTICE, "o_ol_cnt = %d", o_ol_cnt);
elog(NOTICE, "## ol_i_id ol_supply_w_id ol_quantity");
elog(NOTICE, "-- ------- -------------- -----------");
for (i = 0; i < o_ol_cnt; i++) {
elog(NOTICE, "%2d %7d %14d %11d",
i + 1, ol_i_id[i], ol_supply_w_id[i], ol_quantity[i]);
}
#endif /* DEBUG */
SPI_connect();
sprintf(query, NEW_ORDER_1, w_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
w_tax = SPI_getvalue(tuple, tupdesc, 1);
#ifdef DEBUG
elog(NOTICE, "w_tax = %s", w_tax);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(10);
}
sprintf(query, NEW_ORDER_2, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
d_tax = SPI_getvalue(tuple, tupdesc, 1);
d_next_o_id = SPI_getvalue(tuple, tupdesc, 2);
#ifdef DEBUG
elog(NOTICE, "d_tax = %s", d_tax);
elog(NOTICE, "d_next_o_id = %s", d_next_o_id);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(11);
}
sprintf(query, NEW_ORDER_3, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(12);
}
sprintf(query, NEW_ORDER_4, w_id, d_id, c_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
c_discount = SPI_getvalue(tuple, tupdesc, 1);
c_last = SPI_getvalue(tuple, tupdesc, 2);
c_credit = SPI_getvalue(tuple, tupdesc, 3);
#ifdef DEBUG
elog(NOTICE, "c_discount = %s", c_discount);
elog(NOTICE, "c_last = %s", c_last);
elog(NOTICE, "c_credit = %s", c_credit);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(13);
}
sprintf(query, NEW_ORDER_5, d_next_o_id, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_INSERT) {
PG_RETURN_INT32(14);
}
sprintf(query, NEW_ORDER_6, d_next_o_id, d_id, w_id, c_id, o_ol_cnt,
o_all_local);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_INSERT) {
PG_RETURN_INT32(15);
}
for (i = 0; i < o_ol_cnt; i++) {
sprintf(query, NEW_ORDER_7, ol_i_id[i]);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
/*
* Shouldn't have to check if ol_i_id is 0, but if the row
* doesn't exist, the query still passes.
*/
if (ol_i_id[i] != 0 &&
ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
i_price[i] = SPI_getvalue(tuple, tupdesc, 1);
i_name[i] = SPI_getvalue(tuple, tupdesc, 2);
i_data[i] = SPI_getvalue(tuple, tupdesc, 3);
#ifdef DEBUG
elog(NOTICE, "i_price[%d] = %s", i, i_price[i]);
elog(NOTICE, "i_name[%d] = %s", i, i_name[i]);
elog(NOTICE, "i_data[%d] = %s", i, i_data[i]);
#endif /* DEBUG */
} else {
/* Item doesn't exist, rollback transaction. */
PG_RETURN_INT32(2);
}
ol_amount[i] = atof(i_price[i]) * (float) ol_quantity[i];
sprintf(query, NEW_ORDER_8, s_dist[d_id - 1], ol_i_id[i], w_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
s_quantity[i] = SPI_getvalue(tuple, tupdesc, 1);
my_s_dist[i] = SPI_getvalue(tuple, tupdesc, 2);
s_data[i] = SPI_getvalue(tuple, tupdesc, 3);
#ifdef DEBUG
elog(NOTICE, "s_quantity[%d] = %s", i, s_quantity[i]);
elog(NOTICE, "my_s_dist[%d] = %s", i, my_s_dist[i]);
elog(NOTICE, "s_data[%d] = %s", i, s_data[i]);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(16);
}
order_amount += ol_amount[i];
if (atoi(s_quantity[i]) > ol_quantity[i] + 10) {
sprintf(query, NEW_ORDER_9, ol_quantity[i],
ol_i_id[i], w_id);
} else {
sprintf(query, NEW_ORDER_9, ol_quantity[i] + 91,
ol_i_id[i], w_id);
}
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(17);
}
sprintf(query, NEW_ORDER_10, d_next_o_id, d_id, w_id, i + 1,
ol_i_id[i], ol_supply_w_id[i], ol_quantity[i],
ol_amount[i], my_s_dist[i]);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_INSERT) {
PG_RETURN_INT32(18);
}
}
SPI_finish();
PG_RETURN_INT32(0);
}
Datum order_status(PG_FUNCTION_ARGS)
{
/* Input variables. */
int32 c_id = PG_GETARG_INT32(0);
int32 c_w_id = PG_GETARG_INT32(1);
int32 c_d_id = PG_GETARG_INT32(2);
text *c_last = PG_GETARG_TEXT_P(3);
TupleDesc tupdesc;
SPITupleTable *tuptable;
HeapTuple tuple;
int ret;
int count;
int j;
char query[512];
char *tmp_c_id;
int my_c_id = 0;
char *c_first = NULL;
char *c_middle = NULL;
char *my_c_last = NULL;
char *c_balance = NULL;
char *o_id = NULL;
char *o_carrier_id = NULL;
char *o_entry_d = NULL;
char *o_ol_cnt = NULL;
char *ol_i_id[15];
char *ol_supply_w_id[15];
char *ol_quantity[15];
char *ol_amount[15];
char *ol_delivery_d[15];
SPI_connect();
if (c_id == 0) {
sprintf(query, ORDER_STATUS_1, c_w_id, c_d_id,
DatumGetCString(DirectFunctionCall1(textout,
PointerGetDatum(c_last))));
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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);
#ifdef DEBUG
elog(NOTICE, "c_id = %s, %d total, selected %d",
tmp_c_id, count, count / 2);
#endif /* DEBUG */
my_c_id = atoi(tmp_c_id);
} else {
PG_RETURN_INT32(-1);
}
} else {
my_c_id = c_id;
}
sprintf(query, ORDER_STATUS_2, c_w_id, c_d_id, my_c_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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_balance = SPI_getvalue(tuple, tupdesc, 4);
#ifdef DEBUG
elog(NOTICE, "c_first = %s", c_first);
elog(NOTICE, "c_middle = %s", c_middle);
elog(NOTICE, "c_last = %s", my_c_last);
elog(NOTICE, "c_balance = %s", c_balance);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(-1);
}
/* Maybe this should be a join with the previous query. */
sprintf(query, ORDER_STATUS_3, c_w_id, c_d_id, my_c_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
tuple = tuptable->vals[0];
o_id = SPI_getvalue(tuple, tupdesc, 1);
o_carrier_id = SPI_getvalue(tuple, tupdesc, 2);
o_entry_d = SPI_getvalue(tuple, tupdesc, 3);
o_ol_cnt = SPI_getvalue(tuple, tupdesc, 4);
#ifdef DEBUG
elog(NOTICE, "o_id = %s", o_id);
elog(NOTICE, "o_carrier_id = %s", o_carrier_id);
elog(NOTICE, "o_entry_d = %s", o_entry_d);
elog(NOTICE, "o_ol_cnt = %s", o_ol_cnt);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(-1);
}
sprintf(query, ORDER_STATUS_4, c_w_id, c_d_id, o_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
count = SPI_processed;
#ifdef DEBUG
elog(NOTICE, "## ol_i_id ol_supply_w_id ol_quantity ol_amount ol_delivery_d");
elog(NOTICE, "-- ------- -------------- ----------- --------- -------------");
#endif /* DEBUG */
if (ret == SPI_OK_SELECT && SPI_processed > 0) {
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
for (j = 0; j < count; j++) {
tuple = tuptable->vals[j];
ol_i_id[j] = SPI_getvalue(tuple, tupdesc, 1);
ol_supply_w_id[j] = SPI_getvalue(tuple, tupdesc, 2);
ol_quantity[j] = SPI_getvalue(tuple, tupdesc, 3);
ol_amount[j] = SPI_getvalue(tuple, tupdesc, 4);
ol_delivery_d[j] = SPI_getvalue(tuple, tupdesc, 5);
#ifdef DEBUG
elog(NOTICE, "%2d %7s %14s %11s %9.2f %13s",
j + 1, ol_i_id[j], ol_supply_w_id[j],
ol_quantity[j], atof(ol_amount[j]),
ol_delivery_d[j]);
#endif /* DEBUG */
}
} else {
PG_RETURN_INT32(-1);
}
SPI_finish();
PG_RETURN_INT32(1);
}
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 query[4096];
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;
char my_w_name[20];
char my_d_name[20];
#ifdef DEBUG
elog(NOTICE, "w_id = %d", w_id);
elog(NOTICE, "d_id = %d", d_id);
elog(NOTICE, "c_id = %d", c_id);
elog(NOTICE, "c_w_id = %d", c_w_id);
elog(NOTICE, "c_d_id = %d", c_d_id);
elog(NOTICE, "c_last = %s",
DatumGetCString(DirectFunctionCall1(textout,
PointerGetDatum(c_last))));
elog(NOTICE, "h_amount = %f", h_amount);
#endif /* DEBUG */
SPI_connect();
sprintf(query, PAYMENT_1, w_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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);
#ifdef DEBUG
elog(NOTICE, "w_name = %s", w_name);
elog(NOTICE, "w_street_1 = %s", w_street_1);
elog(NOTICE, "w_street_2 = %s", w_street_2);
elog(NOTICE, "w_city = %s", w_city);
elog(NOTICE, "w_state = %s", w_state);
elog(NOTICE, "w_zip = %s", w_zip);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(-1);
}
sprintf(query, PAYMENT_2, h_amount, w_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(-1);
}
sprintf(query, PAYMENT_3, d_id, w_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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);
#ifdef DEBUG
elog(NOTICE, "d_name = %s", d_name);
elog(NOTICE, "d_street_1 = %s", d_street_1);
elog(NOTICE, "d_street_2 = %s", d_street_2);
elog(NOTICE, "d_city = %s", d_city);
elog(NOTICE, "d_state = %s", d_state);
elog(NOTICE, "d_zip = %s", d_zip);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(-1);
}
sprintf(query, PAYMENT_4, h_amount, d_id, w_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(-1);
}
if (c_id == 0) {
sprintf(query, PAYMENT_5, w_id, d_id,
DatumGetCString(DirectFunctionCall1(textout,
PointerGetDatum(c_last))));
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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);
#ifdef DEBUG
elog(NOTICE, "c_id = %s, %d total, selected %d",
tmp_c_id, count, count / 2);
#endif /* DEBUG */
my_c_id = atoi(tmp_c_id);
} else {
PG_RETURN_INT32(-1);
}
} else {
my_c_id = c_id;
}
sprintf(query, PAYMENT_6, c_w_id, c_d_id, my_c_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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);
#ifdef DEBUG
elog(NOTICE, "c_first = %s", c_first);
elog(NOTICE, "c_middle = %s", c_middle);
elog(NOTICE, "c_last = %s", my_c_last);
elog(NOTICE, "c_street_1 = %s", c_street_1);
elog(NOTICE, "c_street_2 = %s", c_street_2);
elog(NOTICE, "c_city = %s", c_city);
elog(NOTICE, "c_state = %s", c_state);
elog(NOTICE, "c_zip = %s", c_zip);
elog(NOTICE, "c_phone = %s", c_phone);
elog(NOTICE, "c_since = %s", c_since);
elog(NOTICE, "c_credit = %s", c_credit);
elog(NOTICE, "c_credit_lim = %s", c_credit_lim);
elog(NOTICE, "c_discount = %s", c_discount);
elog(NOTICE, "c_balance = %s", c_balance);
elog(NOTICE, "c_data = %s", c_data);
elog(NOTICE, "c_ytd_payment = %s", c_ytd_payment);
#endif /* DEBUG */
} else {
PG_RETURN_INT32(-1);
}
/* It's either "BC" or "GC". */
if (c_credit[0] == 'G') {
sprintf(query, PAYMENT_7_GC, h_amount, my_c_id, c_w_id, c_d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
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);
/* Copy and escape all at once! */
escape_str(c_data, my_c_data);
sprintf(query, PAYMENT_7_BC, h_amount, my_c_data, my_c_id,
c_w_id, c_d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_UPDATE) {
PG_RETURN_INT32(-1);
}
}
/* Escape special characters. */
escape_str(w_name, my_w_name);
escape_str(d_name, my_d_name);
sprintf(query, PAYMENT_8, my_c_id, c_d_id, c_w_id, d_id, w_id,
h_amount, my_w_name, my_d_name);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 0);
if (ret != SPI_OK_INSERT) {
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 query[256];
char *buf;
SPI_connect();
sprintf(query, STOCK_LEVEL_1, w_id, d_id);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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);
#ifdef DEBUG
elog(NOTICE, "d_next_o_id = %s", buf);
#endif /* DEBUG */
d_next_o_id = atoi(buf);
} else {
PG_RETURN_INT32(-1);
}
sprintf(query, STOCK_LEVEL_2, w_id, d_id, threshold, d_next_o_id - 20,
d_next_o_id - 1);
#ifdef DEBUG
elog(NOTICE, "%s", query);
#endif /* DEBUG */
ret = SPI_exec(query, 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);
#ifdef DEBUG
elog(NOTICE, "low_stock = %s", buf);
#endif /* DEBUG */
low_stock = atoi(buf);
} else {
PG_RETURN_INT32(-1);
}
SPI_finish();
PG_RETURN_INT32(low_stock);
}
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: libpq ERROR: overflow on numeric ABS(value) ...
@ 2003-08-07 22:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: markw@osdl.org
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2003-08-07 22:14 UTC (permalink / raw)
To: markw@osdl.org; +Cc: pgsql-interfaces; osdldbt-general@lists.sourceforge.net
markw@osdl.org writes:
>>> DECLARE mycursor CURSOR FOR SELECT new_order(...)
>>> FETCH ALL IN mycursor
>>> ERROR: overflow on numeric ABS(value) >= 10^4 for field with precision 4 scale 0
> Does it make sense that is throws the error on the FETCH instead of the
> previous statement?
Sure: the cursor won't actually be executed until you fetch from it.
Since there's no NUMERIC datatypes in the cursor operation, it's a
dead certainty that the cursor itself is not where the error is being
thrown. I see that your function issues a bunch of inserts and updates
internally; presumably the problem is that one of those is trying to
store bogus data into a table. I'd suggest turning on that DEBUG code
you've sprinkled in there and looking to see which statement gets the
error ...
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: libpq ERROR: overflow on numeric ABS(value) ...
@ 2003-08-07 22:23 markw@osdl.org
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: markw@osdl.org @ 2003-08-07 22:23 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-interfaces; osdldbt-general@lists.sourceforge.net
On 7 Aug, Tom Lane wrote:
> markw@osdl.org writes:
>>>> DECLARE mycursor CURSOR FOR SELECT new_order(...)
>>>> FETCH ALL IN mycursor
>>>> ERROR: overflow on numeric ABS(value) >= 10^4 for field with precision 4 scale 0
>
>> Does it make sense that is throws the error on the FETCH instead of the
>> previous statement?
>
> Sure: the cursor won't actually be executed until you fetch from it.
Oh right, I should have remembered that...
Thanks,
Mark
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: libpq ERROR: overflow on numeric ABS(value) ...
@ 2003-08-07 23:53 markw@osdl.org
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: markw@osdl.org @ 2003-08-07 23:53 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-interfaces; osdldbt-general@lists.sourceforge.net
On 7 Aug, Tom Lane wrote:
> markw@osdl.org writes:
>>>> DECLARE mycursor CURSOR FOR SELECT new_order(...)
>>>> FETCH ALL IN mycursor
>>>> ERROR: overflow on numeric ABS(value) >= 10^4 for field with precision 4 scale 0
Ok, found the offending statement:
UPDATE stock
SET s_quantity = s_quantity - 4
WHERE s_i_id = 28472
AND s_w_id = 2
s_quantity is defined as NUMERIC(4). So does this means s_quantity must
be a positive number? Should I use a different data type?
Thanks,
Mark
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: libpq ERROR: overflow on numeric ABS(value) ...
@ 2003-08-08 00:02 markw@osdl.org
parent: markw@osdl.org
0 siblings, 0 replies; 7+ messages in thread
From: markw@osdl.org @ 2003-08-08 00:02 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-interfaces; osdldbt-general@lists.sourceforge.net
On 7 Aug, To: tgl@sss.pgh.pa.us wrote:
> On 7 Aug, Tom Lane wrote:
>> markw@osdl.org writes:
>>>>> DECLARE mycursor CURSOR FOR SELECT new_order(...)
>>>>> FETCH ALL IN mycursor
>>>>> ERROR: overflow on numeric ABS(value) >= 10^4 for field with precision 4 scale 0
>
> Ok, found the offending statement:
> UPDATE stock
> SET s_quantity = s_quantity - 4
> WHERE s_i_id = 28472
> AND s_w_id = 2
>
> s_quantity is defined as NUMERIC(4). So does this means s_quantity must
> be a positive number? Should I use a different data type?
Oh wait, I got it figured out. I'm getting smaller than -9999... :(
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-08-08 00:02 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-07 21:25 libpq ERROR: overflow on numeric ABS(value) ... markw@osdl.org
2003-08-07 21:49 ` Re: libpq ERROR: overflow on numeric ABS(value) ... Tom Lane <tgl@sss.pgh.pa.us>
2003-08-07 21:56 ` Re: libpq ERROR: overflow on numeric ABS(value) ... markw@osdl.org
2003-08-07 22:14 ` Re: libpq ERROR: overflow on numeric ABS(value) ... Tom Lane <tgl@sss.pgh.pa.us>
2003-08-07 22:23 ` Re: libpq ERROR: overflow on numeric ABS(value) ... markw@osdl.org
2003-08-07 23:53 ` Re: libpq ERROR: overflow on numeric ABS(value) ... markw@osdl.org
2003-08-08 00:02 ` Re: libpq ERROR: overflow on numeric ABS(value) ... markw@osdl.org
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox