1 -- Currently this tests polymorphic aggregates and indirectly does some
2 -- testing of polymorphic SQL functions. It ought to be extended.
8 -- P = type is polymorphic
9 -- N = type is non-polymorphic
10 -- B = aggregate base type
11 -- S = aggregate state type
12 -- R = aggregate return type
13 -- 1 = arg1 of a function
14 -- 2 = arg2 of a function
16 -- tf = trans (state) function
17 -- ff = final function
18 -- rt = return type of a function
28 -- when (B = A) -> (tf2 = NE)
32 -- tf2 = (NE || P || N)
35 -- create functions for use as tf and ff with the needed combinations of
36 -- argument polymorphism, but within the constraints of valid aggregate
37 -- functions, i.e. tf arg1 and tf return type must match
39 -- polymorphic single arg transfn
40 CREATE FUNCTION stfp(anyarray) RETURNS anyarray AS
41 'select $1' LANGUAGE SQL;
42 -- non-polymorphic single arg transfn
43 CREATE FUNCTION stfnp(int[]) RETURNS int[] AS
44 'select $1' LANGUAGE SQL;
46 -- dual polymorphic transfn
47 CREATE FUNCTION tfp(anyarray,anyelement) RETURNS anyarray AS
48 'select $1 || $2' LANGUAGE SQL;
49 -- dual non-polymorphic transfn
50 CREATE FUNCTION tfnp(int[],int) RETURNS int[] AS
51 'select $1 || $2' LANGUAGE SQL;
53 -- arg1 only polymorphic transfn
54 CREATE FUNCTION tf1p(anyarray,int) RETURNS anyarray AS
55 'select $1' LANGUAGE SQL;
56 -- arg2 only polymorphic transfn
57 CREATE FUNCTION tf2p(int[],anyelement) RETURNS int[] AS
58 'select $1' LANGUAGE SQL;
60 -- multi-arg polymorphic
61 CREATE FUNCTION sum3(anyelement,anyelement,anyelement) returns anyelement AS
62 'select $1+$2+$3' language sql strict;
64 -- finalfn polymorphic
65 CREATE FUNCTION ffp(anyarray) RETURNS anyarray AS
66 'select $1' LANGUAGE SQL;
67 -- finalfn non-polymorphic
68 CREATE FUNCTION ffnp(int[]) returns int[] as
69 'select $1' LANGUAGE SQL;
71 -- Try to cover all the possible states:
73 -- Note: in Cases 1 & 2, we are trying to return P. Therefore, if the transfn
74 -- is stfnp, tfnp, or tf2p, we must use ffp as finalfn, because stfnp, tfnp,
75 -- and tf2p do not return P. Conversely, in Cases 3 & 4, we are trying to
76 -- return N. Therefore, if the transfn is stfp, tfp, or tf1p, we must use ffnp
77 -- as finalfn, because stfp, tfp, and tf1p do not return N.
79 -- Case1 (R = P) && (B = A)
80 -- ------------------------
85 CREATE AGGREGATE myaggp01a(*) (SFUNC = stfnp, STYPE = int4[],
86 FINALFUNC = ffp, INITCOND = '{}');
89 -- should ERROR: stfnp(anyarray) not matched by stfnp(int[])
90 CREATE AGGREGATE myaggp02a(*) (SFUNC = stfnp, STYPE = anyarray,
91 FINALFUNC = ffp, INITCOND = '{}');
95 CREATE AGGREGATE myaggp03a(*) (SFUNC = stfp, STYPE = int4[],
96 FINALFUNC = ffp, INITCOND = '{}');
97 CREATE AGGREGATE myaggp03b(*) (SFUNC = stfp, STYPE = int4[],
101 -- should ERROR: we have no way to resolve S
102 CREATE AGGREGATE myaggp04a(*) (SFUNC = stfp, STYPE = anyarray,
103 FINALFUNC = ffp, INITCOND = '{}');
104 CREATE AGGREGATE myaggp04b(*) (SFUNC = stfp, STYPE = anyarray,
108 -- Case2 (R = P) && ((B = P) || (B = N))
109 -- -------------------------------------
111 -- -----------------------
114 CREATE AGGREGATE myaggp05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[],
115 FINALFUNC = ffp, INITCOND = '{}');
119 CREATE AGGREGATE myaggp06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[],
120 FINALFUNC = ffp, INITCOND = '{}');
123 -- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int)
124 CREATE AGGREGATE myaggp07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
125 FINALFUNC = ffp, INITCOND = '{}');
129 CREATE AGGREGATE myaggp08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
130 FINALFUNC = ffp, INITCOND = '{}');
134 CREATE AGGREGATE myaggp09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[],
135 FINALFUNC = ffp, INITCOND = '{}');
136 CREATE AGGREGATE myaggp09b(BASETYPE = int, SFUNC = tf1p, STYPE = int[],
141 CREATE AGGREGATE myaggp10a(BASETYPE = int, SFUNC = tfp, STYPE = int[],
142 FINALFUNC = ffp, INITCOND = '{}');
143 CREATE AGGREGATE myaggp10b(BASETYPE = int, SFUNC = tfp, STYPE = int[],
147 -- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int)
148 CREATE AGGREGATE myaggp11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
149 FINALFUNC = ffp, INITCOND = '{}');
150 CREATE AGGREGATE myaggp11b(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
154 -- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement)
155 CREATE AGGREGATE myaggp12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
156 FINALFUNC = ffp, INITCOND = '{}');
157 CREATE AGGREGATE myaggp12b(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
161 -- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int)
162 CREATE AGGREGATE myaggp13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray,
163 FINALFUNC = ffp, INITCOND = '{}');
166 -- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement)
167 CREATE AGGREGATE myaggp14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray,
168 FINALFUNC = ffp, INITCOND = '{}');
171 -- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int)
172 CREATE AGGREGATE myaggp15a(BASETYPE = anyelement, SFUNC = tfnp,
173 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
176 -- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement)
177 CREATE AGGREGATE myaggp16a(BASETYPE = anyelement, SFUNC = tf2p,
178 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
181 -- should ERROR: we have no way to resolve S
182 CREATE AGGREGATE myaggp17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray,
183 FINALFUNC = ffp, INITCOND = '{}');
184 CREATE AGGREGATE myaggp17b(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray,
188 -- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement)
189 CREATE AGGREGATE myaggp18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray,
190 FINALFUNC = ffp, INITCOND = '{}');
191 CREATE AGGREGATE myaggp18b(BASETYPE = int, SFUNC = tfp, STYPE = anyarray,
195 -- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int)
196 CREATE AGGREGATE myaggp19a(BASETYPE = anyelement, SFUNC = tf1p,
197 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
198 CREATE AGGREGATE myaggp19b(BASETYPE = anyelement, SFUNC = tf1p,
199 STYPE = anyarray, INITCOND = '{}');
203 CREATE AGGREGATE myaggp20a(BASETYPE = anyelement, SFUNC = tfp,
204 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}');
205 CREATE AGGREGATE myaggp20b(BASETYPE = anyelement, SFUNC = tfp,
206 STYPE = anyarray, INITCOND = '{}');
208 -- Case3 (R = N) && (B = A)
209 -- ------------------------
214 CREATE AGGREGATE myaggn01a(*) (SFUNC = stfnp, STYPE = int4[],
215 FINALFUNC = ffnp, INITCOND = '{}');
216 CREATE AGGREGATE myaggn01b(*) (SFUNC = stfnp, STYPE = int4[],
220 -- should ERROR: stfnp(anyarray) not matched by stfnp(int[])
221 CREATE AGGREGATE myaggn02a(*) (SFUNC = stfnp, STYPE = anyarray,
222 FINALFUNC = ffnp, INITCOND = '{}');
223 CREATE AGGREGATE myaggn02b(*) (SFUNC = stfnp, STYPE = anyarray,
228 CREATE AGGREGATE myaggn03a(*) (SFUNC = stfp, STYPE = int4[],
229 FINALFUNC = ffnp, INITCOND = '{}');
232 -- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
233 CREATE AGGREGATE myaggn04a(*) (SFUNC = stfp, STYPE = anyarray,
234 FINALFUNC = ffnp, INITCOND = '{}');
237 -- Case4 (R = N) && ((B = P) || (B = N))
238 -- -------------------------------------
240 -- -----------------------
243 CREATE AGGREGATE myaggn05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[],
244 FINALFUNC = ffnp, INITCOND = '{}');
245 CREATE AGGREGATE myaggn05b(BASETYPE = int, SFUNC = tfnp, STYPE = int[],
250 CREATE AGGREGATE myaggn06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[],
251 FINALFUNC = ffnp, INITCOND = '{}');
252 CREATE AGGREGATE myaggn06b(BASETYPE = int, SFUNC = tf2p, STYPE = int[],
256 -- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int)
257 CREATE AGGREGATE myaggn07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
258 FINALFUNC = ffnp, INITCOND = '{}');
259 CREATE AGGREGATE myaggn07b(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
264 CREATE AGGREGATE myaggn08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
265 FINALFUNC = ffnp, INITCOND = '{}');
266 CREATE AGGREGATE myaggn08b(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
271 CREATE AGGREGATE myaggn09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[],
272 FINALFUNC = ffnp, INITCOND = '{}');
276 CREATE AGGREGATE myaggn10a(BASETYPE = int, SFUNC = tfp, STYPE = int[],
277 FINALFUNC = ffnp, INITCOND = '{}');
280 -- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int)
281 CREATE AGGREGATE myaggn11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
282 FINALFUNC = ffnp, INITCOND = '{}');
285 -- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement)
286 CREATE AGGREGATE myaggn12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
287 FINALFUNC = ffnp, INITCOND = '{}');
290 -- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int)
291 CREATE AGGREGATE myaggn13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray,
292 FINALFUNC = ffnp, INITCOND = '{}');
293 CREATE AGGREGATE myaggn13b(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray,
297 -- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement)
298 CREATE AGGREGATE myaggn14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray,
299 FINALFUNC = ffnp, INITCOND = '{}');
300 CREATE AGGREGATE myaggn14b(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray,
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 CREATE AGGREGATE myaggn15b(BASETYPE = anyelement, SFUNC = tfnp,
308 STYPE = anyarray, INITCOND = '{}');
311 -- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement)
312 CREATE AGGREGATE myaggn16a(BASETYPE = anyelement, SFUNC = tf2p,
313 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
314 CREATE AGGREGATE myaggn16b(BASETYPE = anyelement, SFUNC = tf2p,
315 STYPE = anyarray, INITCOND = '{}');
318 -- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
319 CREATE AGGREGATE myaggn17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray,
320 FINALFUNC = ffnp, INITCOND = '{}');
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 = '{}');
328 -- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int)
329 CREATE AGGREGATE myaggn19a(BASETYPE = anyelement, SFUNC = tf1p,
330 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
333 -- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
334 CREATE AGGREGATE myaggn20a(BASETYPE = anyelement, SFUNC = tfp,
335 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}');
337 -- multi-arg polymorphic
338 CREATE AGGREGATE mysum2(anyelement,anyelement) (SFUNC = sum3,
339 STYPE = anyelement, INITCOND = '0');
341 -- create test data for polymorphic aggregates
342 create temp table t(f1 int, f2 int[], f3 text);
343 insert into t values(1,array[1],'a');
344 insert into t values(1,array[11],'b');
345 insert into t values(1,array[111],'c');
346 insert into t values(2,array[2],'a');
347 insert into t values(2,array[22],'b');
348 insert into t values(2,array[222],'c');
349 insert into t values(3,array[3],'a');
350 insert into t values(3,array[3],'b');
352 -- test the successfully created polymorphic aggregates
353 select f3, myaggp01a(*) from t group by f3;
354 select f3, myaggp03a(*) from t group by f3;
355 select f3, myaggp03b(*) from t group by f3;
356 select f3, myaggp05a(f1) from t group by f3;
357 select f3, myaggp06a(f1) from t group by f3;
358 select f3, myaggp08a(f1) from t group by f3;
359 select f3, myaggp09a(f1) from t group by f3;
360 select f3, myaggp09b(f1) from t group by f3;
361 select f3, myaggp10a(f1) from t group by f3;
362 select f3, myaggp10b(f1) from t group by f3;
363 select f3, myaggp20a(f1) from t group by f3;
364 select f3, myaggp20b(f1) from t group by f3;
365 select f3, myaggn01a(*) from t group by f3;
366 select f3, myaggn01b(*) from t group by f3;
367 select f3, myaggn03a(*) from t group by f3;
368 select f3, myaggn05a(f1) from t group by f3;
369 select f3, myaggn05b(f1) from t group by f3;
370 select f3, myaggn06a(f1) from t group by f3;
371 select f3, myaggn06b(f1) from t group by f3;
372 select f3, myaggn08a(f1) from t group by f3;
373 select f3, myaggn08b(f1) from t group by f3;
374 select f3, myaggn09a(f1) from t group by f3;
375 select f3, myaggn10a(f1) from t group by f3;
376 select mysum2(f1, f1 + 1) from t;
378 -- test inlining of polymorphic SQL functions
379 create function bleat(int) returns int as $$
381 raise notice 'bleat %', $1;
383 end$$ language plpgsql;
385 create function sql_if(bool, anyelement, anyelement) returns anyelement as $$
386 select case when $1 then $2 else $3 end $$ language sql;
388 -- Note this would fail with integer overflow, never mind wrong bleat() output,
389 -- if the CASE expression were not successfully inlined
390 select f1, sql_if(f1 > 0, bleat(f1), bleat(f1 + 1)) from int4_tbl;
392 select q2, sql_if(q2 > 0, q2, q2 + 1) from int8_tbl;
394 -- another kind of polymorphic aggregate
396 create function add_group(grp anyarray, ad anyelement, size integer)
403 if array_upper(grp, 1) < size then
409 language plpgsql immutable;
411 create aggregate build_group(anyelement, integer) (
416 select build_group(q1,3) from int8_tbl;
418 -- this should fail because stype isn't compatible with arg
419 create aggregate build_group(int8, integer) (
424 -- but we can make a non-poly agg from a poly sfunc if types are OK
425 create aggregate build_group(int8, integer) (
430 -- test variadic polymorphic functions
432 create function myleast(variadic anyarray) returns anyelement as $$
433 select min($1[i]) from generate_subscripts($1,1) g(i)
434 $$ language sql immutable strict;
436 select myleast(10, 1, 20, 33);
437 select myleast(1.1, 0.22, 0.55);
438 select myleast('z'::text);
439 select myleast(); -- fail
441 -- test with variadic call parameter
442 select myleast(variadic array[1,2,3,4,-1]);
443 select myleast(variadic array[1.1, -5.5]);
445 --test with empty variadic call parameter
446 select myleast(variadic array[]::int[]);
448 -- an example with some ordinary arguments too
449 create function concat(text, variadic anyarray) returns text as $$
450 select array_to_string($2, $1);
451 $$ language sql immutable strict;
453 select concat('%', 1, 2, 3, 4, 5);
454 select concat('|', 'a'::text, 'b', 'c');
455 select concat('|', variadic array[1,2,33]);
456 select concat('|', variadic array[]::int[]);
458 drop function concat(text, anyarray);
460 -- mix variadic with anyelement
461 create function formarray(anyelement, variadic anyarray) returns anyarray as $$
462 select array_prepend($1, $2);
463 $$ language sql immutable strict;
465 select formarray(1,2,3,4,5);
466 select formarray(1.1, variadic array[1.2,55.5]);
467 select formarray(1.1, array[1.2,55.5]); -- fail without variadic
468 select formarray(1, 'x'::text); -- fail, type mismatch
469 select formarray(1, variadic array['x'::text]); -- fail, type mismatch
471 drop function formarray(anyelement, variadic anyarray);
473 -- test pg_typeof() function
474 select pg_typeof(null); -- unknown
475 select pg_typeof(0); -- integer
476 select pg_typeof(0.0); -- numeric
477 select pg_typeof(1+1 = 2); -- boolean
478 select pg_typeof('x'); -- unknown
479 select pg_typeof('' || ''); -- text
480 select pg_typeof(pg_typeof(0)); -- regtype
481 select pg_typeof(array[1.2,55.5]); -- numeric[]
482 select pg_typeof(myleast(10, 1, 20, 33)); -- polymorphic input
484 -- test functions with parameter defaults
485 -- test basic functionality
486 create function dfunc(a int = 1, int = 2) returns int as $$
492 select dfunc(10, 20);
494 drop function dfunc(); -- fail
495 drop function dfunc(int); -- fail
496 drop function dfunc(int, int); -- ok
498 -- fail, gap in arguments with defaults
499 create function dfunc(a int = 1, b int) returns int as $$
503 -- check implicit coercion
504 create function dfunc(a int DEFAULT 1.0, int DEFAULT '-1') returns int as $$
508 create function dfunc(a text DEFAULT 'Hello', b text DEFAULT 'World') returns text as $$
509 select $1 || ', ' || $2;
512 select dfunc(); -- fail; which dfunc should be called? int or text
513 select dfunc('Hi'); -- ok
514 select dfunc('Hi', 'City'); -- ok
515 select dfunc(0); -- ok
516 select dfunc(10, 20); -- ok
518 drop function dfunc(int, int);
519 drop function dfunc(text, text);
521 create function dfunc(int = 1, int = 2) returns int as $$
525 create function dfunc(int = 1, int = 2, int = 3, int = 4) returns int as $$
529 -- Now, dfunc(nargs = 2) and dfunc(nargs = 4) are ambiguous when called
530 -- with 0 or 1 arguments. For 2 arguments, a normall call of
531 -- dfunc(nargs = 2) takes place.
533 select dfunc(); -- fail
534 select dfunc(1); -- fail
535 select dfunc(1, 2); -- ok
536 select dfunc(1, 2, 3); -- ok
537 select dfunc(1, 2, 3, 4); -- ok
539 drop function dfunc(int, int);
540 drop function dfunc(int, int, int, int);
542 -- default values are not allowed for output parameters
543 create function dfunc(out int = 20) returns int as $$
547 -- polymorphic parameter test
548 create function dfunc(anyelement = 'World'::text) returns text as $$
549 select 'Hello, ' || $1::text;
554 select dfunc(to_date('20081215','YYYYMMDD'));
555 select dfunc('City'::text);
557 drop function dfunc(anyelement);
560 create function dfunc(int = null, int = null, int = null) returns int[] as $$
561 select array[$1, $2, $3];
566 select dfunc(1, 2, 3);
568 drop function dfunc(int, int, int);
570 -- The conflict detection algorithm doesn't consider the actual parameter
571 -- types. It detects any possible conflict for n arguments for some
572 -- function. This is unwanted behavior, but solving it needs a move of
573 -- coercion routines.
575 create function dfunc(int = 1, int = 2, int = 3) returns int as $$
579 create function dfunc(int = 1, int = 2) returns int as $$
583 -- for n = 1 dfunc(narg=2) and dfunc(narg=3) are ambiguous
584 select dfunc(1); -- fail
586 create function dfunc(text) returns text as $$
590 -- Will fail, it detects ambiguity between dfunc(int, int, int) and
591 -- dfunc(int, int), but dfunc(text) isn't in conflict with either.
594 drop function dfunc(int, int, int);
595 drop function dfunc(int, int);
596 drop function dfunc(text);