1
-- Currently this tests polymorphic aggregates and indirectly does some
2
-- testing of polymorphic SQL functions. It ought to be extended.
6
-- P = type is polymorphic
7
-- N = type is non-polymorphic
8
-- B = aggregate base type
9
-- S = aggregate state type
10
-- R = aggregate return type
11
-- 1 = arg1 of a function
12
-- 2 = arg2 of a function
14
-- tf = trans (state) function
15
-- ff = final function
16
-- rt = return type of a function
26
-- when (B = A) -> (tf2 = NE)
30
-- tf2 = (NE || P || N)
32
-- create functions for use as tf and ff with the needed combinations of
33
-- argument polymorphism, but within the constraints of valid aggregate
34
-- functions, i.e. tf arg1 and tf return type must match
35
-- polymorphic single arg transfn
36
CREATE FUNCTION stfp(anyarray) returns anyarray as
37
'select $1' language 'sql';
38
-- non-polymorphic single arg transfn
39
CREATE FUNCTION stfnp(int[]) returns int[] as
40
'select $1' language 'sql';
41
-- dual polymorphic transfn
42
CREATE FUNCTION tfp(anyarray,anyelement) returns anyarray as
43
'select $1 || $2' language 'sql';
44
-- dual non-polymorphic transfn
45
CREATE FUNCTION tfnp(int[],int) returns int[] as
46
'select $1 || $2' language 'sql';
47
-- arg1 only polymorphic transfn
48
CREATE FUNCTION tf1p(anyarray,int) returns anyarray as
49
'select $1' language 'sql';
50
-- arg2 only polymorphic transfn
51
CREATE FUNCTION tf2p(int[],anyelement) returns int[] as
52
'select $1' language 'sql';
53
-- finalfn polymorphic
54
CREATE FUNCTION ffp(anyarray) returns anyarray as
55
'select $1' language 'sql';
56
-- finalfn non-polymorphic
57
CREATE FUNCTION ffnp(int[]) returns int[] as
58
'select $1' language 'sql';
59
-- Try to cover all the possible states:
61
-- Note: in Cases 1 & 2, we are trying to return P. Therefore, if the transfn
62
-- is stfnp, tfnp, or tf2p, we must use ffp as finalfn, because stfnp, tfnp,
63
-- and tf2p do not return P. Conversely, in Cases 3 & 4, we are trying to
64
-- return N. Therefore, if the transfn is stfp, tfp, or tf1p, we must use ffnp
65
-- as finalfn, because stfp, tfp, and tf1p do not return N.
67
-- Case1 (R = P) && (B = A)
68
-- ------------------------
73
CREATE AGGREGATE myaggp01a(BASETYPE = "ANY", SFUNC = stfnp, STYPE = int4[],
74
FINALFUNC = ffp, INITCOND = '{}');
76
-- should ERROR: stfnp(anyarray) not matched by stfnp(int[])
77
CREATE AGGREGATE myaggp02a(BASETYPE = "ANY", SFUNC = stfnp, STYPE = anyarray,
78
FINALFUNC = ffp, INITCOND = '{}');
79
ERROR: cannot determine transition data type
80
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
83
CREATE AGGREGATE myaggp03a(BASETYPE = "ANY", SFUNC = stfp, STYPE = int4[],
84
FINALFUNC = ffp, INITCOND = '{}');
85
CREATE AGGREGATE myaggp03b(BASETYPE = "ANY", SFUNC = stfp, STYPE = int4[],
88
-- should ERROR: we have no way to resolve S
89
CREATE AGGREGATE myaggp04a(BASETYPE = "ANY", SFUNC = stfp, STYPE = anyarray,
90
FINALFUNC = ffp, INITCOND = '{}');
91
ERROR: cannot determine transition data type
92
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
93
CREATE AGGREGATE myaggp04b(BASETYPE = "ANY", SFUNC = stfp, STYPE = anyarray,
95
ERROR: cannot determine transition data type
96
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
97
-- Case2 (R = P) && ((B = P) || (B = N))
98
-- -------------------------------------
100
-- -----------------------
103
CREATE AGGREGATE myaggp05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[],
104
FINALFUNC = ffp, INITCOND = '{}');
107
CREATE AGGREGATE myaggp06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[],
108
FINALFUNC = ffp, INITCOND = '{}');
110
-- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int)
111
CREATE AGGREGATE myaggp07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
112
FINALFUNC = ffp, INITCOND = '{}');
113
ERROR: function tfnp(integer[], anyelement) does not exist
116
CREATE AGGREGATE myaggp08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
117
FINALFUNC = ffp, INITCOND = '{}');
120
CREATE AGGREGATE myaggp09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[],
121
FINALFUNC = ffp, INITCOND = '{}');
122
CREATE AGGREGATE myaggp09b(BASETYPE = int, SFUNC = tf1p, STYPE = int[],
126
CREATE AGGREGATE myaggp10a(BASETYPE = int, SFUNC = tfp, STYPE = int[],
127
FINALFUNC = ffp, INITCOND = '{}');
128
CREATE AGGREGATE myaggp10b(BASETYPE = int, SFUNC = tfp, STYPE = int[],
131
-- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int)
132
CREATE AGGREGATE myaggp11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
133
FINALFUNC = ffp, INITCOND = '{}');
134
ERROR: function tf1p(integer[], anyelement) does not exist
135
CREATE AGGREGATE myaggp11b(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
137
ERROR: function tf1p(integer[], anyelement) does not exist
139
-- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement)
140
CREATE AGGREGATE myaggp12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
141
FINALFUNC = ffp, INITCOND = '{}');
142
ERROR: function tfp(integer[], anyelement) does not exist
143
CREATE AGGREGATE myaggp12b(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
145
ERROR: function tfp(integer[], anyelement) does not exist
147
-- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int)
148
CREATE AGGREGATE myaggp13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray,
149
FINALFUNC = ffp, INITCOND = '{}');
150
ERROR: cannot determine transition data type
151
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
153
-- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement)
154
CREATE AGGREGATE myaggp14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray,
155
FINALFUNC = ffp, INITCOND = '{}');
156
ERROR: cannot determine transition data type
157
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
159
-- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int)
160
CREATE AGGREGATE myaggp15a(BASETYPE = anyelement, SFUNC = tfnp,
161
STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
162
ERROR: function tfnp(anyarray, anyelement) does not exist
164
-- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement)
165
CREATE AGGREGATE myaggp16a(BASETYPE = anyelement, SFUNC = tf2p,
166
STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
167
ERROR: function tf2p(anyarray, anyelement) does not exist
169
-- should ERROR: we have no way to resolve S
170
CREATE AGGREGATE myaggp17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray,
171
FINALFUNC = ffp, INITCOND = '{}');
172
ERROR: cannot determine transition data type
173
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
174
CREATE AGGREGATE myaggp17b(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray,
176
ERROR: cannot determine transition data type
177
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
179
-- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement)
180
CREATE AGGREGATE myaggp18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray,
181
FINALFUNC = ffp, INITCOND = '{}');
182
ERROR: cannot determine transition data type
183
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
184
CREATE AGGREGATE myaggp18b(BASETYPE = int, SFUNC = tfp, STYPE = anyarray,
186
ERROR: cannot determine transition data type
187
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
189
-- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int)
190
CREATE AGGREGATE myaggp19a(BASETYPE = anyelement, SFUNC = tf1p,
191
STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
192
ERROR: function tf1p(anyarray, anyelement) does not exist
193
CREATE AGGREGATE myaggp19b(BASETYPE = anyelement, SFUNC = tf1p,
194
STYPE = anyarray, INITCOND = '{}');
195
ERROR: function tf1p(anyarray, anyelement) does not exist
198
CREATE AGGREGATE myaggp20a(BASETYPE = anyelement, SFUNC = tfp,
199
STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
200
CREATE AGGREGATE myaggp20b(BASETYPE = anyelement, SFUNC = tfp,
201
STYPE = anyarray, INITCOND = '{}');
202
-- Case3 (R = N) && (B = A)
203
-- ------------------------
208
CREATE AGGREGATE myaggn01a(BASETYPE = "ANY", SFUNC = stfnp, STYPE = int4[],
209
FINALFUNC = ffnp, INITCOND = '{}');
210
CREATE AGGREGATE myaggn01b(BASETYPE = "ANY", SFUNC = stfnp, STYPE = int4[],
213
-- should ERROR: stfnp(anyarray) not matched by stfnp(int[])
214
CREATE AGGREGATE myaggn02a(BASETYPE = "ANY", SFUNC = stfnp, STYPE = anyarray,
215
FINALFUNC = ffnp, INITCOND = '{}');
216
ERROR: cannot determine transition data type
217
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
218
CREATE AGGREGATE myaggn02b(BASETYPE = "ANY", SFUNC = stfnp, STYPE = anyarray,
220
ERROR: cannot determine transition data type
221
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
224
CREATE AGGREGATE myaggn03a(BASETYPE = "ANY", SFUNC = stfp, STYPE = int4[],
225
FINALFUNC = ffnp, INITCOND = '{}');
227
-- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
228
CREATE AGGREGATE myaggn04a(BASETYPE = "ANY", SFUNC = stfp, STYPE = anyarray,
229
FINALFUNC = ffnp, INITCOND = '{}');
230
ERROR: cannot determine transition data type
231
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
232
-- Case4 (R = N) && ((B = P) || (B = N))
233
-- -------------------------------------
235
-- -----------------------
238
CREATE AGGREGATE myaggn05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[],
239
FINALFUNC = ffnp, INITCOND = '{}');
240
CREATE AGGREGATE myaggn05b(BASETYPE = int, SFUNC = tfnp, STYPE = int[],
244
CREATE AGGREGATE myaggn06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[],
245
FINALFUNC = ffnp, INITCOND = '{}');
246
CREATE AGGREGATE myaggn06b(BASETYPE = int, SFUNC = tf2p, STYPE = int[],
249
-- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int)
250
CREATE AGGREGATE myaggn07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
251
FINALFUNC = ffnp, INITCOND = '{}');
252
ERROR: function tfnp(integer[], anyelement) does not exist
253
CREATE AGGREGATE myaggn07b(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
255
ERROR: function tfnp(integer[], anyelement) does not exist
258
CREATE AGGREGATE myaggn08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
259
FINALFUNC = ffnp, INITCOND = '{}');
260
CREATE AGGREGATE myaggn08b(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
264
CREATE AGGREGATE myaggn09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[],
265
FINALFUNC = ffnp, INITCOND = '{}');
268
CREATE AGGREGATE myaggn10a(BASETYPE = int, SFUNC = tfp, STYPE = int[],
269
FINALFUNC = ffnp, INITCOND = '{}');
271
-- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int)
272
CREATE AGGREGATE myaggn11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
273
FINALFUNC = ffnp, INITCOND = '{}');
274
ERROR: function tf1p(integer[], anyelement) does not exist
276
-- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement)
277
CREATE AGGREGATE myaggn12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
278
FINALFUNC = ffnp, INITCOND = '{}');
279
ERROR: function tfp(integer[], anyelement) does not exist
281
-- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int)
282
CREATE AGGREGATE myaggn13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray,
283
FINALFUNC = ffnp, INITCOND = '{}');
284
ERROR: cannot determine transition data type
285
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
286
CREATE AGGREGATE myaggn13b(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray,
288
ERROR: cannot determine transition data type
289
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
291
-- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement)
292
CREATE AGGREGATE myaggn14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray,
293
FINALFUNC = ffnp, INITCOND = '{}');
294
ERROR: cannot determine transition data type
295
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
296
CREATE AGGREGATE myaggn14b(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray,
298
ERROR: cannot determine transition data type
299
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
301
-- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int)
302
CREATE AGGREGATE myaggn15a(BASETYPE = anyelement, SFUNC = tfnp,
303
STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
304
ERROR: function tfnp(anyarray, anyelement) does not exist
305
CREATE AGGREGATE myaggn15b(BASETYPE = anyelement, SFUNC = tfnp,
306
STYPE = anyarray, INITCOND = '{}');
307
ERROR: function tfnp(anyarray, anyelement) does not exist
309
-- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement)
310
CREATE AGGREGATE myaggn16a(BASETYPE = anyelement, SFUNC = tf2p,
311
STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
312
ERROR: function tf2p(anyarray, anyelement) does not exist
313
CREATE AGGREGATE myaggn16b(BASETYPE = anyelement, SFUNC = tf2p,
314
STYPE = anyarray, INITCOND = '{}');
315
ERROR: function tf2p(anyarray, anyelement) does not exist
317
-- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
318
CREATE AGGREGATE myaggn17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray,
319
FINALFUNC = ffnp, INITCOND = '{}');
320
ERROR: cannot determine transition data type
321
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
323
-- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement)
324
CREATE AGGREGATE myaggn18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray,
325
FINALFUNC = ffnp, INITCOND = '{}');
326
ERROR: cannot determine transition data type
327
DETAIL: An aggregate using "anyarray" or "anyelement" as transition type must have one of them as its base type.
329
-- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int)
330
CREATE AGGREGATE myaggn19a(BASETYPE = anyelement, SFUNC = tf1p,
331
STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
332
ERROR: function tf1p(anyarray, anyelement) does not exist
334
-- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
335
CREATE AGGREGATE myaggn20a(BASETYPE = anyelement, SFUNC = tfp,
336
STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
337
ERROR: function ffnp(anyarray) does not exist
338
-- create test data for polymorphic aggregates
339
create temp table t(f1 int, f2 int[], f3 text);
340
insert into t values(1,array[1],'a');
341
insert into t values(1,array[11],'b');
342
insert into t values(1,array[111],'c');
343
insert into t values(2,array[2],'a');
344
insert into t values(2,array[22],'b');
345
insert into t values(2,array[222],'c');
346
insert into t values(3,array[3],'a');
347
insert into t values(3,array[3],'b');
348
-- test the successfully created polymorphic aggregates
349
select f3, myaggp01a(*) from t group by f3;
357
select f3, myaggp03a(*) from t group by f3;
365
select f3, myaggp03b(*) from t group by f3;
373
select f3, myaggp05a(f1) from t group by f3;
381
select f3, myaggp06a(f1) from t group by f3;
389
select f3, myaggp08a(f1) from t group by f3;
397
select f3, myaggp09a(f1) from t group by f3;
405
select f3, myaggp09b(f1) from t group by f3;
413
select f3, myaggp10a(f1) from t group by f3;
421
select f3, myaggp10b(f1) from t group by f3;
429
select f3, myaggp20a(f1) from t group by f3;
437
select f3, myaggp20b(f1) from t group by f3;
445
select f3, myaggn01a(*) from t group by f3;
453
select f3, myaggn01b(*) from t group by f3;
461
select f3, myaggn03a(*) from t group by f3;
469
select f3, myaggn05a(f1) from t group by f3;
477
select f3, myaggn05b(f1) from t group by f3;
485
select f3, myaggn06a(f1) from t group by f3;
493
select f3, myaggn06b(f1) from t group by f3;
501
select f3, myaggn08a(f1) from t group by f3;
509
select f3, myaggn08b(f1) from t group by f3;
517
select f3, myaggn09a(f1) from t group by f3;
525
select f3, myaggn10a(f1) from t group by f3;