own_test.sql (5264B)
1 DROP TABLE IF EXISTS X; 2 CREATE TABLE X ( 3 a integer 4 ); 5 6 INSERT INTO X (a) 7 VALUES (1), (2), (3), (4), (5), (6), (7); 8 9 DROP TABLE IF EXISTS Y; 10 CREATE TABLE Y (col1 INT, col2 INT); 11 INSERT INTO Y (col1,col2) 12 VALUES (1,2), (2,0), (0,4), (4,0), (0,6), (6,7), (7,8); 13 14 DROP TABLE IF EXISTS Z; 15 CREATE TABLE Z (col1 BYTEA); 16 17 DROP TABLE IF EXISTS deposits; 18 CREATE TABLE deposits( 19 deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY 20 ,shard INT8 NOT NULL 21 ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) 22 ,known_coin_id INT8 NOT NULL 23 ,amount_with_fee_val INT8 NOT NULL 24 ,amount_with_fee_frac INT4 NOT NULL 25 ,wallet_timestamp INT8 NOT NULL 26 ,exchange_timestamp INT8 NOT NULL 27 ,refund_deadline INT8 NOT NULL 28 ,wire_deadline INT8 NOT NULL 29 ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32) 30 ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) 31 ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64) 32 ,wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16) 33 ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32) 34 ,done BOOLEAN NOT NULL DEFAULT FALSE 35 ,policy_blocked BOOLEAN NOT NULL DEFAULT FALSE 36 ,policy_details_serial_id INT8); 37 38 39 DROP FUNCTION IF EXISTS pg_spi_insert_int; 40 CREATE FUNCTION pg_spi_insert_int() 41 RETURNS VOID 42 LANGUAGE c VOLATILE COST 100 43 AS '$libdir/own_test', 'pg_spi_insert_int'; 44 45 DROP FUNCTION IF EXISTS pg_spi_select_from_x; 46 CREATE FUNCTION pg_spi_select_from_x() 47 RETURNS INT8 48 LANGUAGE c COST 100 49 AS '$libdir/own_test', 'pg_spi_select_from_x'; 50 51 /* 52 CREATE FUNCTION pg_spi_select_pair_from_y() 53 RETURNS valuest 54 LANGUAGE c COST 100 55 AS '$libdir/own_test', 'pg_spi_select_pair_from_y'; 56 */ 57 /*CREATE FUNCTION pg_spi_select_with_cond() 58 RETURNS INT8 59 LANGUAGE c COST 100 60 AS '$libdir/own_test', 'pg_spi_select_with_cond'; 61 */ 62 63 DROP FUNCTION IF EXISTS pg_spi_update_y; 64 CREATE FUNCTION pg_spi_update_y() 65 RETURNS VOID 66 LANGUAGE c VOLATILE COST 100 67 AS '$libdir/own_test', 'pg_spi_update_y'; 68 69 DROP FUNCTION IF EXISTS pg_spi_prepare_example; 70 CREATE FUNCTION pg_spi_prepare_example() 71 RETURNS INT8 72 LANGUAGE c COST 100 73 AS '$libdir/own_test', 'pg_spi_prepare_example'; 74 75 DROP FUNCTION IF EXISTS pg_spi_prepare_example_without_saveplan; 76 CREATE FUNCTION pg_spi_prepare_example_without_saveplan() 77 RETURNS INT8 78 LANGUAGE c COST 100 79 AS '$libdir/own_test', 'pg_spi_prepare_example_without_saveplan'; 80 81 DROP FUNCTION IF EXISTS pg_spi_prepare_insert; 82 CREATE FUNCTION pg_spi_prepare_insert() 83 RETURNS VOID 84 LANGUAGE c VOLATILE COST 100 85 AS '$libdir/own_test', 'pg_spi_prepare_insert'; 86 87 DROP FUNCTION IF EXISTS pg_spi_prepare_insert_without_saveplan; 88 CREATE FUNCTION pg_spi_prepare_insert_without_saveplan() 89 RETURNS VOID 90 LANGUAGE c VOLATILE COST 100 91 AS '$libdir/own_test', 'pg_spi_prepare_insert_without_saveplan'; 92 93 /* 94 CREATE FUNCTION pg_spi_prepare_select_with_cond() 95 RETURNS INT8 96 LANGUAGE c COST 100 97 AS '$libdir/own_test', 'pg_spi_prepare_select_with_cond'; 98 */ 99 100 DROP FUNCTION IF EXISTS pg_spi_prepare_select_with_cond_without_saveplan; 101 CREATE FUNCTION pg_spi_prepare_select_with_cond_without_saveplan() 102 RETURNS INT8 103 LANGUAGE c COST 100 104 AS '$libdir/own_test', 'pg_spi_prepare_select_with_cond_without_saveplan'; 105 106 DROP FUNCTION IF EXISTS pg_spi_prepare_update; 107 CREATE FUNCTION pg_spi_prepare_update() 108 RETURNS VOID 109 LANGUAGE c VOLATILE COST 100 110 AS '$libdir/own_test', 'pg_spi_prepare_update'; 111 112 DROP FUNCTION IF EXISTS pg_spi_get_dep_ref_fees; 113 CREATE FUNCTION pg_spi_get_dep_ref_fees( 114 IN in_timestamp INT8 115 ,IN merchant_pub BYTEA 116 ,IN wire_target_h_payto BYTEA 117 ,IN wtid BYTEA 118 ) 119 RETURNS VOID 120 LANGUAGE c VOLATILE COST 100 121 AS '$libdir/own_test', 'pg_spi_get_dep_ref_fees'; 122 123 DROP FUNCTION IF EXISTS update_pg_spi_get_dep_ref_fees; 124 CREATE FUNCTION update_pg_spi_get_dep_ref_fees( 125 IN in_refund_deadline INT8, 126 IN in_merchant_pub BYTEA, 127 IN in_wire_target_h_payto BYTEA 128 ) 129 RETURNS SETOF record 130 LANGUAGE plpgsql VOLATILE 131 AS $$ 132 DECLARE 133 134 BEGIN 135 RETURN QUERY 136 UPDATE deposits 137 SET done = TRUE 138 WHERE NOT (done OR policy_blocked) 139 AND refund_deadline < in_refund_deadline 140 AND merchant_pub = in_merchant_pub 141 AND wire_target_h_payto = in_wire_target_h_payto 142 RETURNING 143 deposit_serial_id, 144 coin_pub, 145 amount_with_fee_val, 146 amount_with_fee_frac; 147 END $$; 148 149 DROP FUNCTION IF EXISTS stored_procedure_update; 150 CREATE FUNCTION stored_procedure_update( 151 IN in_number INT8 152 ) 153 RETURNS VOID 154 LANGUAGE plpgsql 155 AS $$ 156 BEGIN 157 UPDATE Y 158 SET col1 = 4 159 WHERE col2 = in_number; 160 END $$; 161 162 DROP FUNCTION IF EXISTS stored_procedure_select; 163 CREATE FUNCTION stored_procedure_select(OUT out_value INT8) 164 RETURNS INT8 165 LANGUAGE plpgsql 166 AS $$ 167 BEGIN 168 SELECT 1 169 INTO out_value 170 FROM X; 171 RETURN; 172 END $$; 173 174 175 DROP FUNCTION IF EXISTS stored_procedure_insert; 176 CREATE FUNCTION stored_procedure_insert( 177 IN in_number INT8, 178 OUT out_number INT8) 179 RETURNS INT8 180 LANGUAGE plpgsql 181 AS $$ 182 BEGIN 183 INSERT INTO X (a) 184 VALUES (in_number) 185 RETURNING a INTO out_number; 186 END $$; 187 188 DROP FUNCTION IF EXISTS stored_procedure_select_with_cond; 189 CREATE FUNCTION stored_procedure_select_with_cond( 190 IN in_number INT8, 191 OUT out_number INT8 192 ) 193 RETURNS INT8 194 LANGUAGE plpgsql 195 AS $$ 196 BEGIN 197 SELECT col1 INTO out_number 198 FROM Y 199 WHERE col2 = in_number; 200 RETURN; 201 END $$;