| blob | 6ddd3410ca92f7f9bdaf96b21ed76497e9d49aaa |
1 -- Currently this tests polymorphic aggregates and indirectly does some
2 -- testing of polymorphic SQL functions. It ought to be extended.
3 -- Legend:
4 -----------
5 -- A = type is ANY
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
13 -- ag = aggregate
14 -- tf = trans (state) function
15 -- ff = final function
16 -- rt = return type of a function
17 -- -> = implies
18 -- => = allowed
19 -- !> = not allowed
20 -- E = exists
21 -- NE = not-exists
22 --
23 -- Possible states:
24 -- ----------------
25 -- B = (A || P || N)
26 -- when (B = A) -> (tf2 = NE)
27 -- S = (P || N)
28 -- ff = (E || NE)
29 -- tf1 = (P || N)
30 -- tf2 = (NE || P || N)
31 -- R = (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 -- multi-arg polymorphic
54 CREATE FUNCTION sum3(anyelement,anyelement,anyelement) returns anyelement AS
55 'select $1+$2+$3' language sql strict;
56 -- finalfn polymorphic
57 CREATE FUNCTION ffp(anyarray) RETURNS anyarray AS
58 'select $1' LANGUAGE SQL;
59 -- finalfn non-polymorphic
60 CREATE FUNCTION ffnp(int[]) returns int[] as
61 'select $1' LANGUAGE SQL;
62 -- Try to cover all the possible states:
63 --
64 -- Note: in Cases 1 & 2, we are trying to return P. Therefore, if the transfn
65 -- is stfnp, tfnp, or tf2p, we must use ffp as finalfn, because stfnp, tfnp,
66 -- and tf2p do not return P. Conversely, in Cases 3 & 4, we are trying to
67 -- return N. Therefore, if the transfn is stfp, tfp, or tf1p, we must use ffnp
68 -- as finalfn, because stfp, tfp, and tf1p do not return N.
69 --
70 -- Case1 (R = P) && (B = A)
71 -- ------------------------
72 -- S tf1
73 -- -------
74 -- N N
75 -- should CREATE
76 CREATE AGGREGATE myaggp01a(*) (SFUNC = stfnp, STYPE = int4[],
77 FINALFUNC = ffp, INITCOND = '{}');
78 -- P N
79 -- should ERROR: stfnp(anyarray) not matched by stfnp(int[])
80 CREATE AGGREGATE myaggp02a(*) (SFUNC = stfnp, STYPE = anyarray,
81 FINALFUNC = ffp, INITCOND = '{}');
82 ERROR: cannot determine transition data type
83 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
84 -- N P
85 -- should CREATE
86 CREATE AGGREGATE myaggp03a(*) (SFUNC = stfp, STYPE = int4[],
87 FINALFUNC = ffp, INITCOND = '{}');
88 CREATE AGGREGATE myaggp03b(*) (SFUNC = stfp, STYPE = int4[],
89 INITCOND = '{}');
90 -- P P
91 -- should ERROR: we have no way to resolve S
92 CREATE AGGREGATE myaggp04a(*) (SFUNC = stfp, STYPE = anyarray,
93 FINALFUNC = ffp, INITCOND = '{}');
94 ERROR: cannot determine transition data type
95 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
96 CREATE AGGREGATE myaggp04b(*) (SFUNC = stfp, STYPE = anyarray,
97 INITCOND = '{}');
98 ERROR: cannot determine transition data type
99 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
100 -- Case2 (R = P) && ((B = P) || (B = N))
101 -- -------------------------------------
102 -- S tf1 B tf2
103 -- -----------------------
104 -- N N N N
105 -- should CREATE
106 CREATE AGGREGATE myaggp05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[],
107 FINALFUNC = ffp, INITCOND = '{}');
108 -- N N N P
109 -- should CREATE
110 CREATE AGGREGATE myaggp06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[],
111 FINALFUNC = ffp, INITCOND = '{}');
112 -- N N P N
113 -- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int)
114 CREATE AGGREGATE myaggp07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
115 FINALFUNC = ffp, INITCOND = '{}');
116 ERROR: function tfnp(integer[], anyelement) does not exist
117 -- N N P P
118 -- should CREATE
119 CREATE AGGREGATE myaggp08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
120 FINALFUNC = ffp, INITCOND = '{}');
121 -- N P N N
122 -- should CREATE
123 CREATE AGGREGATE myaggp09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[],
124 FINALFUNC = ffp, INITCOND = '{}');
125 CREATE AGGREGATE myaggp09b(BASETYPE = int, SFUNC = tf1p, STYPE = int[],
126 INITCOND = '{}');
127 -- N P N P
128 -- should CREATE
129 CREATE AGGREGATE myaggp10a(BASETYPE = int, SFUNC = tfp, STYPE = int[],
130 FINALFUNC = ffp, INITCOND = '{}');
131 CREATE AGGREGATE myaggp10b(BASETYPE = int, SFUNC = tfp, STYPE = int[],
132 INITCOND = '{}');
133 -- N P P N
134 -- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int)
135 CREATE AGGREGATE myaggp11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
136 FINALFUNC = ffp, INITCOND = '{}');
137 ERROR: function tf1p(integer[], anyelement) does not exist
138 CREATE AGGREGATE myaggp11b(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
139 INITCOND = '{}');
140 ERROR: function tf1p(integer[], anyelement) does not exist
141 -- N P P P
142 -- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement)
143 CREATE AGGREGATE myaggp12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
144 FINALFUNC = ffp, INITCOND = '{}');
145 ERROR: function tfp(integer[], anyelement) does not exist
146 CREATE AGGREGATE myaggp12b(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
147 INITCOND = '{}');
148 ERROR: function tfp(integer[], anyelement) does not exist
149 -- P N N N
150 -- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int)
151 CREATE AGGREGATE myaggp13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray,
152 FINALFUNC = ffp, INITCOND = '{}');
153 ERROR: cannot determine transition data type
154 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
155 -- P N N P
156 -- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement)
157 CREATE AGGREGATE myaggp14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray,
158 FINALFUNC = ffp, INITCOND = '{}');
159 ERROR: cannot determine transition data type
160 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
161 -- P N P N
162 -- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int)
163 CREATE AGGREGATE myaggp15a(BASETYPE = anyelement, SFUNC = tfnp,
164 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
165 ERROR: function tfnp(anyarray, anyelement) does not exist
166 -- P N P P
167 -- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement)
168 CREATE AGGREGATE myaggp16a(BASETYPE = anyelement, SFUNC = tf2p,
169 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
170 ERROR: function tf2p(anyarray, anyelement) does not exist
171 -- P P N N
172 -- should ERROR: we have no way to resolve S
173 CREATE AGGREGATE myaggp17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray,
174 FINALFUNC = ffp, INITCOND = '{}');
175 ERROR: cannot determine transition data type
176 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
177 CREATE AGGREGATE myaggp17b(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray,
178 INITCOND = '{}');
179 ERROR: cannot determine transition data type
180 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
181 -- P P N P
182 -- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement)
183 CREATE AGGREGATE myaggp18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray,
184 FINALFUNC = ffp, INITCOND = '{}');
185 ERROR: cannot determine transition data type
186 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
187 CREATE AGGREGATE myaggp18b(BASETYPE = int, SFUNC = tfp, STYPE = anyarray,
188 INITCOND = '{}');
189 ERROR: cannot determine transition data type
190 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
191 -- P P P N
192 -- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int)
193 CREATE AGGREGATE myaggp19a(BASETYPE = anyelement, SFUNC = tf1p,
194 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
195 ERROR: function tf1p(anyarray, anyelement) does not exist
196 CREATE AGGREGATE myaggp19b(BASETYPE = anyelement, SFUNC = tf1p,
197 STYPE = anyarray, INITCOND = '{}');
198 ERROR: function tf1p(anyarray, anyelement) does not exist
199 -- P P P P
200 -- should CREATE
201 CREATE AGGREGATE myaggp20a(BASETYPE = anyelement, SFUNC = tfp,
202 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
203 CREATE AGGREGATE myaggp20b(BASETYPE = anyelement, SFUNC = tfp,
204 STYPE = anyarray, INITCOND = '{}');
205 -- Case3 (R = N) && (B = A)
206 -- ------------------------
207 -- S tf1
208 -- -------
209 -- N N
210 -- should CREATE
211 CREATE AGGREGATE myaggn01a(*) (SFUNC = stfnp, STYPE = int4[],
212 FINALFUNC = ffnp, INITCOND = '{}');
213 CREATE AGGREGATE myaggn01b(*) (SFUNC = stfnp, STYPE = int4[],
214 INITCOND = '{}');
215 -- P N
216 -- should ERROR: stfnp(anyarray) not matched by stfnp(int[])
217 CREATE AGGREGATE myaggn02a(*) (SFUNC = stfnp, STYPE = anyarray,
218 FINALFUNC = ffnp, INITCOND = '{}');
219 ERROR: cannot determine transition data type
220 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
221 CREATE AGGREGATE myaggn02b(*) (SFUNC = stfnp, STYPE = anyarray,
222 INITCOND = '{}');
223 ERROR: cannot determine transition data type
224 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
225 -- N P
226 -- should CREATE
227 CREATE AGGREGATE myaggn03a(*) (SFUNC = stfp, STYPE = int4[],
228 FINALFUNC = ffnp, INITCOND = '{}');
229 -- P P
230 -- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
231 CREATE AGGREGATE myaggn04a(*) (SFUNC = stfp, STYPE = anyarray,
232 FINALFUNC = ffnp, INITCOND = '{}');
233 ERROR: cannot determine transition data type
234 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
235 -- Case4 (R = N) && ((B = P) || (B = N))
236 -- -------------------------------------
237 -- S tf1 B tf2
238 -- -----------------------
239 -- N N N N
240 -- should CREATE
241 CREATE AGGREGATE myaggn05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[],
242 FINALFUNC = ffnp, INITCOND = '{}');
243 CREATE AGGREGATE myaggn05b(BASETYPE = int, SFUNC = tfnp, STYPE = int[],
244 INITCOND = '{}');
245 -- N N N P
246 -- should CREATE
247 CREATE AGGREGATE myaggn06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[],
248 FINALFUNC = ffnp, INITCOND = '{}');
249 CREATE AGGREGATE myaggn06b(BASETYPE = int, SFUNC = tf2p, STYPE = int[],
250 INITCOND = '{}');
251 -- N N P N
252 -- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int)
253 CREATE AGGREGATE myaggn07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
254 FINALFUNC = ffnp, INITCOND = '{}');
255 ERROR: function tfnp(integer[], anyelement) does not exist
256 CREATE AGGREGATE myaggn07b(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
257 INITCOND = '{}');
258 ERROR: function tfnp(integer[], anyelement) does not exist
259 -- N N P P
260 -- should CREATE
261 CREATE AGGREGATE myaggn08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
262 FINALFUNC = ffnp, INITCOND = '{}');
263 CREATE AGGREGATE myaggn08b(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
264 INITCOND = '{}');
265 -- N P N N
266 -- should CREATE
267 CREATE AGGREGATE myaggn09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[],
268 FINALFUNC = ffnp, INITCOND = '{}');
269 -- N P N P
270 -- should CREATE
271 CREATE AGGREGATE myaggn10a(BASETYPE = int, SFUNC = tfp, STYPE = int[],
272 FINALFUNC = ffnp, INITCOND = '{}');
273 -- N P P N
274 -- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int)
275 CREATE AGGREGATE myaggn11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
276 FINALFUNC = ffnp, INITCOND = '{}');
277 ERROR: function tf1p(integer[], anyelement) does not exist
278 -- N P P P
279 -- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement)
280 CREATE AGGREGATE myaggn12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
281 FINALFUNC = ffnp, INITCOND = '{}');
282 ERROR: function tfp(integer[], anyelement) does not exist
283 -- P N N N
284 -- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int)
285 CREATE AGGREGATE myaggn13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray,
286 FINALFUNC = ffnp, INITCOND = '{}');
287 ERROR: cannot determine transition data type
288 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
289 CREATE AGGREGATE myaggn13b(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray,
290 INITCOND = '{}');
291 ERROR: cannot determine transition data type
292 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
293 -- P N N P
294 -- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement)
295 CREATE AGGREGATE myaggn14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray,
296 FINALFUNC = ffnp, INITCOND = '{}');
297 ERROR: cannot determine transition data type
298 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
299 CREATE AGGREGATE myaggn14b(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray,
300 INITCOND = '{}');
301 ERROR: cannot determine transition data type
302 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
303 -- P N P N
304 -- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int)
305 CREATE AGGREGATE myaggn15a(BASETYPE = anyelement, SFUNC = tfnp,
306 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
307 ERROR: function tfnp(anyarray, anyelement) does not exist
308 CREATE AGGREGATE myaggn15b(BASETYPE = anyelement, SFUNC = tfnp,
309 STYPE = anyarray, INITCOND = '{}');
310 ERROR: function tfnp(anyarray, anyelement) does not exist
311 -- P N P P
312 -- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement)
313 CREATE AGGREGATE myaggn16a(BASETYPE = anyelement, SFUNC = tf2p,
314 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
315 ERROR: function tf2p(anyarray, anyelement) does not exist
316 CREATE AGGREGATE myaggn16b(BASETYPE = anyelement, SFUNC = tf2p,
317 STYPE = anyarray, INITCOND = '{}');
318 ERROR: function tf2p(anyarray, anyelement) does not exist
319 -- P P N N
320 -- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
321 CREATE AGGREGATE myaggn17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray,
322 FINALFUNC = ffnp, INITCOND = '{}');
323 ERROR: cannot determine transition data type
324 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
325 -- P P N P
326 -- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement)
327 CREATE AGGREGATE myaggn18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray,
328 FINALFUNC = ffnp, INITCOND = '{}');
329 ERROR: cannot determine transition data type
330 DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.
331 -- P P P N
332 -- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int)
333 CREATE AGGREGATE myaggn19a(BASETYPE = anyelement, SFUNC = tf1p,
334 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
335 ERROR: function tf1p(anyarray, anyelement) does not exist
336 -- P P P P
337 -- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
338 CREATE AGGREGATE myaggn20a(BASETYPE = anyelement, SFUNC = tfp,
339 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
340 ERROR: function ffnp(anyarray) does not exist
341 -- multi-arg polymorphic
342 CREATE AGGREGATE mysum2(anyelement,anyelement) (SFUNC = sum3,
343 STYPE = anyelement, INITCOND = '0');
344 -- create test data for polymorphic aggregates
345 create temp table t(f1 int, f2 int[], f3 text);
346 insert into t values(1,array[1],'a');
347 insert into t values(1,array[11],'b');
348 insert into t values(1,array[111],'c');
349 insert into t values(2,array[2],'a');
350 insert into t values(2,array[22],'b');
351 insert into t values(2,array[222],'c');
352 insert into t values(3,array[3],'a');
353 insert into t values(3,array[3],'b');
354 -- test the successfully created polymorphic aggregates
355 select f3, myaggp01a(*) from t group by f3;
356 f3 | myaggp01a
357 ----+-----------
358 b | {}
359 c | {}
360 a | {}
361 (3 rows)
363 select f3, myaggp03a(*) from t group by f3;
364 f3 | myaggp03a
365 ----+-----------
366 b | {}
367 c | {}
368 a | {}
369 (3 rows)
371 select f3, myaggp03b(*) from t group by f3;
372 f3 | myaggp03b
373 ----+-----------
374 b | {}
375 c | {}
376 a | {}
377 (3 rows)
379 select f3, myaggp05a(f1) from t group by f3;
380 f3 | myaggp05a
381 ----+-----------
382 b | {1,2,3}
383 c | {1,2}
384 a | {1,2,3}
385 (3 rows)
387 select f3, myaggp06a(f1) from t group by f3;
388 f3 | myaggp06a
389 ----+-----------
390 b | {}
391 c | {}
392 a | {}
393 (3 rows)
395 select f3, myaggp08a(f1) from t group by f3;
396 f3 | myaggp08a
397 ----+-----------
398 b | {}
399 c | {}
400 a | {}
401 (3 rows)
403 select f3, myaggp09a(f1) from t group by f3;
404 f3 | myaggp09a
405 ----+-----------
406 b | {}
407 c | {}
408 a | {}
409 (3 rows)
411 select f3, myaggp09b(f1) from t group by f3;
412 f3 | myaggp09b
413 ----+-----------
414 b | {}
415 c | {}
416 a | {}
417 (3 rows)
419 select f3, myaggp10a(f1) from t group by f3;
420 f3 | myaggp10a
421 ----+-----------
422 b | {1,2,3}
423 c | {1,2}
424 a | {1,2,3}
425 (3 rows)
427 select f3, myaggp10b(f1) from t group by f3;
428 f3 | myaggp10b
429 ----+-----------
430 b | {1,2,3}
431 c | {1,2}
432 a | {1,2,3}
433 (3 rows)
435 select f3, myaggp20a(f1) from t group by f3;
436 f3 | myaggp20a
437 ----+-----------
438 b | {1,2,3}
439 c | {1,2}
440 a | {1,2,3}
441 (3 rows)
443 select f3, myaggp20b(f1) from t group by f3;
444 f3 | myaggp20b
445 ----+-----------
446 b | {1,2,3}
447 c | {1,2}
448 a | {1,2,3}
449 (3 rows)
451 select f3, myaggn01a(*) from t group by f3;
452 f3 | myaggn01a
453 ----+-----------
454 b | {}
455 c | {}
456 a | {}
457 (3 rows)
459 select f3, myaggn01b(*) from t group by f3;
460 f3 | myaggn01b
461 ----+-----------
462 b | {}
463 c | {}
464 a | {}
465 (3 rows)
467 select f3, myaggn03a(*) from t group by f3;
468 f3 | myaggn03a
469 ----+-----------
470 b | {}
471 c | {}
472 a | {}
473 (3 rows)
475 select f3, myaggn05a(f1) from t group by f3;
476 f3 | myaggn05a
477 ----+-----------
478 b | {1,2,3}
479 c | {1,2}
480 a | {1,2,3}
481 (3 rows)
483 select f3, myaggn05b(f1) from t group by f3;
484 f3 | myaggn05b
485 ----+-----------
486 b | {1,2,3}
487 c | {1,2}
488 a | {1,2,3}
489 (3 rows)
491 select f3, myaggn06a(f1) from t group by f3;
492 f3 | myaggn06a
493 ----+-----------
494 b | {}
495 c | {}
496 a | {}
497 (3 rows)
499 select f3, myaggn06b(f1) from t group by f3;
500 f3 | myaggn06b
501 ----+-----------
502 b | {}
503 c | {}
504 a | {}
505 (3 rows)
507 select f3, myaggn08a(f1) from t group by f3;
508 f3 | myaggn08a
509 ----+-----------
510 b | {}
511 c | {}
512 a | {}
513 (3 rows)
515 select f3, myaggn08b(f1) from t group by f3;
516 f3 | myaggn08b
517 ----+-----------
518 b | {}
519 c | {}
520 a | {}
521 (3 rows)
523 select f3, myaggn09a(f1) from t group by f3;
524 f3 | myaggn09a
525 ----+-----------
526 b | {}
527 c | {}
528 a | {}
529 (3 rows)
531 select f3, myaggn10a(f1) from t group by f3;
532 f3 | myaggn10a
533 ----+-----------
534 b | {1,2,3}
535 c | {1,2}
536 a | {1,2,3}
537 (3 rows)
539 select mysum2(f1, f1 + 1) from t;
540 mysum2
541 --------
542 38
543 (1 row)
545 -- test inlining of polymorphic SQL functions
546 create function bleat(int) returns int as $$
547 begin
548 raise notice 'bleat %', $1;
549 return $1;
550 end$$ language plpgsql;
551 create function sql_if(bool, anyelement, anyelement) returns anyelement as $$
552 select case when $1 then $2 else $3 end $$ language sql;
553 -- Note this would fail with integer overflow, never mind wrong bleat() output,
554 -- if the CASE expression were not successfully inlined
555 select f1, sql_if(f1 > 0, bleat(f1), bleat(f1 + 1)) from int4_tbl;
556 NOTICE: bleat 1
557 NOTICE: bleat 123456
558 NOTICE: bleat -123455
559 NOTICE: bleat 2147483647
560 NOTICE: bleat -2147483646
561 f1 | sql_if
562 -------------+-------------
563 0 | 1
564 123456 | 123456
565 -123456 | -123455
566 2147483647 | 2147483647
567 -2147483647 | -2147483646
568 (5 rows)
570 select q2, sql_if(q2 > 0, q2, q2 + 1) from int8_tbl;
571 q2 | sql_if
572 -------------------+-------------------
573 456 | 456
574 4567890123456789 | 4567890123456789
575 123 | 123
576 4567890123456789 | 4567890123456789
577 -4567890123456789 | -4567890123456788
578 (5 rows)
580 -- another kind of polymorphic aggregate
581 create function add_group(grp anyarray, ad anyelement, size integer)
582 returns anyarray
583 as $$
584 begin
585 if grp is null then
586 return array[ad];
587 end if;
588 if array_upper(grp, 1) < size then
589 return grp || ad;
590 end if;
591 return grp;
592 end;
593 $$
594 language plpgsql immutable;
595 create aggregate build_group(anyelement, integer) (
596 SFUNC = add_group,
597 STYPE = anyarray
598 );
599 select build_group(q1,3) from int8_tbl;
600 build_group
601 ----------------------------
602 {123,123,4567890123456789}
603 (1 row)
605 -- this should fail because stype isn't compatible with arg
606 create aggregate build_group(int8, integer) (
607 SFUNC = add_group,
608 STYPE = int2[]
609 );
610 ERROR: function add_group(smallint[], bigint, integer) does not exist
611 -- but we can make a non-poly agg from a poly sfunc if types are OK
612 create aggregate build_group(int8, integer) (
613 SFUNC = add_group,
614 STYPE = int8[]
615 );
616 -- test variadic polymorphic functions
617 create function myleast(variadic anyarray) returns anyelement as $$
618 select min($1[i]) from generate_subscripts($1,1) g(i)
619 $$ language sql immutable strict;
620 select myleast(10, 1, 20, 33);
621 myleast
622 ---------
623 1
624 (1 row)
626 select myleast(1.1, 0.22, 0.55);
627 myleast
628 ---------
629 0.22
630 (1 row)
632 select myleast('z'::text);
633 myleast
634 ---------
635 z
636 (1 row)
638 select myleast(); -- fail
639 ERROR: function myleast() does not exist
640 LINE 1: select myleast();
641 ^
642 HINT: No function matches the given name and argument types. You might need to add explicit type casts.
643 -- test with variadic call parameter
644 select myleast(variadic array[1,2,3,4,-1]);
645 myleast
646 ---------
647 -1
648 (1 row)
650 select myleast(variadic array[1.1, -5.5]);
651 myleast
652 ---------
653 -5.5
654 (1 row)
656 --test with empty variadic call parameter
657 select myleast(variadic array[]::int[]);
658 myleast
659 ---------
661 (1 row)
663 -- an example with some ordinary arguments too
664 create function concat(text, variadic anyarray) returns text as $$
665 select array_to_string($2, $1);
666 $$ language sql immutable strict;
667 select concat('%', 1, 2, 3, 4, 5);
668 concat
669 -----------
670 1%2%3%4%5
671 (1 row)
673 select concat('|', 'a'::text, 'b', 'c');
674 concat
675 --------
676 a|b|c
677 (1 row)
679 select concat('|', variadic array[1,2,33]);
680 concat
681 --------
682 1|2|33
683 (1 row)
685 select concat('|', variadic array[]::int[]);
686 concat
687 --------
689 (1 row)
691 drop function concat(text, anyarray);
692 -- mix variadic with anyelement
693 create function formarray(anyelement, variadic anyarray) returns anyarray as $$
694 select array_prepend($1, $2);
695 $$ language sql immutable strict;
696 select formarray(1,2,3,4,5);
697 formarray
698 -------------
699 {1,2,3,4,5}
700 (1 row)
702 select formarray(1.1, variadic array[1.2,55.5]);
703 formarray
704 ----------------
705 {1.1,1.2,55.5}
706 (1 row)
708 select formarray(1.1, array[1.2,55.5]); -- fail without variadic
709 ERROR: function formarray(numeric, numeric[]) does not exist
710 LINE 1: select formarray(1.1, array[1.2,55.5]);
711 ^
712 HINT: No function matches the given name and argument types. You might need to add explicit type casts.
713 select formarray(1, 'x'::text); -- fail, type mismatch
714 ERROR: function formarray(integer, text) does not exist
715 LINE 1: select formarray(1, 'x'::text);
716 ^
717 HINT: No function matches the given name and argument types. You might need to add explicit type casts.
718 select formarray(1, variadic array['x'::text]); -- fail, type mismatch
719 ERROR: function formarray(integer, text[]) does not exist
720 LINE 1: select formarray(1, variadic array['x'::text]);
721 ^
722 HINT: No function matches the given name and argument types. You might need to add explicit type casts.
723 drop function formarray(anyelement, variadic anyarray);
724 -- test pg_typeof() function
725 select pg_typeof(null); -- unknown
726 pg_typeof
727 -----------
728 unknown
729 (1 row)
731 select pg_typeof(0); -- integer
732 pg_typeof
733 -----------
734 integer
735 (1 row)
737 select pg_typeof(0.0); -- numeric
738 pg_typeof
739 -----------
740 numeric
741 (1 row)
743 select pg_typeof(1+1 = 2); -- boolean
744 pg_typeof
745 -----------
746 boolean
747 (1 row)
749 select pg_typeof('x'); -- unknown
750 pg_typeof
751 -----------
752 unknown
753 (1 row)
755 select pg_typeof('' || ''); -- text
756 pg_typeof
757 -----------
758 text
759 (1 row)
761 select pg_typeof(pg_typeof(0)); -- regtype
762 pg_typeof
763 -----------
764 regtype
765 (1 row)
767 select pg_typeof(array[1.2,55.5]); -- numeric[]
768 pg_typeof
769 -----------
770 numeric[]
771 (1 row)
773 select pg_typeof(myleast(10, 1, 20, 33)); -- polymorphic input
774 pg_typeof
775 -----------
776 integer
777 (1 row)