diff options
Diffstat (limited to 'src/exchangedb/spi/own_test.sql')
-rw-r--r-- | src/exchangedb/spi/own_test.sql | 201 |
1 files changed, 201 insertions, 0 deletions
diff --git a/src/exchangedb/spi/own_test.sql b/src/exchangedb/spi/own_test.sql new file mode 100644 index 000000000..12729d068 --- /dev/null +++ b/src/exchangedb/spi/own_test.sql @@ -0,0 +1,201 @@ +DROP TABLE IF EXISTS X; +CREATE TABLE X ( + a integer +); + +INSERT INTO X (a) + VALUES (1), (2), (3), (4), (5), (6), (7); + +DROP TABLE IF EXISTS Y; +CREATE TABLE Y (col1 INT, col2 INT); +INSERT INTO Y (col1,col2) + VALUES (1,2), (2,0), (0,4), (4,0), (0,6), (6,7), (7,8); + +DROP TABLE IF EXISTS Z; +CREATE TABLE Z (col1 BYTEA); + +DROP TABLE IF EXISTS deposits; +CREATE TABLE deposits( + deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY + ,shard INT8 NOT NULL + ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) + ,known_coin_id INT8 NOT NULL + ,amount_with_fee_val INT8 NOT NULL + ,amount_with_fee_frac INT4 NOT NULL + ,wallet_timestamp INT8 NOT NULL + ,exchange_timestamp INT8 NOT NULL + ,refund_deadline INT8 NOT NULL + ,wire_deadline INT8 NOT NULL + ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32) + ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) + ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64) + ,wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16) + ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32) + ,done BOOLEAN NOT NULL DEFAULT FALSE + ,policy_blocked BOOLEAN NOT NULL DEFAULT FALSE + ,policy_details_serial_id INT8); + + +DROP FUNCTION IF EXISTS pg_spi_insert_int; +CREATE FUNCTION pg_spi_insert_int() + RETURNS VOID + LANGUAGE c VOLATILE COST 100 +AS '$libdir/own_test', 'pg_spi_insert_int'; + +DROP FUNCTION IF EXISTS pg_spi_select_from_x; +CREATE FUNCTION pg_spi_select_from_x() + RETURNS INT8 + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_select_from_x'; + +/* +CREATE FUNCTION pg_spi_select_pair_from_y() + RETURNS valuest + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_select_pair_from_y'; +*/ +/*CREATE FUNCTION pg_spi_select_with_cond() + RETURNS INT8 + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_select_with_cond'; +*/ + +DROP FUNCTION IF EXISTS pg_spi_update_y; +CREATE FUNCTION pg_spi_update_y() + RETURNS VOID + LANGUAGE c VOLATILE COST 100 +AS '$libdir/own_test', 'pg_spi_update_y'; + +DROP FUNCTION IF EXISTS pg_spi_prepare_example; +CREATE FUNCTION pg_spi_prepare_example() + RETURNS INT8 + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_prepare_example'; + +DROP FUNCTION IF EXISTS pg_spi_prepare_example_without_saveplan; +CREATE FUNCTION pg_spi_prepare_example_without_saveplan() + RETURNS INT8 + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_prepare_example_without_saveplan'; + +DROP FUNCTION IF EXISTS pg_spi_prepare_insert; +CREATE FUNCTION pg_spi_prepare_insert() + RETURNS VOID + LANGUAGE c VOLATILE COST 100 +AS '$libdir/own_test', 'pg_spi_prepare_insert'; + +DROP FUNCTION IF EXISTS pg_spi_prepare_insert_without_saveplan; +CREATE FUNCTION pg_spi_prepare_insert_without_saveplan() + RETURNS VOID + LANGUAGE c VOLATILE COST 100 +AS '$libdir/own_test', 'pg_spi_prepare_insert_without_saveplan'; + +/* +CREATE FUNCTION pg_spi_prepare_select_with_cond() + RETURNS INT8 + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_prepare_select_with_cond'; +*/ + +DROP FUNCTION IF EXISTS pg_spi_prepare_select_with_cond_without_saveplan; +CREATE FUNCTION pg_spi_prepare_select_with_cond_without_saveplan() + RETURNS INT8 + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_prepare_select_with_cond_without_saveplan'; + +DROP FUNCTION IF EXISTS pg_spi_prepare_update; +CREATE FUNCTION pg_spi_prepare_update() + RETURNS VOID + LANGUAGE c VOLATILE COST 100 +AS '$libdir/own_test', 'pg_spi_prepare_update'; + +DROP FUNCTION IF EXISTS pg_spi_get_dep_ref_fees; +CREATE FUNCTION pg_spi_get_dep_ref_fees( + IN in_timestamp INT8 + ,IN merchant_pub BYTEA + ,IN wire_target_h_payto BYTEA + ,IN wtid BYTEA +) + RETURNS VOID + LANGUAGE c VOLATILE COST 100 +AS '$libdir/own_test', 'pg_spi_get_dep_ref_fees'; + +DROP FUNCTION IF EXISTS update_pg_spi_get_dep_ref_fees; +CREATE FUNCTION update_pg_spi_get_dep_ref_fees( + IN in_refund_deadline INT8, + IN in_merchant_pub BYTEA, + IN in_wire_target_h_payto BYTEA +) +RETURNS SETOF record +LANGUAGE plpgsql VOLATILE +AS $$ +DECLARE + +BEGIN +RETURN QUERY + UPDATE deposits + SET done = TRUE + WHERE NOT (done OR policy_blocked) + AND refund_deadline < in_refund_deadline + AND merchant_pub = in_merchant_pub + AND wire_target_h_payto = in_wire_target_h_payto + RETURNING + deposit_serial_id, + coin_pub, + amount_with_fee_val, + amount_with_fee_frac; +END $$; + +DROP FUNCTION IF EXISTS stored_procedure_update; +CREATE FUNCTION stored_procedure_update( +IN in_number INT8 +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + UPDATE Y + SET col1 = 4 + WHERE col2 = in_number; +END $$; + +DROP FUNCTION IF EXISTS stored_procedure_select; +CREATE FUNCTION stored_procedure_select(OUT out_value INT8) +RETURNS INT8 +LANGUAGE plpgsql +AS $$ +BEGIN + SELECT 1 + INTO out_value + FROM X; + RETURN; +END $$; + + +DROP FUNCTION IF EXISTS stored_procedure_insert; +CREATE FUNCTION stored_procedure_insert( +IN in_number INT8, +OUT out_number INT8) +RETURNS INT8 +LANGUAGE plpgsql +AS $$ +BEGIN + INSERT INTO X (a) + VALUES (in_number) + RETURNING a INTO out_number; +END $$; + +DROP FUNCTION IF EXISTS stored_procedure_select_with_cond; +CREATE FUNCTION stored_procedure_select_with_cond( +IN in_number INT8, +OUT out_number INT8 +) +RETURNS INT8 +LANGUAGE plpgsql +AS $$ +BEGIN + SELECT col1 INTO out_number + FROM Y + WHERE col2 = in_number; + RETURN; +END $$; |