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
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
|
--
-- Tests for PL/pgSQL's behavior with domain types
--
CREATE DOMAIN booltrue AS bool CHECK (VALUE IS TRUE OR VALUE IS NULL);
CREATE FUNCTION test_argresult_booltrue(x booltrue, y bool) RETURNS booltrue AS $$
begin
return y;
end
$$ LANGUAGE plpgsql;
SELECT * FROM test_argresult_booltrue(true, true);
SELECT * FROM test_argresult_booltrue(false, true);
SELECT * FROM test_argresult_booltrue(true, false);
CREATE FUNCTION test_assign_booltrue(x bool, y bool) RETURNS booltrue AS $$
declare v booltrue := x;
begin
v := y;
return v;
end
$$ LANGUAGE plpgsql;
SELECT * FROM test_assign_booltrue(true, true);
SELECT * FROM test_assign_booltrue(false, true);
SELECT * FROM test_assign_booltrue(true, false);
CREATE DOMAIN uint2 AS int2 CHECK (VALUE >= 0);
CREATE FUNCTION test_argresult_uint2(x uint2, y int) RETURNS uint2 AS $$
begin
return y;
end
$$ LANGUAGE plpgsql;
SELECT * FROM test_argresult_uint2(100::uint2, 50);
SELECT * FROM test_argresult_uint2(100::uint2, -50);
SELECT * FROM test_argresult_uint2(null, 1);
CREATE FUNCTION test_assign_uint2(x int, y int) RETURNS uint2 AS $$
declare v uint2 := x;
begin
v := y;
return v;
end
$$ LANGUAGE plpgsql;
SELECT * FROM test_assign_uint2(100, 50);
SELECT * FROM test_assign_uint2(100, -50);
SELECT * FROM test_assign_uint2(-100, 50);
SELECT * FROM test_assign_uint2(null, 1);
CREATE DOMAIN nnint AS int NOT NULL;
CREATE FUNCTION test_argresult_nnint(x nnint, y int) RETURNS nnint AS $$
begin
return y;
end
$$ LANGUAGE plpgsql;
SELECT * FROM test_argresult_nnint(10, 20);
SELECT * FROM test_argresult_nnint(null, 20);
SELECT * FROM test_argresult_nnint(10, null);
CREATE FUNCTION test_assign_nnint(x int, y int) RETURNS nnint AS $$
declare v nnint := x;
begin
v := y;
return v;
end
$$ LANGUAGE plpgsql;
SELECT * FROM test_assign_nnint(10, 20);
SELECT * FROM test_assign_nnint(null, 20);
SELECT * FROM test_assign_nnint(10, null);
--
-- Domains over arrays
--
CREATE DOMAIN ordered_pair_domain AS integer[] CHECK (array_length(VALUE,1)=2 AND VALUE[1] < VALUE[2]);
CREATE FUNCTION test_argresult_array_domain(x ordered_pair_domain)
RETURNS ordered_pair_domain AS $$
begin
return x;
end
$$ LANGUAGE plpgsql;
SELECT * FROM test_argresult_array_domain(ARRAY[0, 100]::ordered_pair_domain);
SELECT * FROM test_argresult_array_domain(NULL::ordered_pair_domain);
CREATE FUNCTION test_argresult_array_domain_check_violation()
RETURNS ordered_pair_domain AS $$
begin
return array[2,1];
end
$$ LANGUAGE plpgsql;
SELECT * FROM test_argresult_array_domain_check_violation();
CREATE FUNCTION test_assign_ordered_pair_domain(x int, y int, z int) RETURNS ordered_pair_domain AS $$
declare v ordered_pair_domain := array[x, y];
begin
v[2] := z;
return v;
end
$$ LANGUAGE plpgsql;
SELECT * FROM test_assign_ordered_pair_domain(1,2,3);
SELECT * FROM test_assign_ordered_pair_domain(1,2,0);
SELECT * FROM test_assign_ordered_pair_domain(2,1,3);
--
-- Arrays of domains
--
CREATE FUNCTION test_read_uint2_array(x uint2[]) RETURNS uint2 AS $$
begin
return x[1];
end
$$ LANGUAGE plpgsql;
select test_read_uint2_array(array[1::uint2]);
CREATE FUNCTION test_build_uint2_array(x int2) RETURNS uint2[] AS $$
begin
return array[x, x];
end
$$ LANGUAGE plpgsql;
select test_build_uint2_array(1::int2);
select test_build_uint2_array(-1::int2); -- fail
CREATE FUNCTION test_argresult_domain_array(x integer[])
RETURNS ordered_pair_domain[] AS $$
begin
return array[x::ordered_pair_domain, x::ordered_pair_domain];
end
$$ LANGUAGE plpgsql;
select test_argresult_domain_array(array[2,4]);
select test_argresult_domain_array(array[4,2]); -- fail
CREATE FUNCTION test_argresult_domain_array2(x ordered_pair_domain)
RETURNS integer AS $$
begin
return x[1];
end
$$ LANGUAGE plpgsql;
select test_argresult_domain_array2(array[2,4]);
select test_argresult_domain_array2(array[4,2]); -- fail
CREATE FUNCTION test_argresult_array_domain_array(x ordered_pair_domain[])
RETURNS ordered_pair_domain AS $$
begin
return x[1];
end
$$ LANGUAGE plpgsql;
select test_argresult_array_domain_array(array[array[2,4]::ordered_pair_domain]);
--
-- Domains within composite
--
CREATE TYPE nnint_container AS (f1 int, f2 nnint);
CREATE FUNCTION test_result_nnint_container(x int, y int)
RETURNS nnint_container AS $$
begin
return row(x, y)::nnint_container;
end
$$ LANGUAGE plpgsql;
SELECT test_result_nnint_container(null, 3);
SELECT test_result_nnint_container(3, null); -- fail
CREATE FUNCTION test_assign_nnint_container(x int, y int, z int)
RETURNS nnint_container AS $$
declare v nnint_container := row(x, y);
begin
v.f2 := z;
return v;
end
$$ LANGUAGE plpgsql;
SELECT * FROM test_assign_nnint_container(1,2,3);
SELECT * FROM test_assign_nnint_container(1,2,null);
SELECT * FROM test_assign_nnint_container(1,null,3);
-- Since core system allows this:
SELECT null::nnint_container;
-- so should PL/PgSQL
CREATE FUNCTION test_assign_nnint_container2(x int, y int, z int)
RETURNS nnint_container AS $$
declare v nnint_container;
begin
v.f2 := z;
return v;
end
$$ LANGUAGE plpgsql;
SELECT * FROM test_assign_nnint_container2(1,2,3);
SELECT * FROM test_assign_nnint_container2(1,2,null);
--
-- Domains of composite
--
CREATE TYPE named_pair AS (
i integer,
j integer
);
CREATE DOMAIN ordered_named_pair AS named_pair CHECK((VALUE).i <= (VALUE).j);
CREATE FUNCTION read_ordered_named_pair(p ordered_named_pair) RETURNS integer AS $$
begin
return p.i + p.j;
end
$$ LANGUAGE plpgsql;
SELECT read_ordered_named_pair(row(1, 2));
SELECT read_ordered_named_pair(row(2, 1)); -- fail
CREATE FUNCTION build_ordered_named_pair(i int, j int) RETURNS ordered_named_pair AS $$
begin
return row(i, j);
end
$$ LANGUAGE plpgsql;
SELECT build_ordered_named_pair(1,2);
SELECT build_ordered_named_pair(2,1); -- fail
CREATE FUNCTION test_assign_ordered_named_pair(x int, y int, z int)
RETURNS ordered_named_pair AS $$
declare v ordered_named_pair := row(x, y);
begin
v.j := z;
return v;
end
$$ LANGUAGE plpgsql;
SELECT * FROM test_assign_ordered_named_pair(1,2,3);
SELECT * FROM test_assign_ordered_named_pair(1,2,0);
SELECT * FROM test_assign_ordered_named_pair(2,1,3);
CREATE FUNCTION build_ordered_named_pairs(i int, j int) RETURNS ordered_named_pair[] AS $$
begin
return array[row(i, j), row(i, j+1)];
end
$$ LANGUAGE plpgsql;
SELECT build_ordered_named_pairs(1,2);
SELECT build_ordered_named_pairs(2,1); -- fail
CREATE FUNCTION test_assign_ordered_named_pairs(x int, y int, z int)
RETURNS ordered_named_pair[] AS $$
declare v ordered_named_pair[] := array[row(x, y)];
begin
-- ideally this would work, but it doesn't yet:
-- v[1].j := z;
return v;
end
$$ LANGUAGE plpgsql;
SELECT * FROM test_assign_ordered_named_pairs(1,2,3);
SELECT * FROM test_assign_ordered_named_pairs(2,1,3);
SELECT * FROM test_assign_ordered_named_pairs(1,2,0); -- should fail someday
|
