diff options
Diffstat (limited to 'src/exchangedb/spi/own_test.sql')
-rw-r--r-- | src/exchangedb/spi/own_test.sql | 131 |
1 files changed, 58 insertions, 73 deletions
diff --git a/src/exchangedb/spi/own_test.sql b/src/exchangedb/spi/own_test.sql index 9fe42c0a4..12729d068 100644 --- a/src/exchangedb/spi/own_test.sql +++ b/src/exchangedb/spi/own_test.sql @@ -1,42 +1,20 @@ - -DROP TABLE joseph_test.X; -CREATE TABLE joseph_test.X ( +DROP TABLE IF EXISTS X; +CREATE TABLE X ( a integer ); -INSERT INTO joseph_test.X (a) VALUES (1), (2), (3), (4), (5), (6), (7); - -DROP TABLE joseph_test.Y; -CREATE TABLE joseph_test.Y (col1 INT, col2 INT); -INSERT INTO joseph_test.Y (col1,col2) VALUES (1,2), (2,0), (0,4), (4,0), (0,6), (6,7), (7,8); - -DROP TABLE joseph_test.Z; -CREATE TABLE joseph_test.Z(col1 BYTEA); -DROP TABLE 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); -*/ ---INSERT INTO deposits VALUES (); +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 @@ -58,79 +36,81 @@ CREATE TABLE deposits( ,policy_details_serial_id INT8); -CREATE OR REPLACE FUNCTION pg_spi_insert_int() +DROP FUNCTION IF EXISTS pg_spi_insert_int; +CREATE FUNCTION pg_spi_insert_int() RETURNS VOID - LANGUAGE c COST 100 + LANGUAGE c VOLATILE COST 100 AS '$libdir/own_test', 'pg_spi_insert_int'; -DROP FUNCTION pg_spi_select_from_x(); -CREATE OR REPLACE FUNCTION pg_spi_select_from_x() + +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'; -/*DROP FUNCTION pg_spi_select_pair_from_y(); -CREATE OR REPLACE FUNCTION pg_spi_select_pair_from_y() +/* +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 OR REPLACE FUNCTION pg_spi_select_with_cond() +/*CREATE FUNCTION pg_spi_select_with_cond() RETURNS INT8 LANGUAGE c COST 100 AS '$libdir/own_test', 'pg_spi_select_with_cond'; */ -DROP FUNCTION pg_spi_update_y(); -CREATE OR REPLACE FUNCTION pg_spi_update_y() + +DROP FUNCTION IF EXISTS pg_spi_update_y; +CREATE FUNCTION pg_spi_update_y() RETURNS VOID - LANGUAGE c COST 100 + LANGUAGE c VOLATILE COST 100 AS '$libdir/own_test', 'pg_spi_update_y'; -DROP FUNCTION pg_spi_prepare_example(); -CREATE OR REPLACE FUNCTION pg_spi_prepare_example() +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 pg_spi_prepare_example_without_saveplan(); -CREATE OR REPLACE FUNCTION pg_spi_prepare_example_without_saveplan() +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'; -CREATE OR REPLACE FUNCTION pg_spi_prepare_insert() +DROP FUNCTION IF EXISTS pg_spi_prepare_insert; +CREATE FUNCTION pg_spi_prepare_insert() RETURNS VOID - LANGUAGE c COST 100 + LANGUAGE c VOLATILE COST 100 AS '$libdir/own_test', 'pg_spi_prepare_insert'; -CREATE OR REPLACE FUNCTION pg_spi_prepare_insert_without_saveplan() +DROP FUNCTION IF EXISTS pg_spi_prepare_insert_without_saveplan; +CREATE FUNCTION pg_spi_prepare_insert_without_saveplan() RETURNS VOID - LANGUAGE c COST 100 + LANGUAGE c VOLATILE COST 100 AS '$libdir/own_test', 'pg_spi_prepare_insert_without_saveplan'; -/*DROP FUNCTION pg_spi_prepare_select_with_cond(); -CREATE OR REPLACE FUNCTION pg_spi_prepare_select_with_cond() +/* +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 pg_spi_prepare_select_with_cond_without_saveplan(); -CREATE OR REPLACE FUNCTION pg_spi_prepare_select_with_cond_without_saveplan() + +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 pg_spi_prepare_update(); -CREATE OR REPLACE FUNCTION pg_spi_prepare_update() +DROP FUNCTION IF EXISTS pg_spi_prepare_update; +CREATE FUNCTION pg_spi_prepare_update() RETURNS VOID - LANGUAGE c COST 100 + LANGUAGE c VOLATILE COST 100 AS '$libdir/own_test', 'pg_spi_prepare_update'; -DROP FUNCTION pg_spi_get_dep_ref_fees( - IN in_timestamp INT8 - ,IN merchant_pub BYTEA - ,IN wire_target_h_payto BYTEA - ,IN wtid BYTEA); -CREATE OR REPLACE FUNCTION pg_spi_get_dep_ref_fees( +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 @@ -140,7 +120,8 @@ CREATE OR REPLACE FUNCTION pg_spi_get_dep_ref_fees( LANGUAGE c VOLATILE COST 100 AS '$libdir/own_test', 'pg_spi_get_dep_ref_fees'; -CREATE OR REPLACE FUNCTION update_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 @@ -165,43 +146,47 @@ RETURN QUERY amount_with_fee_frac; END $$; -CREATE OR REPLACE FUNCTION stored_procedure_update( +DROP FUNCTION IF EXISTS stored_procedure_update; +CREATE FUNCTION stored_procedure_update( IN in_number INT8 ) RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN - UPDATE joseph_test.Y + UPDATE Y SET col1 = 4 WHERE col2 = in_number; END $$; -CREATE OR REPLACE FUNCTION stored_procedure_select(OUT out_value INT8) +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 joseph_test.X; + FROM X; RETURN; END $$; -CREATE OR REPLACE FUNCTION stored_procedure_insert( +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 joseph_test.X (a) + INSERT INTO X (a) VALUES (in_number) RETURNING a INTO out_number; END $$; -CREATE OR REPLACE FUNCTION stored_procedure_select_with_cond( +DROP FUNCTION IF EXISTS stored_procedure_select_with_cond; +CREATE FUNCTION stored_procedure_select_with_cond( IN in_number INT8, OUT out_number INT8 ) @@ -210,7 +195,7 @@ LANGUAGE plpgsql AS $$ BEGIN SELECT col1 INTO out_number - FROM joseph_test.Y + FROM Y WHERE col2 = in_number; RETURN; END $$; |