1-- Currently this tests polymorphic aggregates and indirectly does some 2-- testing of polymorphic SQL functions. It ought to be extended. 3-- Tests for other features related to function-calling have snuck in, too. 4 5 6-- Legend: 7----------- 8-- A = type is ANY 9-- P = type is polymorphic 10-- N = type is non-polymorphic 11-- B = aggregate base type 12-- S = aggregate state type 13-- R = aggregate return type 14-- 1 = arg1 of a function 15-- 2 = arg2 of a function 16-- ag = aggregate 17-- tf = trans (state) function 18-- ff = final function 19-- rt = return type of a function 20-- -> = implies 21-- => = allowed 22-- !> = not allowed 23-- E = exists 24-- NE = not-exists 25-- 26-- Possible states: 27-- ---------------- 28-- B = (A || P || N) 29-- when (B = A) -> (tf2 = NE) 30-- S = (P || N) 31-- ff = (E || NE) 32-- tf1 = (P || N) 33-- tf2 = (NE || P || N) 34-- R = (P || N) 35 36-- create functions for use as tf and ff with the needed combinations of 37-- argument polymorphism, but within the constraints of valid aggregate 38-- functions, i.e. tf arg1 and tf return type must match 39 40-- polymorphic single arg transfn 41CREATE FUNCTION stfp(anyarray) RETURNS anyarray AS 42'select $1' LANGUAGE SQL; 43-- non-polymorphic single arg transfn 44CREATE FUNCTION stfnp(int[]) RETURNS int[] AS 45'select $1' LANGUAGE SQL; 46 47-- dual polymorphic transfn 48CREATE FUNCTION tfp(anyarray,anyelement) RETURNS anyarray AS 49'select $1 || $2' LANGUAGE SQL; 50-- dual non-polymorphic transfn 51CREATE FUNCTION tfnp(int[],int) RETURNS int[] AS 52'select $1 || $2' LANGUAGE SQL; 53 54-- arg1 only polymorphic transfn 55CREATE FUNCTION tf1p(anyarray,int) RETURNS anyarray AS 56'select $1' LANGUAGE SQL; 57-- arg2 only polymorphic transfn 58CREATE FUNCTION tf2p(int[],anyelement) RETURNS int[] AS 59'select $1' LANGUAGE SQL; 60 61-- multi-arg polymorphic 62CREATE FUNCTION sum3(anyelement,anyelement,anyelement) returns anyelement AS 63'select $1+$2+$3' language sql strict; 64 65-- finalfn polymorphic 66CREATE FUNCTION ffp(anyarray) RETURNS anyarray AS 67'select $1' LANGUAGE SQL; 68-- finalfn non-polymorphic 69CREATE FUNCTION ffnp(int[]) returns int[] as 70'select $1' LANGUAGE SQL; 71 72-- Try to cover all the possible states: 73-- 74-- Note: in Cases 1 & 2, we are trying to return P. Therefore, if the transfn 75-- is stfnp, tfnp, or tf2p, we must use ffp as finalfn, because stfnp, tfnp, 76-- and tf2p do not return P. Conversely, in Cases 3 & 4, we are trying to 77-- return N. Therefore, if the transfn is stfp, tfp, or tf1p, we must use ffnp 78-- as finalfn, because stfp, tfp, and tf1p do not return N. 79-- 80-- Case1 (R = P) && (B = A) 81-- ------------------------ 82-- S tf1 83-- ------- 84-- N N 85-- should CREATE 86CREATE AGGREGATE myaggp01a(*) (SFUNC = stfnp, STYPE = int4[], 87 FINALFUNC = ffp, INITCOND = '{}'); 88 89-- P N 90-- should ERROR: stfnp(anyarray) not matched by stfnp(int[]) 91CREATE AGGREGATE myaggp02a(*) (SFUNC = stfnp, STYPE = anyarray, 92 FINALFUNC = ffp, INITCOND = '{}'); 93 94-- N P 95-- should CREATE 96CREATE AGGREGATE myaggp03a(*) (SFUNC = stfp, STYPE = int4[], 97 FINALFUNC = ffp, INITCOND = '{}'); 98CREATE AGGREGATE myaggp03b(*) (SFUNC = stfp, STYPE = int4[], 99 INITCOND = '{}'); 100 101-- P P 102-- should ERROR: we have no way to resolve S 103CREATE AGGREGATE myaggp04a(*) (SFUNC = stfp, STYPE = anyarray, 104 FINALFUNC = ffp, INITCOND = '{}'); 105CREATE AGGREGATE myaggp04b(*) (SFUNC = stfp, STYPE = anyarray, 106 INITCOND = '{}'); 107 108 109-- Case2 (R = P) && ((B = P) || (B = N)) 110-- ------------------------------------- 111-- S tf1 B tf2 112-- ----------------------- 113-- N N N N 114-- should CREATE 115CREATE AGGREGATE myaggp05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[], 116 FINALFUNC = ffp, INITCOND = '{}'); 117 118-- N N N P 119-- should CREATE 120CREATE AGGREGATE myaggp06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[], 121 FINALFUNC = ffp, INITCOND = '{}'); 122 123-- N N P N 124-- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int) 125CREATE AGGREGATE myaggp07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[], 126 FINALFUNC = ffp, INITCOND = '{}'); 127 128-- N N P P 129-- should CREATE 130CREATE AGGREGATE myaggp08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[], 131 FINALFUNC = ffp, INITCOND = '{}'); 132 133-- N P N N 134-- should CREATE 135CREATE AGGREGATE myaggp09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[], 136 FINALFUNC = ffp, INITCOND = '{}'); 137CREATE AGGREGATE myaggp09b(BASETYPE = int, SFUNC = tf1p, STYPE = int[], 138 INITCOND = '{}'); 139 140-- N P N P 141-- should CREATE 142CREATE AGGREGATE myaggp10a(BASETYPE = int, SFUNC = tfp, STYPE = int[], 143 FINALFUNC = ffp, INITCOND = '{}'); 144CREATE AGGREGATE myaggp10b(BASETYPE = int, SFUNC = tfp, STYPE = int[], 145 INITCOND = '{}'); 146 147-- N P P N 148-- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int) 149CREATE AGGREGATE myaggp11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[], 150 FINALFUNC = ffp, INITCOND = '{}'); 151CREATE AGGREGATE myaggp11b(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[], 152 INITCOND = '{}'); 153 154-- N P P P 155-- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement) 156CREATE AGGREGATE myaggp12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[], 157 FINALFUNC = ffp, INITCOND = '{}'); 158CREATE AGGREGATE myaggp12b(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[], 159 INITCOND = '{}'); 160 161-- P N N N 162-- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int) 163CREATE AGGREGATE myaggp13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, 164 FINALFUNC = ffp, INITCOND = '{}'); 165 166-- P N N P 167-- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement) 168CREATE AGGREGATE myaggp14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, 169 FINALFUNC = ffp, INITCOND = '{}'); 170 171-- P N P N 172-- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int) 173CREATE AGGREGATE myaggp15a(BASETYPE = anyelement, SFUNC = tfnp, 174 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); 175 176-- P N P P 177-- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement) 178CREATE AGGREGATE myaggp16a(BASETYPE = anyelement, SFUNC = tf2p, 179 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); 180 181-- P P N N 182-- should ERROR: we have no way to resolve S 183CREATE AGGREGATE myaggp17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, 184 FINALFUNC = ffp, INITCOND = '{}'); 185CREATE AGGREGATE myaggp17b(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, 186 INITCOND = '{}'); 187 188-- P P N P 189-- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement) 190CREATE AGGREGATE myaggp18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, 191 FINALFUNC = ffp, INITCOND = '{}'); 192CREATE AGGREGATE myaggp18b(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, 193 INITCOND = '{}'); 194 195-- P P P N 196-- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int) 197CREATE AGGREGATE myaggp19a(BASETYPE = anyelement, SFUNC = tf1p, 198 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); 199CREATE AGGREGATE myaggp19b(BASETYPE = anyelement, SFUNC = tf1p, 200 STYPE = anyarray, INITCOND = '{}'); 201 202-- P P P P 203-- should CREATE 204CREATE AGGREGATE myaggp20a(BASETYPE = anyelement, SFUNC = tfp, 205 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); 206CREATE AGGREGATE myaggp20b(BASETYPE = anyelement, SFUNC = tfp, 207 STYPE = anyarray, INITCOND = '{}'); 208 209-- Case3 (R = N) && (B = A) 210-- ------------------------ 211-- S tf1 212-- ------- 213-- N N 214-- should CREATE 215CREATE AGGREGATE myaggn01a(*) (SFUNC = stfnp, STYPE = int4[], 216 FINALFUNC = ffnp, INITCOND = '{}'); 217CREATE AGGREGATE myaggn01b(*) (SFUNC = stfnp, STYPE = int4[], 218 INITCOND = '{}'); 219 220-- P N 221-- should ERROR: stfnp(anyarray) not matched by stfnp(int[]) 222CREATE AGGREGATE myaggn02a(*) (SFUNC = stfnp, STYPE = anyarray, 223 FINALFUNC = ffnp, INITCOND = '{}'); 224CREATE AGGREGATE myaggn02b(*) (SFUNC = stfnp, STYPE = anyarray, 225 INITCOND = '{}'); 226 227-- N P 228-- should CREATE 229CREATE AGGREGATE myaggn03a(*) (SFUNC = stfp, STYPE = int4[], 230 FINALFUNC = ffnp, INITCOND = '{}'); 231 232-- P P 233-- should ERROR: ffnp(anyarray) not matched by ffnp(int[]) 234CREATE AGGREGATE myaggn04a(*) (SFUNC = stfp, STYPE = anyarray, 235 FINALFUNC = ffnp, INITCOND = '{}'); 236 237 238-- Case4 (R = N) && ((B = P) || (B = N)) 239-- ------------------------------------- 240-- S tf1 B tf2 241-- ----------------------- 242-- N N N N 243-- should CREATE 244CREATE AGGREGATE myaggn05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[], 245 FINALFUNC = ffnp, INITCOND = '{}'); 246CREATE AGGREGATE myaggn05b(BASETYPE = int, SFUNC = tfnp, STYPE = int[], 247 INITCOND = '{}'); 248 249-- N N N P 250-- should CREATE 251CREATE AGGREGATE myaggn06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[], 252 FINALFUNC = ffnp, INITCOND = '{}'); 253CREATE AGGREGATE myaggn06b(BASETYPE = int, SFUNC = tf2p, STYPE = int[], 254 INITCOND = '{}'); 255 256-- N N P N 257-- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int) 258CREATE AGGREGATE myaggn07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[], 259 FINALFUNC = ffnp, INITCOND = '{}'); 260CREATE AGGREGATE myaggn07b(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[], 261 INITCOND = '{}'); 262 263-- N N P P 264-- should CREATE 265CREATE AGGREGATE myaggn08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[], 266 FINALFUNC = ffnp, INITCOND = '{}'); 267CREATE AGGREGATE myaggn08b(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[], 268 INITCOND = '{}'); 269 270-- N P N N 271-- should CREATE 272CREATE AGGREGATE myaggn09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[], 273 FINALFUNC = ffnp, INITCOND = '{}'); 274 275-- N P N P 276-- should CREATE 277CREATE AGGREGATE myaggn10a(BASETYPE = int, SFUNC = tfp, STYPE = int[], 278 FINALFUNC = ffnp, INITCOND = '{}'); 279 280-- N P P N 281-- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int) 282CREATE AGGREGATE myaggn11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[], 283 FINALFUNC = ffnp, INITCOND = '{}'); 284 285-- N P P P 286-- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement) 287CREATE AGGREGATE myaggn12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[], 288 FINALFUNC = ffnp, INITCOND = '{}'); 289 290-- P N N N 291-- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int) 292CREATE AGGREGATE myaggn13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, 293 FINALFUNC = ffnp, INITCOND = '{}'); 294CREATE AGGREGATE myaggn13b(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, 295 INITCOND = '{}'); 296 297-- P N N P 298-- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement) 299CREATE AGGREGATE myaggn14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, 300 FINALFUNC = ffnp, INITCOND = '{}'); 301CREATE AGGREGATE myaggn14b(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, 302 INITCOND = '{}'); 303 304-- P N P N 305-- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int) 306CREATE AGGREGATE myaggn15a(BASETYPE = anyelement, SFUNC = tfnp, 307 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}'); 308CREATE AGGREGATE myaggn15b(BASETYPE = anyelement, SFUNC = tfnp, 309 STYPE = anyarray, INITCOND = '{}'); 310 311-- P N P P 312-- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement) 313CREATE AGGREGATE myaggn16a(BASETYPE = anyelement, SFUNC = tf2p, 314 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}'); 315CREATE AGGREGATE myaggn16b(BASETYPE = anyelement, SFUNC = tf2p, 316 STYPE = anyarray, INITCOND = '{}'); 317 318-- P P N N 319-- should ERROR: ffnp(anyarray) not matched by ffnp(int[]) 320CREATE AGGREGATE myaggn17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, 321 FINALFUNC = ffnp, INITCOND = '{}'); 322 323-- P P N P 324-- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement) 325CREATE AGGREGATE myaggn18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, 326 FINALFUNC = ffnp, INITCOND = '{}'); 327 328-- P P P N 329-- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int) 330CREATE AGGREGATE myaggn19a(BASETYPE = anyelement, SFUNC = tf1p, 331 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}'); 332 333-- P P P P 334-- should ERROR: ffnp(anyarray) not matched by ffnp(int[]) 335CREATE AGGREGATE myaggn20a(BASETYPE = anyelement, SFUNC = tfp, 336 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}'); 337 338-- multi-arg polymorphic 339CREATE AGGREGATE mysum2(anyelement,anyelement) (SFUNC = sum3, 340 STYPE = anyelement, INITCOND = '0'); 341 342-- create test data for polymorphic aggregates 343create temp table t(f1 int, f2 int[], f3 text); 344insert into t values(1,array[1],'a'); 345insert into t values(1,array[11],'b'); 346insert into t values(1,array[111],'c'); 347insert into t values(2,array[2],'a'); 348insert into t values(2,array[22],'b'); 349insert into t values(2,array[222],'c'); 350insert into t values(3,array[3],'a'); 351insert into t values(3,array[3],'b'); 352 353-- test the successfully created polymorphic aggregates 354select f3, myaggp01a(*) from t group by f3 order by f3; 355select f3, myaggp03a(*) from t group by f3 order by f3; 356select f3, myaggp03b(*) from t group by f3 order by f3; 357select f3, myaggp05a(f1) from t group by f3 order by f3; 358select f3, myaggp06a(f1) from t group by f3 order by f3; 359select f3, myaggp08a(f1) from t group by f3 order by f3; 360select f3, myaggp09a(f1) from t group by f3 order by f3; 361select f3, myaggp09b(f1) from t group by f3 order by f3; 362select f3, myaggp10a(f1) from t group by f3 order by f3; 363select f3, myaggp10b(f1) from t group by f3 order by f3; 364select f3, myaggp20a(f1) from t group by f3 order by f3; 365select f3, myaggp20b(f1) from t group by f3 order by f3; 366select f3, myaggn01a(*) from t group by f3 order by f3; 367select f3, myaggn01b(*) from t group by f3 order by f3; 368select f3, myaggn03a(*) from t group by f3 order by f3; 369select f3, myaggn05a(f1) from t group by f3 order by f3; 370select f3, myaggn05b(f1) from t group by f3 order by f3; 371select f3, myaggn06a(f1) from t group by f3 order by f3; 372select f3, myaggn06b(f1) from t group by f3 order by f3; 373select f3, myaggn08a(f1) from t group by f3 order by f3; 374select f3, myaggn08b(f1) from t group by f3 order by f3; 375select f3, myaggn09a(f1) from t group by f3 order by f3; 376select f3, myaggn10a(f1) from t group by f3 order by f3; 377select mysum2(f1, f1 + 1) from t; 378 379-- test inlining of polymorphic SQL functions 380create function bleat(int) returns int as $$ 381begin 382 raise notice 'bleat %', $1; 383 return $1; 384end$$ language plpgsql; 385 386create function sql_if(bool, anyelement, anyelement) returns anyelement as $$ 387select case when $1 then $2 else $3 end $$ language sql; 388 389-- Note this would fail with integer overflow, never mind wrong bleat() output, 390-- if the CASE expression were not successfully inlined 391select f1, sql_if(f1 > 0, bleat(f1), bleat(f1 + 1)) from int4_tbl; 392 393select q2, sql_if(q2 > 0, q2, q2 + 1) from int8_tbl; 394 395-- another sort of polymorphic aggregate 396 397CREATE AGGREGATE array_cat_accum (anyarray) 398( 399 sfunc = array_cat, 400 stype = anyarray, 401 initcond = '{}' 402); 403 404SELECT array_cat_accum(i) 405FROM (VALUES (ARRAY[1,2]), (ARRAY[3,4])) as t(i); 406 407SELECT array_cat_accum(i) 408FROM (VALUES (ARRAY[row(1,2),row(3,4)]), (ARRAY[row(5,6),row(7,8)])) as t(i); 409 410-- another kind of polymorphic aggregate 411 412create function add_group(grp anyarray, ad anyelement, size integer) 413 returns anyarray 414 as $$ 415begin 416 if grp is null then 417 return array[ad]; 418 end if; 419 if array_upper(grp, 1) < size then 420 return grp || ad; 421 end if; 422 return grp; 423end; 424$$ 425 language plpgsql immutable; 426 427create aggregate build_group(anyelement, integer) ( 428 SFUNC = add_group, 429 STYPE = anyarray 430); 431 432select build_group(q1,3) from int8_tbl; 433 434-- this should fail because stype isn't compatible with arg 435create aggregate build_group(int8, integer) ( 436 SFUNC = add_group, 437 STYPE = int2[] 438); 439 440-- but we can make a non-poly agg from a poly sfunc if types are OK 441create aggregate build_group(int8, integer) ( 442 SFUNC = add_group, 443 STYPE = int8[] 444); 445 446-- check proper resolution of data types for polymorphic transfn/finalfn 447 448create function first_el(anyarray) returns anyelement as 449'select $1[1]' language sql strict immutable; 450 451create aggregate first_el_agg_f8(float8) ( 452 SFUNC = array_append, 453 STYPE = float8[], 454 FINALFUNC = first_el 455); 456 457create aggregate first_el_agg_any(anyelement) ( 458 SFUNC = array_append, 459 STYPE = anyarray, 460 FINALFUNC = first_el 461); 462 463select first_el_agg_f8(x::float8) from generate_series(1,10) x; 464select first_el_agg_any(x) from generate_series(1,10) x; 465select first_el_agg_f8(x::float8) over(order by x) from generate_series(1,10) x; 466select first_el_agg_any(x) over(order by x) from generate_series(1,10) x; 467 468-- check that we can apply functions taking ANYARRAY to pg_stats 469select distinct array_ndims(histogram_bounds) from pg_stats 470where histogram_bounds is not null; 471 472-- such functions must protect themselves if varying element type isn't OK 473-- (WHERE clause here is to avoid possibly getting a collation error instead) 474select max(histogram_bounds) from pg_stats where tablename = 'pg_am'; 475 476-- test variadic polymorphic functions 477 478create function myleast(variadic anyarray) returns anyelement as $$ 479 select min($1[i]) from generate_subscripts($1,1) g(i) 480$$ language sql immutable strict; 481 482select myleast(10, 1, 20, 33); 483select myleast(1.1, 0.22, 0.55); 484select myleast('z'::text); 485select myleast(); -- fail 486 487-- test with variadic call parameter 488select myleast(variadic array[1,2,3,4,-1]); 489select myleast(variadic array[1.1, -5.5]); 490 491--test with empty variadic call parameter 492select myleast(variadic array[]::int[]); 493 494-- an example with some ordinary arguments too 495create function concat(text, variadic anyarray) returns text as $$ 496 select array_to_string($2, $1); 497$$ language sql immutable strict; 498 499select concat('%', 1, 2, 3, 4, 5); 500select concat('|', 'a'::text, 'b', 'c'); 501select concat('|', variadic array[1,2,33]); 502select concat('|', variadic array[]::int[]); 503 504drop function concat(text, anyarray); 505 506-- mix variadic with anyelement 507create function formarray(anyelement, variadic anyarray) returns anyarray as $$ 508 select array_prepend($1, $2); 509$$ language sql immutable strict; 510 511select formarray(1,2,3,4,5); 512select formarray(1.1, variadic array[1.2,55.5]); 513select formarray(1.1, array[1.2,55.5]); -- fail without variadic 514select formarray(1, 'x'::text); -- fail, type mismatch 515select formarray(1, variadic array['x'::text]); -- fail, type mismatch 516 517drop function formarray(anyelement, variadic anyarray); 518 519-- test pg_typeof() function 520select pg_typeof(null); -- unknown 521select pg_typeof(0); -- integer 522select pg_typeof(0.0); -- numeric 523select pg_typeof(1+1 = 2); -- boolean 524select pg_typeof('x'); -- unknown 525select pg_typeof('' || ''); -- text 526select pg_typeof(pg_typeof(0)); -- regtype 527select pg_typeof(array[1.2,55.5]); -- numeric[] 528select pg_typeof(myleast(10, 1, 20, 33)); -- polymorphic input 529 530-- test functions with default parameters 531 532-- test basic functionality 533create function dfunc(a int = 1, int = 2) returns int as $$ 534 select $1 + $2; 535$$ language sql; 536 537select dfunc(); 538select dfunc(10); 539select dfunc(10, 20); 540select dfunc(10, 20, 30); -- fail 541 542drop function dfunc(); -- fail 543drop function dfunc(int); -- fail 544drop function dfunc(int, int); -- ok 545 546-- fail: defaults must be at end of argument list 547create function dfunc(a int = 1, b int) returns int as $$ 548 select $1 + $2; 549$$ language sql; 550 551-- however, this should work: 552create function dfunc(a int = 1, out sum int, b int = 2) as $$ 553 select $1 + $2; 554$$ language sql; 555 556select dfunc(); 557 558-- verify it lists properly 559\df dfunc 560 561drop function dfunc(int, int); 562 563-- check implicit coercion 564create function dfunc(a int DEFAULT 1.0, int DEFAULT '-1') returns int as $$ 565 select $1 + $2; 566$$ language sql; 567select dfunc(); 568 569create function dfunc(a text DEFAULT 'Hello', b text DEFAULT 'World') returns text as $$ 570 select $1 || ', ' || $2; 571$$ language sql; 572 573select dfunc(); -- fail: which dfunc should be called? int or text 574select dfunc('Hi'); -- ok 575select dfunc('Hi', 'City'); -- ok 576select dfunc(0); -- ok 577select dfunc(10, 20); -- ok 578 579drop function dfunc(int, int); 580drop function dfunc(text, text); 581 582create function dfunc(int = 1, int = 2) returns int as $$ 583 select 2; 584$$ language sql; 585 586create function dfunc(int = 1, int = 2, int = 3, int = 4) returns int as $$ 587 select 4; 588$$ language sql; 589 590-- Now, dfunc(nargs = 2) and dfunc(nargs = 4) are ambiguous when called 591-- with 0 to 2 arguments. 592 593select dfunc(); -- fail 594select dfunc(1); -- fail 595select dfunc(1, 2); -- fail 596select dfunc(1, 2, 3); -- ok 597select dfunc(1, 2, 3, 4); -- ok 598 599drop function dfunc(int, int); 600drop function dfunc(int, int, int, int); 601 602-- default values are not allowed for output parameters 603create function dfunc(out int = 20) returns int as $$ 604 select 1; 605$$ language sql; 606 607-- polymorphic parameter test 608create function dfunc(anyelement = 'World'::text) returns text as $$ 609 select 'Hello, ' || $1::text; 610$$ language sql; 611 612select dfunc(); 613select dfunc(0); 614select dfunc(to_date('20081215','YYYYMMDD')); 615select dfunc('City'::text); 616 617drop function dfunc(anyelement); 618 619-- check defaults for variadics 620 621create function dfunc(a variadic int[]) returns int as 622$$ select array_upper($1, 1) $$ language sql; 623 624select dfunc(); -- fail 625select dfunc(10); 626select dfunc(10,20); 627 628create or replace function dfunc(a variadic int[] default array[]::int[]) returns int as 629$$ select array_upper($1, 1) $$ language sql; 630 631select dfunc(); -- now ok 632select dfunc(10); 633select dfunc(10,20); 634 635-- can't remove the default once it exists 636create or replace function dfunc(a variadic int[]) returns int as 637$$ select array_upper($1, 1) $$ language sql; 638 639\df dfunc 640 641drop function dfunc(a variadic int[]); 642 643-- Ambiguity should be reported only if there's not a better match available 644 645create function dfunc(int = 1, int = 2, int = 3) returns int as $$ 646 select 3; 647$$ language sql; 648 649create function dfunc(int = 1, int = 2) returns int as $$ 650 select 2; 651$$ language sql; 652 653create function dfunc(text) returns text as $$ 654 select $1; 655$$ language sql; 656 657-- dfunc(narg=2) and dfunc(narg=3) are ambiguous 658select dfunc(1); -- fail 659 660-- but this works since the ambiguous functions aren't preferred anyway 661select dfunc('Hi'); 662 663drop function dfunc(int, int, int); 664drop function dfunc(int, int); 665drop function dfunc(text); 666 667-- 668-- Tests for named- and mixed-notation function calling 669-- 670 671create function dfunc(a int, b int, c int = 0, d int = 0) 672 returns table (a int, b int, c int, d int) as $$ 673 select $1, $2, $3, $4; 674$$ language sql; 675 676select (dfunc(10,20,30)).*; 677select (dfunc(a := 10, b := 20, c := 30)).*; 678select * from dfunc(a := 10, b := 20); 679select * from dfunc(b := 10, a := 20); 680select * from dfunc(0); -- fail 681select * from dfunc(1,2); 682select * from dfunc(1,2,c := 3); 683select * from dfunc(1,2,d := 3); 684 685select * from dfunc(x := 20, b := 10, x := 30); -- fail, duplicate name 686select * from dfunc(10, b := 20, 30); -- fail, named args must be last 687select * from dfunc(x := 10, b := 20, c := 30); -- fail, unknown param 688select * from dfunc(10, 10, a := 20); -- fail, a overlaps positional parameter 689select * from dfunc(1,c := 2,d := 3); -- fail, no value for b 690 691drop function dfunc(int, int, int, int); 692 693-- test with different parameter types 694create function dfunc(a varchar, b numeric, c date = current_date) 695 returns table (a varchar, b numeric, c date) as $$ 696 select $1, $2, $3; 697$$ language sql; 698 699select (dfunc('Hello World', 20, '2009-07-25'::date)).*; 700select * from dfunc('Hello World', 20, '2009-07-25'::date); 701select * from dfunc(c := '2009-07-25'::date, a := 'Hello World', b := 20); 702select * from dfunc('Hello World', b := 20, c := '2009-07-25'::date); 703select * from dfunc('Hello World', c := '2009-07-25'::date, b := 20); 704select * from dfunc('Hello World', c := 20, b := '2009-07-25'::date); -- fail 705 706drop function dfunc(varchar, numeric, date); 707 708-- test out parameters with named params 709create function dfunc(a varchar = 'def a', out _a varchar, c numeric = NULL, out _c numeric) 710returns record as $$ 711 select $1, $2; 712$$ language sql; 713 714select (dfunc()).*; 715select * from dfunc(); 716select * from dfunc('Hello', 100); 717select * from dfunc(a := 'Hello', c := 100); 718select * from dfunc(c := 100, a := 'Hello'); 719select * from dfunc('Hello'); 720select * from dfunc('Hello', c := 100); 721select * from dfunc(c := 100); 722 723-- fail, can no longer change an input parameter's name 724create or replace function dfunc(a varchar = 'def a', out _a varchar, x numeric = NULL, out _c numeric) 725returns record as $$ 726 select $1, $2; 727$$ language sql; 728 729create or replace function dfunc(a varchar = 'def a', out _a varchar, numeric = NULL, out _c numeric) 730returns record as $$ 731 select $1, $2; 732$$ language sql; 733 734drop function dfunc(varchar, numeric); 735 736--fail, named parameters are not unique 737create function testpolym(a int, a int) returns int as $$ select 1;$$ language sql; 738create function testpolym(int, out a int, out a int) returns int as $$ select 1;$$ language sql; 739create function testpolym(out a int, inout a int) returns int as $$ select 1;$$ language sql; 740create function testpolym(a int, inout a int) returns int as $$ select 1;$$ language sql; 741 742-- valid 743create function testpolym(a int, out a int) returns int as $$ select $1;$$ language sql; 744select testpolym(37); 745drop function testpolym(int); 746create function testpolym(a int) returns table(a int) as $$ select $1;$$ language sql; 747select * from testpolym(37); 748drop function testpolym(int); 749 750-- test polymorphic params and defaults 751create function dfunc(a anyelement, b anyelement = null, flag bool = true) 752returns anyelement as $$ 753 select case when $3 then $1 else $2 end; 754$$ language sql; 755 756select dfunc(1,2); 757select dfunc('a'::text, 'b'); -- positional notation with default 758 759select dfunc(a := 1, b := 2); 760select dfunc(a := 'a'::text, b := 'b'); 761select dfunc(a := 'a'::text, b := 'b', flag := false); -- named notation 762 763select dfunc(b := 'b'::text, a := 'a'); -- named notation with default 764select dfunc(a := 'a'::text, flag := true); -- named notation with default 765select dfunc(a := 'a'::text, flag := false); -- named notation with default 766select dfunc(b := 'b'::text, a := 'a', flag := true); -- named notation 767 768select dfunc('a'::text, 'b', false); -- full positional notation 769select dfunc('a'::text, 'b', flag := false); -- mixed notation 770select dfunc('a'::text, 'b', true); -- full positional notation 771select dfunc('a'::text, 'b', flag := true); -- mixed notation 772 773-- ansi/sql syntax 774select dfunc(a => 1, b => 2); 775select dfunc(a => 'a'::text, b => 'b'); 776select dfunc(a => 'a'::text, b => 'b', flag => false); -- named notation 777 778select dfunc(b => 'b'::text, a => 'a'); -- named notation with default 779select dfunc(a => 'a'::text, flag => true); -- named notation with default 780select dfunc(a => 'a'::text, flag => false); -- named notation with default 781select dfunc(b => 'b'::text, a => 'a', flag => true); -- named notation 782 783select dfunc('a'::text, 'b', false); -- full positional notation 784select dfunc('a'::text, 'b', flag => false); -- mixed notation 785select dfunc('a'::text, 'b', true); -- full positional notation 786select dfunc('a'::text, 'b', flag => true); -- mixed notation 787 788-- this tests lexer edge cases around => 789select dfunc(a =>-1); 790select dfunc(a =>+1); 791select dfunc(a =>/**/1); 792select dfunc(a =>--comment to be removed by psql 793 1); 794-- need DO to protect the -- from psql 795do $$ 796 declare r integer; 797 begin 798 select dfunc(a=>-- comment 799 1) into r; 800 raise info 'r = %', r; 801 end; 802$$; 803 804-- check reverse-listing of named-arg calls 805CREATE VIEW dfview AS 806 SELECT q1, q2, 807 dfunc(q1,q2, flag := q1>q2) as c3, 808 dfunc(q1, flag := q1<q2, b := q2) as c4 809 FROM int8_tbl; 810 811select * from dfview; 812 813\d+ dfview 814 815drop view dfview; 816drop function dfunc(anyelement, anyelement, bool); 817