1-- 2-- Tests for polymorphic SQL functions and aggregates based on them. 3-- Tests for other features related to function-calling have snuck in, too. 4-- 5 6create function polyf(x anyelement) returns anyelement as $$ 7 select x + 1 8$$ language sql; 9 10select polyf(42) as int, polyf(4.5) as num; 11select polyf(point(3,4)); -- fail for lack of + operator 12 13drop function polyf(x anyelement); 14 15create function polyf(x anyelement) returns anyarray as $$ 16 select array[x + 1, x + 2] 17$$ language sql; 18 19select polyf(42) as int, polyf(4.5) as num; 20 21drop function polyf(x anyelement); 22 23create function polyf(x anyarray) returns anyelement as $$ 24 select x[1] 25$$ language sql; 26 27select polyf(array[2,4]) as int, polyf(array[4.5, 7.7]) as num; 28 29select polyf(stavalues1) from pg_statistic; -- fail, can't infer element type 30 31drop function polyf(x anyarray); 32 33create function polyf(x anyarray) returns anyarray as $$ 34 select x 35$$ language sql; 36 37select polyf(array[2,4]) as int, polyf(array[4.5, 7.7]) as num; 38 39select polyf(stavalues1) from pg_statistic; -- fail, can't infer element type 40 41drop function polyf(x anyarray); 42 43-- fail, can't infer type: 44create function polyf(x anyelement) returns anyrange as $$ 45 select array[x + 1, x + 2] 46$$ language sql; 47 48create function polyf(x anyrange) returns anyarray as $$ 49 select array[lower(x), upper(x)] 50$$ language sql; 51 52select polyf(int4range(42, 49)) as int, polyf(float8range(4.5, 7.8)) as num; 53 54drop function polyf(x anyrange); 55 56create function polyf(x anycompatible, y anycompatible) returns anycompatiblearray as $$ 57 select array[x, y] 58$$ language sql; 59 60select polyf(2, 4) as int, polyf(2, 4.5) as num; 61 62drop function polyf(x anycompatible, y anycompatible); 63 64create function polyf(x anycompatiblerange, y anycompatible, z anycompatible) returns anycompatiblearray as $$ 65 select array[lower(x), upper(x), y, z] 66$$ language sql; 67 68select polyf(int4range(42, 49), 11, 2::smallint) as int, polyf(float8range(4.5, 7.8), 7.8, 11::real) as num; 69 70select polyf(int4range(42, 49), 11, 4.5) as fail; -- range type doesn't fit 71 72drop function polyf(x anycompatiblerange, y anycompatible, z anycompatible); 73 74create function polyf(x anycompatiblemultirange, y anycompatible, z anycompatible) returns anycompatiblearray as $$ 75 select array[lower(x), upper(x), y, z] 76$$ language sql; 77 78select polyf(multirange(int4range(42, 49)), 11, 2::smallint) as int, polyf(multirange(float8range(4.5, 7.8)), 7.8, 11::real) as num; 79 80select polyf(multirange(int4range(42, 49)), 11, 4.5) as fail; -- range type doesn't fit 81 82drop function polyf(x anycompatiblemultirange, y anycompatible, z anycompatible); 83 84-- fail, can't infer type: 85create function polyf(x anycompatible) returns anycompatiblerange as $$ 86 select array[x + 1, x + 2] 87$$ language sql; 88 89create function polyf(x anycompatiblerange, y anycompatiblearray) returns anycompatiblerange as $$ 90 select x 91$$ language sql; 92 93select polyf(int4range(42, 49), array[11]) as int, polyf(float8range(4.5, 7.8), array[7]) as num; 94 95drop function polyf(x anycompatiblerange, y anycompatiblearray); 96 97-- fail, can't infer type: 98create function polyf(x anycompatible) returns anycompatiblemultirange as $$ 99 select array[x + 1, x + 2] 100$$ language sql; 101 102create function polyf(x anycompatiblemultirange, y anycompatiblearray) returns anycompatiblemultirange as $$ 103 select x 104$$ language sql; 105 106select polyf(multirange(int4range(42, 49)), array[11]) as int, polyf(multirange(float8range(4.5, 7.8)), array[7]) as num; 107 108drop function polyf(x anycompatiblemultirange, y anycompatiblearray); 109 110create function polyf(a anyelement, b anyarray, 111 c anycompatible, d anycompatible, 112 OUT x anyarray, OUT y anycompatiblearray) 113as $$ 114 select a || b, array[c, d] 115$$ language sql; 116 117select x, pg_typeof(x), y, pg_typeof(y) 118 from polyf(11, array[1, 2], 42, 34.5); 119select x, pg_typeof(x), y, pg_typeof(y) 120 from polyf(11, array[1, 2], point(1,2), point(3,4)); 121select x, pg_typeof(x), y, pg_typeof(y) 122 from polyf(11, '{1,2}', point(1,2), '(3,4)'); 123select x, pg_typeof(x), y, pg_typeof(y) 124 from polyf(11, array[1, 2.2], 42, 34.5); -- fail 125 126drop function polyf(a anyelement, b anyarray, 127 c anycompatible, d anycompatible); 128 129create function polyf(anyrange) returns anymultirange 130as 'select multirange($1);' language sql; 131 132select polyf(int4range(1,10)); 133select polyf(null); 134 135drop function polyf(anyrange); 136 137create function polyf(anymultirange) returns anyelement 138as 'select lower($1);' language sql; 139 140select polyf(int4multirange(int4range(1,10), int4range(20,30))); 141select polyf(null); 142 143drop function polyf(anymultirange); 144 145create function polyf(anycompatiblerange) returns anycompatiblemultirange 146as 'select multirange($1);' language sql; 147 148select polyf(int4range(1,10)); 149select polyf(null); 150 151drop function polyf(anycompatiblerange); 152 153create function polyf(anymultirange) returns anyrange 154as 'select range_merge($1);' language sql; 155 156select polyf(int4multirange(int4range(1,10), int4range(20,30))); 157select polyf(null); 158 159drop function polyf(anymultirange); 160 161create function polyf(anycompatiblemultirange) returns anycompatiblerange 162as 'select range_merge($1);' language sql; 163 164select polyf(int4multirange(int4range(1,10), int4range(20,30))); 165select polyf(null); 166 167drop function polyf(anycompatiblemultirange); 168 169create function polyf(anycompatiblemultirange) returns anycompatible 170as 'select lower($1);' language sql; 171 172select polyf(int4multirange(int4range(1,10), int4range(20,30))); 173select polyf(null); 174 175drop function polyf(anycompatiblemultirange); 176 177 178-- 179-- Polymorphic aggregate tests 180-- 181-- Legend: 182----------- 183-- A = type is ANY 184-- P = type is polymorphic 185-- N = type is non-polymorphic 186-- B = aggregate base type 187-- S = aggregate state type 188-- R = aggregate return type 189-- 1 = arg1 of a function 190-- 2 = arg2 of a function 191-- ag = aggregate 192-- tf = trans (state) function 193-- ff = final function 194-- rt = return type of a function 195-- -> = implies 196-- => = allowed 197-- !> = not allowed 198-- E = exists 199-- NE = not-exists 200-- 201-- Possible states: 202-- ---------------- 203-- B = (A || P || N) 204-- when (B = A) -> (tf2 = NE) 205-- S = (P || N) 206-- ff = (E || NE) 207-- tf1 = (P || N) 208-- tf2 = (NE || P || N) 209-- R = (P || N) 210 211-- create functions for use as tf and ff with the needed combinations of 212-- argument polymorphism, but within the constraints of valid aggregate 213-- functions, i.e. tf arg1 and tf return type must match 214 215-- polymorphic single arg transfn 216CREATE FUNCTION stfp(anyarray) RETURNS anyarray AS 217'select $1' LANGUAGE SQL; 218-- non-polymorphic single arg transfn 219CREATE FUNCTION stfnp(int[]) RETURNS int[] AS 220'select $1' LANGUAGE SQL; 221 222-- dual polymorphic transfn 223CREATE FUNCTION tfp(anyarray,anyelement) RETURNS anyarray AS 224'select $1 || $2' LANGUAGE SQL; 225-- dual non-polymorphic transfn 226CREATE FUNCTION tfnp(int[],int) RETURNS int[] AS 227'select $1 || $2' LANGUAGE SQL; 228 229-- arg1 only polymorphic transfn 230CREATE FUNCTION tf1p(anyarray,int) RETURNS anyarray AS 231'select $1' LANGUAGE SQL; 232-- arg2 only polymorphic transfn 233CREATE FUNCTION tf2p(int[],anyelement) RETURNS int[] AS 234'select $1' LANGUAGE SQL; 235 236-- multi-arg polymorphic 237CREATE FUNCTION sum3(anyelement,anyelement,anyelement) returns anyelement AS 238'select $1+$2+$3' language sql strict; 239 240-- finalfn polymorphic 241CREATE FUNCTION ffp(anyarray) RETURNS anyarray AS 242'select $1' LANGUAGE SQL; 243-- finalfn non-polymorphic 244CREATE FUNCTION ffnp(int[]) returns int[] as 245'select $1' LANGUAGE SQL; 246 247-- Try to cover all the possible states: 248-- 249-- Note: in Cases 1 & 2, we are trying to return P. Therefore, if the transfn 250-- is stfnp, tfnp, or tf2p, we must use ffp as finalfn, because stfnp, tfnp, 251-- and tf2p do not return P. Conversely, in Cases 3 & 4, we are trying to 252-- return N. Therefore, if the transfn is stfp, tfp, or tf1p, we must use ffnp 253-- as finalfn, because stfp, tfp, and tf1p do not return N. 254-- 255-- Case1 (R = P) && (B = A) 256-- ------------------------ 257-- S tf1 258-- ------- 259-- N N 260-- should CREATE 261CREATE AGGREGATE myaggp01a(*) (SFUNC = stfnp, STYPE = int4[], 262 FINALFUNC = ffp, INITCOND = '{}'); 263 264-- P N 265-- should ERROR: stfnp(anyarray) not matched by stfnp(int[]) 266CREATE AGGREGATE myaggp02a(*) (SFUNC = stfnp, STYPE = anyarray, 267 FINALFUNC = ffp, INITCOND = '{}'); 268 269-- N P 270-- should CREATE 271CREATE AGGREGATE myaggp03a(*) (SFUNC = stfp, STYPE = int4[], 272 FINALFUNC = ffp, INITCOND = '{}'); 273CREATE AGGREGATE myaggp03b(*) (SFUNC = stfp, STYPE = int4[], 274 INITCOND = '{}'); 275 276-- P P 277-- should ERROR: we have no way to resolve S 278CREATE AGGREGATE myaggp04a(*) (SFUNC = stfp, STYPE = anyarray, 279 FINALFUNC = ffp, INITCOND = '{}'); 280CREATE AGGREGATE myaggp04b(*) (SFUNC = stfp, STYPE = anyarray, 281 INITCOND = '{}'); 282 283 284-- Case2 (R = P) && ((B = P) || (B = N)) 285-- ------------------------------------- 286-- S tf1 B tf2 287-- ----------------------- 288-- N N N N 289-- should CREATE 290CREATE AGGREGATE myaggp05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[], 291 FINALFUNC = ffp, INITCOND = '{}'); 292 293-- N N N P 294-- should CREATE 295CREATE AGGREGATE myaggp06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[], 296 FINALFUNC = ffp, INITCOND = '{}'); 297 298-- N N P N 299-- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int) 300CREATE AGGREGATE myaggp07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[], 301 FINALFUNC = ffp, INITCOND = '{}'); 302 303-- N N P P 304-- should CREATE 305CREATE AGGREGATE myaggp08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[], 306 FINALFUNC = ffp, INITCOND = '{}'); 307 308-- N P N N 309-- should CREATE 310CREATE AGGREGATE myaggp09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[], 311 FINALFUNC = ffp, INITCOND = '{}'); 312CREATE AGGREGATE myaggp09b(BASETYPE = int, SFUNC = tf1p, STYPE = int[], 313 INITCOND = '{}'); 314 315-- N P N P 316-- should CREATE 317CREATE AGGREGATE myaggp10a(BASETYPE = int, SFUNC = tfp, STYPE = int[], 318 FINALFUNC = ffp, INITCOND = '{}'); 319CREATE AGGREGATE myaggp10b(BASETYPE = int, SFUNC = tfp, STYPE = int[], 320 INITCOND = '{}'); 321 322-- N P P N 323-- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int) 324CREATE AGGREGATE myaggp11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[], 325 FINALFUNC = ffp, INITCOND = '{}'); 326CREATE AGGREGATE myaggp11b(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[], 327 INITCOND = '{}'); 328 329-- N P P P 330-- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement) 331CREATE AGGREGATE myaggp12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[], 332 FINALFUNC = ffp, INITCOND = '{}'); 333CREATE AGGREGATE myaggp12b(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[], 334 INITCOND = '{}'); 335 336-- P N N N 337-- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int) 338CREATE AGGREGATE myaggp13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, 339 FINALFUNC = ffp, INITCOND = '{}'); 340 341-- P N N P 342-- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement) 343CREATE AGGREGATE myaggp14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, 344 FINALFUNC = ffp, INITCOND = '{}'); 345 346-- P N P N 347-- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int) 348CREATE AGGREGATE myaggp15a(BASETYPE = anyelement, SFUNC = tfnp, 349 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); 350 351-- P N P P 352-- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement) 353CREATE AGGREGATE myaggp16a(BASETYPE = anyelement, SFUNC = tf2p, 354 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); 355 356-- P P N N 357-- should ERROR: we have no way to resolve S 358CREATE AGGREGATE myaggp17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, 359 FINALFUNC = ffp, INITCOND = '{}'); 360CREATE AGGREGATE myaggp17b(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, 361 INITCOND = '{}'); 362 363-- P P N P 364-- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement) 365CREATE AGGREGATE myaggp18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, 366 FINALFUNC = ffp, INITCOND = '{}'); 367CREATE AGGREGATE myaggp18b(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, 368 INITCOND = '{}'); 369 370-- P P P N 371-- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int) 372CREATE AGGREGATE myaggp19a(BASETYPE = anyelement, SFUNC = tf1p, 373 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); 374CREATE AGGREGATE myaggp19b(BASETYPE = anyelement, SFUNC = tf1p, 375 STYPE = anyarray, INITCOND = '{}'); 376 377-- P P P P 378-- should CREATE 379CREATE AGGREGATE myaggp20a(BASETYPE = anyelement, SFUNC = tfp, 380 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); 381CREATE AGGREGATE myaggp20b(BASETYPE = anyelement, SFUNC = tfp, 382 STYPE = anyarray, INITCOND = '{}'); 383 384-- Case3 (R = N) && (B = A) 385-- ------------------------ 386-- S tf1 387-- ------- 388-- N N 389-- should CREATE 390CREATE AGGREGATE myaggn01a(*) (SFUNC = stfnp, STYPE = int4[], 391 FINALFUNC = ffnp, INITCOND = '{}'); 392CREATE AGGREGATE myaggn01b(*) (SFUNC = stfnp, STYPE = int4[], 393 INITCOND = '{}'); 394 395-- P N 396-- should ERROR: stfnp(anyarray) not matched by stfnp(int[]) 397CREATE AGGREGATE myaggn02a(*) (SFUNC = stfnp, STYPE = anyarray, 398 FINALFUNC = ffnp, INITCOND = '{}'); 399CREATE AGGREGATE myaggn02b(*) (SFUNC = stfnp, STYPE = anyarray, 400 INITCOND = '{}'); 401 402-- N P 403-- should CREATE 404CREATE AGGREGATE myaggn03a(*) (SFUNC = stfp, STYPE = int4[], 405 FINALFUNC = ffnp, INITCOND = '{}'); 406 407-- P P 408-- should ERROR: ffnp(anyarray) not matched by ffnp(int[]) 409CREATE AGGREGATE myaggn04a(*) (SFUNC = stfp, STYPE = anyarray, 410 FINALFUNC = ffnp, INITCOND = '{}'); 411 412 413-- Case4 (R = N) && ((B = P) || (B = N)) 414-- ------------------------------------- 415-- S tf1 B tf2 416-- ----------------------- 417-- N N N N 418-- should CREATE 419CREATE AGGREGATE myaggn05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[], 420 FINALFUNC = ffnp, INITCOND = '{}'); 421CREATE AGGREGATE myaggn05b(BASETYPE = int, SFUNC = tfnp, STYPE = int[], 422 INITCOND = '{}'); 423 424-- N N N P 425-- should CREATE 426CREATE AGGREGATE myaggn06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[], 427 FINALFUNC = ffnp, INITCOND = '{}'); 428CREATE AGGREGATE myaggn06b(BASETYPE = int, SFUNC = tf2p, STYPE = int[], 429 INITCOND = '{}'); 430 431-- N N P N 432-- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int) 433CREATE AGGREGATE myaggn07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[], 434 FINALFUNC = ffnp, INITCOND = '{}'); 435CREATE AGGREGATE myaggn07b(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[], 436 INITCOND = '{}'); 437 438-- N N P P 439-- should CREATE 440CREATE AGGREGATE myaggn08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[], 441 FINALFUNC = ffnp, INITCOND = '{}'); 442CREATE AGGREGATE myaggn08b(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[], 443 INITCOND = '{}'); 444 445-- N P N N 446-- should CREATE 447CREATE AGGREGATE myaggn09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[], 448 FINALFUNC = ffnp, INITCOND = '{}'); 449 450-- N P N P 451-- should CREATE 452CREATE AGGREGATE myaggn10a(BASETYPE = int, SFUNC = tfp, STYPE = int[], 453 FINALFUNC = ffnp, INITCOND = '{}'); 454 455-- N P P N 456-- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int) 457CREATE AGGREGATE myaggn11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[], 458 FINALFUNC = ffnp, INITCOND = '{}'); 459 460-- N P P P 461-- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement) 462CREATE AGGREGATE myaggn12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[], 463 FINALFUNC = ffnp, INITCOND = '{}'); 464 465-- P N N N 466-- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int) 467CREATE AGGREGATE myaggn13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, 468 FINALFUNC = ffnp, INITCOND = '{}'); 469CREATE AGGREGATE myaggn13b(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, 470 INITCOND = '{}'); 471 472-- P N N P 473-- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement) 474CREATE AGGREGATE myaggn14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, 475 FINALFUNC = ffnp, INITCOND = '{}'); 476CREATE AGGREGATE myaggn14b(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, 477 INITCOND = '{}'); 478 479-- P N P N 480-- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int) 481CREATE AGGREGATE myaggn15a(BASETYPE = anyelement, SFUNC = tfnp, 482 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}'); 483CREATE AGGREGATE myaggn15b(BASETYPE = anyelement, SFUNC = tfnp, 484 STYPE = anyarray, INITCOND = '{}'); 485 486-- P N P P 487-- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement) 488CREATE AGGREGATE myaggn16a(BASETYPE = anyelement, SFUNC = tf2p, 489 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}'); 490CREATE AGGREGATE myaggn16b(BASETYPE = anyelement, SFUNC = tf2p, 491 STYPE = anyarray, INITCOND = '{}'); 492 493-- P P N N 494-- should ERROR: ffnp(anyarray) not matched by ffnp(int[]) 495CREATE AGGREGATE myaggn17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, 496 FINALFUNC = ffnp, INITCOND = '{}'); 497 498-- P P N P 499-- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement) 500CREATE AGGREGATE myaggn18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, 501 FINALFUNC = ffnp, INITCOND = '{}'); 502 503-- P P P N 504-- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int) 505CREATE AGGREGATE myaggn19a(BASETYPE = anyelement, SFUNC = tf1p, 506 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}'); 507 508-- P P P P 509-- should ERROR: ffnp(anyarray) not matched by ffnp(int[]) 510CREATE AGGREGATE myaggn20a(BASETYPE = anyelement, SFUNC = tfp, 511 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}'); 512 513-- multi-arg polymorphic 514CREATE AGGREGATE mysum2(anyelement,anyelement) (SFUNC = sum3, 515 STYPE = anyelement, INITCOND = '0'); 516 517-- create test data for polymorphic aggregates 518create temp table t(f1 int, f2 int[], f3 text); 519insert into t values(1,array[1],'a'); 520insert into t values(1,array[11],'b'); 521insert into t values(1,array[111],'c'); 522insert into t values(2,array[2],'a'); 523insert into t values(2,array[22],'b'); 524insert into t values(2,array[222],'c'); 525insert into t values(3,array[3],'a'); 526insert into t values(3,array[3],'b'); 527 528-- test the successfully created polymorphic aggregates 529select f3, myaggp01a(*) from t group by f3 order by f3; 530select f3, myaggp03a(*) from t group by f3 order by f3; 531select f3, myaggp03b(*) from t group by f3 order by f3; 532select f3, myaggp05a(f1) from t group by f3 order by f3; 533select f3, myaggp06a(f1) from t group by f3 order by f3; 534select f3, myaggp08a(f1) from t group by f3 order by f3; 535select f3, myaggp09a(f1) from t group by f3 order by f3; 536select f3, myaggp09b(f1) from t group by f3 order by f3; 537select f3, myaggp10a(f1) from t group by f3 order by f3; 538select f3, myaggp10b(f1) from t group by f3 order by f3; 539select f3, myaggp20a(f1) from t group by f3 order by f3; 540select f3, myaggp20b(f1) from t group by f3 order by f3; 541select f3, myaggn01a(*) from t group by f3 order by f3; 542select f3, myaggn01b(*) from t group by f3 order by f3; 543select f3, myaggn03a(*) from t group by f3 order by f3; 544select f3, myaggn05a(f1) from t group by f3 order by f3; 545select f3, myaggn05b(f1) from t group by f3 order by f3; 546select f3, myaggn06a(f1) from t group by f3 order by f3; 547select f3, myaggn06b(f1) from t group by f3 order by f3; 548select f3, myaggn08a(f1) from t group by f3 order by f3; 549select f3, myaggn08b(f1) from t group by f3 order by f3; 550select f3, myaggn09a(f1) from t group by f3 order by f3; 551select f3, myaggn10a(f1) from t group by f3 order by f3; 552select mysum2(f1, f1 + 1) from t; 553 554-- test inlining of polymorphic SQL functions 555create function bleat(int) returns int as $$ 556begin 557 raise notice 'bleat %', $1; 558 return $1; 559end$$ language plpgsql; 560 561create function sql_if(bool, anyelement, anyelement) returns anyelement as $$ 562select case when $1 then $2 else $3 end $$ language sql; 563 564-- Note this would fail with integer overflow, never mind wrong bleat() output, 565-- if the CASE expression were not successfully inlined 566select f1, sql_if(f1 > 0, bleat(f1), bleat(f1 + 1)) from int4_tbl; 567 568select q2, sql_if(q2 > 0, q2, q2 + 1) from int8_tbl; 569 570-- another sort of polymorphic aggregate 571 572CREATE AGGREGATE array_larger_accum (anyarray) 573( 574 sfunc = array_larger, 575 stype = anyarray, 576 initcond = '{}' 577); 578 579SELECT array_larger_accum(i) 580FROM (VALUES (ARRAY[1,2]), (ARRAY[3,4])) as t(i); 581 582SELECT array_larger_accum(i) 583FROM (VALUES (ARRAY[row(1,2),row(3,4)]), (ARRAY[row(5,6),row(7,8)])) as t(i); 584 585-- another kind of polymorphic aggregate 586 587create function add_group(grp anyarray, ad anyelement, size integer) 588 returns anyarray 589 as $$ 590begin 591 if grp is null then 592 return array[ad]; 593 end if; 594 if array_upper(grp, 1) < size then 595 return grp || ad; 596 end if; 597 return grp; 598end; 599$$ 600 language plpgsql immutable; 601 602create aggregate build_group(anyelement, integer) ( 603 SFUNC = add_group, 604 STYPE = anyarray 605); 606 607select build_group(q1,3) from int8_tbl; 608 609-- this should fail because stype isn't compatible with arg 610create aggregate build_group(int8, integer) ( 611 SFUNC = add_group, 612 STYPE = int2[] 613); 614 615-- but we can make a non-poly agg from a poly sfunc if types are OK 616create aggregate build_group(int8, integer) ( 617 SFUNC = add_group, 618 STYPE = int8[] 619); 620 621-- check proper resolution of data types for polymorphic transfn/finalfn 622 623create function first_el_transfn(anyarray, anyelement) returns anyarray as 624'select $1 || $2' language sql immutable; 625 626create function first_el(anyarray) returns anyelement as 627'select $1[1]' language sql strict immutable; 628 629create aggregate first_el_agg_f8(float8) ( 630 SFUNC = array_append, 631 STYPE = float8[], 632 FINALFUNC = first_el 633); 634 635create aggregate first_el_agg_any(anyelement) ( 636 SFUNC = first_el_transfn, 637 STYPE = anyarray, 638 FINALFUNC = first_el 639); 640 641select first_el_agg_f8(x::float8) from generate_series(1,10) x; 642select first_el_agg_any(x) from generate_series(1,10) x; 643select first_el_agg_f8(x::float8) over(order by x) from generate_series(1,10) x; 644select first_el_agg_any(x) over(order by x) from generate_series(1,10) x; 645 646-- check that we can apply functions taking ANYARRAY to pg_stats 647select distinct array_ndims(histogram_bounds) from pg_stats 648where histogram_bounds is not null; 649 650-- such functions must protect themselves if varying element type isn't OK 651-- (WHERE clause here is to avoid possibly getting a collation error instead) 652select max(histogram_bounds) from pg_stats where tablename = 'pg_am'; 653 654-- another corner case is the input functions for polymorphic pseudotypes 655select array_in('{1,2,3}','int4'::regtype,-1); -- this has historically worked 656select * from array_in('{1,2,3}','int4'::regtype,-1); -- this not 657select anyrange_in('[10,20)','int4range'::regtype,-1); 658 659-- test variadic polymorphic functions 660 661create function myleast(variadic anyarray) returns anyelement as $$ 662 select min($1[i]) from generate_subscripts($1,1) g(i) 663$$ language sql immutable strict; 664 665select myleast(10, 1, 20, 33); 666select myleast(1.1, 0.22, 0.55); 667select myleast('z'::text); 668select myleast(); -- fail 669 670-- test with variadic call parameter 671select myleast(variadic array[1,2,3,4,-1]); 672select myleast(variadic array[1.1, -5.5]); 673 674--test with empty variadic call parameter 675select myleast(variadic array[]::int[]); 676 677-- an example with some ordinary arguments too 678create function concat(text, variadic anyarray) returns text as $$ 679 select array_to_string($2, $1); 680$$ language sql immutable strict; 681 682select concat('%', 1, 2, 3, 4, 5); 683select concat('|', 'a'::text, 'b', 'c'); 684select concat('|', variadic array[1,2,33]); 685select concat('|', variadic array[]::int[]); 686 687drop function concat(text, anyarray); 688 689-- mix variadic with anyelement 690create function formarray(anyelement, variadic anyarray) returns anyarray as $$ 691 select array_prepend($1, $2); 692$$ language sql immutable strict; 693 694select formarray(1,2,3,4,5); 695select formarray(1.1, variadic array[1.2,55.5]); 696select formarray(1.1, array[1.2,55.5]); -- fail without variadic 697select formarray(1, 'x'::text); -- fail, type mismatch 698select formarray(1, variadic array['x'::text]); -- fail, type mismatch 699 700drop function formarray(anyelement, variadic anyarray); 701 702-- test pg_typeof() function 703select pg_typeof(null); -- unknown 704select pg_typeof(0); -- integer 705select pg_typeof(0.0); -- numeric 706select pg_typeof(1+1 = 2); -- boolean 707select pg_typeof('x'); -- unknown 708select pg_typeof('' || ''); -- text 709select pg_typeof(pg_typeof(0)); -- regtype 710select pg_typeof(array[1.2,55.5]); -- numeric[] 711select pg_typeof(myleast(10, 1, 20, 33)); -- polymorphic input 712 713-- test functions with default parameters 714 715-- test basic functionality 716create function dfunc(a int = 1, int = 2) returns int as $$ 717 select $1 + $2; 718$$ language sql; 719 720select dfunc(); 721select dfunc(10); 722select dfunc(10, 20); 723select dfunc(10, 20, 30); -- fail 724 725drop function dfunc(); -- fail 726drop function dfunc(int); -- fail 727drop function dfunc(int, int); -- ok 728 729-- fail: defaults must be at end of argument list 730create function dfunc(a int = 1, b int) returns int as $$ 731 select $1 + $2; 732$$ language sql; 733 734-- however, this should work: 735create function dfunc(a int = 1, out sum int, b int = 2) as $$ 736 select $1 + $2; 737$$ language sql; 738 739select dfunc(); 740 741-- verify it lists properly 742\df dfunc 743 744drop function dfunc(int, int); 745 746-- check implicit coercion 747create function dfunc(a int DEFAULT 1.0, int DEFAULT '-1') returns int as $$ 748 select $1 + $2; 749$$ language sql; 750select dfunc(); 751 752create function dfunc(a text DEFAULT 'Hello', b text DEFAULT 'World') returns text as $$ 753 select $1 || ', ' || $2; 754$$ language sql; 755 756select dfunc(); -- fail: which dfunc should be called? int or text 757select dfunc('Hi'); -- ok 758select dfunc('Hi', 'City'); -- ok 759select dfunc(0); -- ok 760select dfunc(10, 20); -- ok 761 762drop function dfunc(int, int); 763drop function dfunc(text, text); 764 765create function dfunc(int = 1, int = 2) returns int as $$ 766 select 2; 767$$ language sql; 768 769create function dfunc(int = 1, int = 2, int = 3, int = 4) returns int as $$ 770 select 4; 771$$ language sql; 772 773-- Now, dfunc(nargs = 2) and dfunc(nargs = 4) are ambiguous when called 774-- with 0 to 2 arguments. 775 776select dfunc(); -- fail 777select dfunc(1); -- fail 778select dfunc(1, 2); -- fail 779select dfunc(1, 2, 3); -- ok 780select dfunc(1, 2, 3, 4); -- ok 781 782drop function dfunc(int, int); 783drop function dfunc(int, int, int, int); 784 785-- default values are not allowed for output parameters 786create function dfunc(out int = 20) returns int as $$ 787 select 1; 788$$ language sql; 789 790-- polymorphic parameter test 791create function dfunc(anyelement = 'World'::text) returns text as $$ 792 select 'Hello, ' || $1::text; 793$$ language sql; 794 795select dfunc(); 796select dfunc(0); 797select dfunc(to_date('20081215','YYYYMMDD')); 798select dfunc('City'::text); 799 800drop function dfunc(anyelement); 801 802-- check defaults for variadics 803 804create function dfunc(a variadic int[]) returns int as 805$$ select array_upper($1, 1) $$ language sql; 806 807select dfunc(); -- fail 808select dfunc(10); 809select dfunc(10,20); 810 811create or replace function dfunc(a variadic int[] default array[]::int[]) returns int as 812$$ select array_upper($1, 1) $$ language sql; 813 814select dfunc(); -- now ok 815select dfunc(10); 816select dfunc(10,20); 817 818-- can't remove the default once it exists 819create or replace function dfunc(a variadic int[]) returns int as 820$$ select array_upper($1, 1) $$ language sql; 821 822\df dfunc 823 824drop function dfunc(a variadic int[]); 825 826-- Ambiguity should be reported only if there's not a better match available 827 828create function dfunc(int = 1, int = 2, int = 3) returns int as $$ 829 select 3; 830$$ language sql; 831 832create function dfunc(int = 1, int = 2) returns int as $$ 833 select 2; 834$$ language sql; 835 836create function dfunc(text) returns text as $$ 837 select $1; 838$$ language sql; 839 840-- dfunc(narg=2) and dfunc(narg=3) are ambiguous 841select dfunc(1); -- fail 842 843-- but this works since the ambiguous functions aren't preferred anyway 844select dfunc('Hi'); 845 846drop function dfunc(int, int, int); 847drop function dfunc(int, int); 848drop function dfunc(text); 849 850-- 851-- Tests for named- and mixed-notation function calling 852-- 853 854create function dfunc(a int, b int, c int = 0, d int = 0) 855 returns table (a int, b int, c int, d int) as $$ 856 select $1, $2, $3, $4; 857$$ language sql; 858 859select (dfunc(10,20,30)).*; 860select (dfunc(a := 10, b := 20, c := 30)).*; 861select * from dfunc(a := 10, b := 20); 862select * from dfunc(b := 10, a := 20); 863select * from dfunc(0); -- fail 864select * from dfunc(1,2); 865select * from dfunc(1,2,c := 3); 866select * from dfunc(1,2,d := 3); 867 868select * from dfunc(x := 20, b := 10, x := 30); -- fail, duplicate name 869select * from dfunc(10, b := 20, 30); -- fail, named args must be last 870select * from dfunc(x := 10, b := 20, c := 30); -- fail, unknown param 871select * from dfunc(10, 10, a := 20); -- fail, a overlaps positional parameter 872select * from dfunc(1,c := 2,d := 3); -- fail, no value for b 873 874drop function dfunc(int, int, int, int); 875 876-- test with different parameter types 877create function dfunc(a varchar, b numeric, c date = current_date) 878 returns table (a varchar, b numeric, c date) as $$ 879 select $1, $2, $3; 880$$ language sql; 881 882select (dfunc('Hello World', 20, '2009-07-25'::date)).*; 883select * from dfunc('Hello World', 20, '2009-07-25'::date); 884select * from dfunc(c := '2009-07-25'::date, a := 'Hello World', b := 20); 885select * from dfunc('Hello World', b := 20, c := '2009-07-25'::date); 886select * from dfunc('Hello World', c := '2009-07-25'::date, b := 20); 887select * from dfunc('Hello World', c := 20, b := '2009-07-25'::date); -- fail 888 889drop function dfunc(varchar, numeric, date); 890 891-- test out parameters with named params 892create function dfunc(a varchar = 'def a', out _a varchar, c numeric = NULL, out _c numeric) 893returns record as $$ 894 select $1, $2; 895$$ language sql; 896 897select (dfunc()).*; 898select * from dfunc(); 899select * from dfunc('Hello', 100); 900select * from dfunc(a := 'Hello', c := 100); 901select * from dfunc(c := 100, a := 'Hello'); 902select * from dfunc('Hello'); 903select * from dfunc('Hello', c := 100); 904select * from dfunc(c := 100); 905 906-- fail, can no longer change an input parameter's name 907create or replace function dfunc(a varchar = 'def a', out _a varchar, x numeric = NULL, out _c numeric) 908returns record as $$ 909 select $1, $2; 910$$ language sql; 911 912create or replace function dfunc(a varchar = 'def a', out _a varchar, numeric = NULL, out _c numeric) 913returns record as $$ 914 select $1, $2; 915$$ language sql; 916 917drop function dfunc(varchar, numeric); 918 919--fail, named parameters are not unique 920create function testpolym(a int, a int) returns int as $$ select 1;$$ language sql; 921create function testpolym(int, out a int, out a int) returns int as $$ select 1;$$ language sql; 922create function testpolym(out a int, inout a int) returns int as $$ select 1;$$ language sql; 923create function testpolym(a int, inout a int) returns int as $$ select 1;$$ language sql; 924 925-- valid 926create function testpolym(a int, out a int) returns int as $$ select $1;$$ language sql; 927select testpolym(37); 928drop function testpolym(int); 929create function testpolym(a int) returns table(a int) as $$ select $1;$$ language sql; 930select * from testpolym(37); 931drop function testpolym(int); 932 933-- test polymorphic params and defaults 934create function dfunc(a anyelement, b anyelement = null, flag bool = true) 935returns anyelement as $$ 936 select case when $3 then $1 else $2 end; 937$$ language sql; 938 939select dfunc(1,2); 940select dfunc('a'::text, 'b'); -- positional notation with default 941 942select dfunc(a := 1, b := 2); 943select dfunc(a := 'a'::text, b := 'b'); 944select dfunc(a := 'a'::text, b := 'b', flag := false); -- named notation 945 946select dfunc(b := 'b'::text, a := 'a'); -- named notation with default 947select dfunc(a := 'a'::text, flag := true); -- named notation with default 948select dfunc(a := 'a'::text, flag := false); -- named notation with default 949select dfunc(b := 'b'::text, a := 'a', flag := true); -- named notation 950 951select dfunc('a'::text, 'b', false); -- full positional notation 952select dfunc('a'::text, 'b', flag := false); -- mixed notation 953select dfunc('a'::text, 'b', true); -- full positional notation 954select dfunc('a'::text, 'b', flag := true); -- mixed notation 955 956-- ansi/sql syntax 957select dfunc(a => 1, b => 2); 958select dfunc(a => 'a'::text, b => 'b'); 959select dfunc(a => 'a'::text, b => 'b', flag => false); -- named notation 960 961select dfunc(b => 'b'::text, a => 'a'); -- named notation with default 962select dfunc(a => 'a'::text, flag => true); -- named notation with default 963select dfunc(a => 'a'::text, flag => false); -- named notation with default 964select dfunc(b => 'b'::text, a => 'a', flag => true); -- named notation 965 966select dfunc('a'::text, 'b', false); -- full positional notation 967select dfunc('a'::text, 'b', flag => false); -- mixed notation 968select dfunc('a'::text, 'b', true); -- full positional notation 969select dfunc('a'::text, 'b', flag => true); -- mixed notation 970 971-- this tests lexer edge cases around => 972select dfunc(a =>-1); 973select dfunc(a =>+1); 974select dfunc(a =>/**/1); 975select dfunc(a =>--comment to be removed by psql 976 1); 977-- need DO to protect the -- from psql 978do $$ 979 declare r integer; 980 begin 981 select dfunc(a=>-- comment 982 1) into r; 983 raise info 'r = %', r; 984 end; 985$$; 986 987-- check reverse-listing of named-arg calls 988CREATE VIEW dfview AS 989 SELECT q1, q2, 990 dfunc(q1,q2, flag := q1>q2) as c3, 991 dfunc(q1, flag := q1<q2, b := q2) as c4 992 FROM int8_tbl; 993 994select * from dfview; 995 996\d+ dfview 997 998drop view dfview; 999drop function dfunc(anyelement, anyelement, bool); 1000 1001-- 1002-- Tests for ANYCOMPATIBLE polymorphism family 1003-- 1004 1005create function anyctest(anycompatible, anycompatible) 1006returns anycompatible as $$ 1007 select greatest($1, $2) 1008$$ language sql; 1009 1010select x, pg_typeof(x) from anyctest(11, 12) x; 1011select x, pg_typeof(x) from anyctest(11, 12.3) x; 1012select x, pg_typeof(x) from anyctest(11, point(1,2)) x; -- fail 1013select x, pg_typeof(x) from anyctest('11', '12.3') x; -- defaults to text 1014 1015drop function anyctest(anycompatible, anycompatible); 1016 1017create function anyctest(anycompatible, anycompatible) 1018returns anycompatiblearray as $$ 1019 select array[$1, $2] 1020$$ language sql; 1021 1022select x, pg_typeof(x) from anyctest(11, 12) x; 1023select x, pg_typeof(x) from anyctest(11, 12.3) x; 1024select x, pg_typeof(x) from anyctest(11, array[1,2]) x; -- fail 1025 1026drop function anyctest(anycompatible, anycompatible); 1027 1028create function anyctest(anycompatible, anycompatiblearray) 1029returns anycompatiblearray as $$ 1030 select array[$1] || $2 1031$$ language sql; 1032 1033select x, pg_typeof(x) from anyctest(11, array[12]) x; 1034select x, pg_typeof(x) from anyctest(11, array[12.3]) x; 1035select x, pg_typeof(x) from anyctest(12.3, array[13]) x; 1036select x, pg_typeof(x) from anyctest(12.3, '{13,14.4}') x; 1037select x, pg_typeof(x) from anyctest(11, array[point(1,2)]) x; -- fail 1038select x, pg_typeof(x) from anyctest(11, 12) x; -- fail 1039 1040drop function anyctest(anycompatible, anycompatiblearray); 1041 1042create function anyctest(anycompatible, anycompatiblerange) 1043returns anycompatiblerange as $$ 1044 select $2 1045$$ language sql; 1046 1047select x, pg_typeof(x) from anyctest(11, int4range(4,7)) x; 1048select x, pg_typeof(x) from anyctest(11, numrange(4,7)) x; 1049select x, pg_typeof(x) from anyctest(11, 12) x; -- fail 1050select x, pg_typeof(x) from anyctest(11.2, int4range(4,7)) x; -- fail 1051select x, pg_typeof(x) from anyctest(11.2, '[4,7)') x; -- fail 1052 1053drop function anyctest(anycompatible, anycompatiblerange); 1054 1055create function anyctest(anycompatiblerange, anycompatiblerange) 1056returns anycompatible as $$ 1057 select lower($1) + upper($2) 1058$$ language sql; 1059 1060select x, pg_typeof(x) from anyctest(int4range(11,12), int4range(4,7)) x; 1061select x, pg_typeof(x) from anyctest(int4range(11,12), numrange(4,7)) x; -- fail 1062 1063drop function anyctest(anycompatiblerange, anycompatiblerange); 1064 1065-- fail, can't infer result type: 1066create function anyctest(anycompatible) 1067returns anycompatiblerange as $$ 1068 select $1 1069$$ language sql; 1070 1071create function anyctest(anycompatible, anycompatiblemultirange) 1072returns anycompatiblemultirange as $$ 1073 select $2 1074$$ language sql; 1075 1076select x, pg_typeof(x) from anyctest(11, multirange(int4range(4,7))) x; 1077select x, pg_typeof(x) from anyctest(11, multirange(numrange(4,7))) x; 1078select x, pg_typeof(x) from anyctest(11, 12) x; -- fail 1079select x, pg_typeof(x) from anyctest(11.2, multirange(int4range(4,7))) x; -- fail 1080select x, pg_typeof(x) from anyctest(11.2, '{[4,7)}') x; -- fail 1081 1082drop function anyctest(anycompatible, anycompatiblemultirange); 1083 1084create function anyctest(anycompatiblemultirange, anycompatiblemultirange) 1085returns anycompatible as $$ 1086 select lower($1) + upper($2) 1087$$ language sql; 1088 1089select x, pg_typeof(x) from anyctest(multirange(int4range(11,12)), multirange(int4range(4,7))) x; 1090select x, pg_typeof(x) from anyctest(multirange(int4range(11,12)), multirange(numrange(4,7))) x; -- fail 1091 1092drop function anyctest(anycompatiblemultirange, anycompatiblemultirange); 1093 1094-- fail, can't infer result type: 1095create function anyctest(anycompatible) 1096returns anycompatiblemultirange as $$ 1097 select $1 1098$$ language sql; 1099 1100create function anyctest(anycompatiblenonarray, anycompatiblenonarray) 1101returns anycompatiblearray as $$ 1102 select array[$1, $2] 1103$$ language sql; 1104 1105select x, pg_typeof(x) from anyctest(11, 12) x; 1106select x, pg_typeof(x) from anyctest(11, 12.3) x; 1107select x, pg_typeof(x) from anyctest(array[11], array[1,2]) x; -- fail 1108 1109drop function anyctest(anycompatiblenonarray, anycompatiblenonarray); 1110 1111create function anyctest(a anyelement, b anyarray, 1112 c anycompatible, d anycompatible) 1113returns anycompatiblearray as $$ 1114 select array[c, d] 1115$$ language sql; 1116 1117select x, pg_typeof(x) from anyctest(11, array[1, 2], 42, 34.5) x; 1118select x, pg_typeof(x) from anyctest(11, array[1, 2], point(1,2), point(3,4)) x; 1119select x, pg_typeof(x) from anyctest(11, '{1,2}', point(1,2), '(3,4)') x; 1120select x, pg_typeof(x) from anyctest(11, array[1, 2.2], 42, 34.5) x; -- fail 1121 1122drop function anyctest(a anyelement, b anyarray, 1123 c anycompatible, d anycompatible); 1124 1125create function anyctest(variadic anycompatiblearray) 1126returns anycompatiblearray as $$ 1127 select $1 1128$$ language sql; 1129 1130select x, pg_typeof(x) from anyctest(11, 12) x; 1131select x, pg_typeof(x) from anyctest(11, 12.2) x; 1132select x, pg_typeof(x) from anyctest(11, '12') x; 1133select x, pg_typeof(x) from anyctest(11, '12.2') x; -- fail 1134select x, pg_typeof(x) from anyctest(variadic array[11, 12]) x; 1135select x, pg_typeof(x) from anyctest(variadic array[11, 12.2]) x; 1136 1137drop function anyctest(variadic anycompatiblearray); 1138