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
|
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 $$;
|
