1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
|
DROP TABLE joseph_test.X;
CREATE TABLE joseph_test.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 ();
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);
CREATE OR REPLACE FUNCTION pg_spi_insert_int()
RETURNS VOID
LANGUAGE c 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()
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()
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()
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()
RETURNS VOID
LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_update_y';
DROP FUNCTION pg_spi_prepare_example();
CREATE OR REPLACE 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()
RETURNS INT8
LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_prepare_example_without_saveplan';
CREATE OR REPLACE FUNCTION pg_spi_prepare_insert()
RETURNS VOID
LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_prepare_insert';
CREATE OR REPLACE FUNCTION pg_spi_prepare_insert_without_saveplan()
RETURNS VOID
LANGUAGE c 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()
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()
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()
RETURNS VOID
LANGUAGE c 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(
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';
CREATE OR REPLACE 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 $$;
CREATE OR REPLACE FUNCTION stored_procedure_update(
IN in_number INT8
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE joseph_test.Y
SET col1 = 4
WHERE col2 = in_number;
END $$;
CREATE OR REPLACE FUNCTION stored_procedure_select(OUT out_value INT8)
RETURNS INT8
LANGUAGE plpgsql
AS $$
BEGIN
SELECT 1
INTO out_value
FROM joseph_test.X;
RETURN;
END $$;
CREATE OR REPLACE FUNCTION stored_procedure_insert(
IN in_number INT8,
OUT out_number INT8)
RETURNS INT8
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO joseph_test.X (a)
VALUES (in_number)
RETURNING a INTO out_number;
END $$;
CREATE OR REPLACE 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 joseph_test.Y
WHERE col2 = in_number;
RETURN;
END $$;
|
