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-- Legend: 5----------- 6-- A = type is ANY 7-- P = type is polymorphic 8-- N = type is non-polymorphic 9-- B = aggregate base type 10-- S = aggregate state type 11-- R = aggregate return type 12-- 1 = arg1 of a function 13-- 2 = arg2 of a function 14-- ag = aggregate 15-- tf = trans (state) function 16-- ff = final function 17-- rt = return type of a function 18-- -> = implies 19-- => = allowed 20-- !> = not allowed 21-- E = exists 22-- NE = not-exists 23-- 24-- Possible states: 25-- ---------------- 26-- B = (A || P || N) 27-- when (B = A) -> (tf2 = NE) 28-- S = (P || N) 29-- ff = (E || NE) 30-- tf1 = (P || N) 31-- tf2 = (NE || P || N) 32-- R = (P || N) 33-- create functions for use as tf and ff with the needed combinations of 34-- argument polymorphism, but within the constraints of valid aggregate 35-- functions, i.e. tf arg1 and tf return type must match 36-- polymorphic single arg transfn 37CREATE FUNCTION stfp(anyarray) RETURNS anyarray AS 38'select $1' LANGUAGE SQL; 39-- non-polymorphic single arg transfn 40CREATE FUNCTION stfnp(int[]) RETURNS int[] AS 41'select $1' LANGUAGE SQL; 42-- dual polymorphic transfn 43CREATE FUNCTION tfp(anyarray,anyelement) RETURNS anyarray AS 44'select $1 || $2' LANGUAGE SQL; 45-- dual non-polymorphic transfn 46CREATE FUNCTION tfnp(int[],int) RETURNS int[] AS 47'select $1 || $2' LANGUAGE SQL; 48-- arg1 only polymorphic transfn 49CREATE FUNCTION tf1p(anyarray,int) RETURNS anyarray AS 50'select $1' LANGUAGE SQL; 51-- arg2 only polymorphic transfn 52CREATE FUNCTION tf2p(int[],anyelement) RETURNS int[] AS 53'select $1' LANGUAGE SQL; 54-- multi-arg polymorphic 55CREATE FUNCTION sum3(anyelement,anyelement,anyelement) returns anyelement AS 56'select $1+$2+$3' language sql strict; 57-- finalfn polymorphic 58CREATE FUNCTION ffp(anyarray) RETURNS anyarray AS 59'select $1' LANGUAGE SQL; 60-- finalfn non-polymorphic 61CREATE FUNCTION ffnp(int[]) returns int[] as 62'select $1' LANGUAGE SQL; 63-- Try to cover all the possible states: 64-- 65-- Note: in Cases 1 & 2, we are trying to return P. Therefore, if the transfn 66-- is stfnp, tfnp, or tf2p, we must use ffp as finalfn, because stfnp, tfnp, 67-- and tf2p do not return P. Conversely, in Cases 3 & 4, we are trying to 68-- return N. Therefore, if the transfn is stfp, tfp, or tf1p, we must use ffnp 69-- as finalfn, because stfp, tfp, and tf1p do not return N. 70-- 71-- Case1 (R = P) && (B = A) 72-- ------------------------ 73-- S tf1 74-- ------- 75-- N N 76-- should CREATE 77CREATE AGGREGATE myaggp01a(*) (SFUNC = stfnp, STYPE = int4[], 78 FINALFUNC = ffp, INITCOND = '{}'); 79-- P N 80-- should ERROR: stfnp(anyarray) not matched by stfnp(int[]) 81CREATE AGGREGATE myaggp02a(*) (SFUNC = stfnp, STYPE = anyarray, 82 FINALFUNC = ffp, INITCOND = '{}'); 83ERROR: cannot determine transition data type 84DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 85-- N P 86-- should CREATE 87CREATE AGGREGATE myaggp03a(*) (SFUNC = stfp, STYPE = int4[], 88 FINALFUNC = ffp, INITCOND = '{}'); 89CREATE AGGREGATE myaggp03b(*) (SFUNC = stfp, STYPE = int4[], 90 INITCOND = '{}'); 91-- P P 92-- should ERROR: we have no way to resolve S 93CREATE AGGREGATE myaggp04a(*) (SFUNC = stfp, STYPE = anyarray, 94 FINALFUNC = ffp, INITCOND = '{}'); 95ERROR: cannot determine transition data type 96DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 97CREATE AGGREGATE myaggp04b(*) (SFUNC = stfp, STYPE = anyarray, 98 INITCOND = '{}'); 99ERROR: cannot determine transition data type 100DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 101-- Case2 (R = P) && ((B = P) || (B = N)) 102-- ------------------------------------- 103-- S tf1 B tf2 104-- ----------------------- 105-- N N N N 106-- should CREATE 107CREATE AGGREGATE myaggp05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[], 108 FINALFUNC = ffp, INITCOND = '{}'); 109-- N N N P 110-- should CREATE 111CREATE AGGREGATE myaggp06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[], 112 FINALFUNC = ffp, INITCOND = '{}'); 113-- N N P N 114-- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int) 115CREATE AGGREGATE myaggp07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[], 116 FINALFUNC = ffp, INITCOND = '{}'); 117ERROR: function tfnp(integer[], anyelement) does not exist 118-- N N P P 119-- should CREATE 120CREATE AGGREGATE myaggp08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[], 121 FINALFUNC = ffp, INITCOND = '{}'); 122-- N P N N 123-- should CREATE 124CREATE AGGREGATE myaggp09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[], 125 FINALFUNC = ffp, INITCOND = '{}'); 126CREATE AGGREGATE myaggp09b(BASETYPE = int, SFUNC = tf1p, STYPE = int[], 127 INITCOND = '{}'); 128-- N P N P 129-- should CREATE 130CREATE AGGREGATE myaggp10a(BASETYPE = int, SFUNC = tfp, STYPE = int[], 131 FINALFUNC = ffp, INITCOND = '{}'); 132CREATE AGGREGATE myaggp10b(BASETYPE = int, SFUNC = tfp, STYPE = int[], 133 INITCOND = '{}'); 134-- N P P N 135-- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int) 136CREATE AGGREGATE myaggp11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[], 137 FINALFUNC = ffp, INITCOND = '{}'); 138ERROR: function tf1p(integer[], anyelement) does not exist 139CREATE AGGREGATE myaggp11b(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[], 140 INITCOND = '{}'); 141ERROR: function tf1p(integer[], anyelement) does not exist 142-- N P P P 143-- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement) 144CREATE AGGREGATE myaggp12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[], 145 FINALFUNC = ffp, INITCOND = '{}'); 146ERROR: function tfp(integer[], anyelement) does not exist 147CREATE AGGREGATE myaggp12b(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[], 148 INITCOND = '{}'); 149ERROR: function tfp(integer[], anyelement) does not exist 150-- P N N N 151-- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int) 152CREATE AGGREGATE myaggp13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, 153 FINALFUNC = ffp, INITCOND = '{}'); 154ERROR: cannot determine transition data type 155DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 156-- P N N P 157-- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement) 158CREATE AGGREGATE myaggp14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, 159 FINALFUNC = ffp, INITCOND = '{}'); 160ERROR: cannot determine transition data type 161DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 162-- P N P N 163-- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int) 164CREATE AGGREGATE myaggp15a(BASETYPE = anyelement, SFUNC = tfnp, 165 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); 166ERROR: function tfnp(anyarray, anyelement) does not exist 167-- P N P P 168-- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement) 169CREATE AGGREGATE myaggp16a(BASETYPE = anyelement, SFUNC = tf2p, 170 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); 171ERROR: function tf2p(anyarray, anyelement) does not exist 172-- P P N N 173-- should ERROR: we have no way to resolve S 174CREATE AGGREGATE myaggp17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, 175 FINALFUNC = ffp, INITCOND = '{}'); 176ERROR: cannot determine transition data type 177DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 178CREATE AGGREGATE myaggp17b(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, 179 INITCOND = '{}'); 180ERROR: cannot determine transition data type 181DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 182-- P P N P 183-- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement) 184CREATE AGGREGATE myaggp18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, 185 FINALFUNC = ffp, INITCOND = '{}'); 186ERROR: cannot determine transition data type 187DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 188CREATE AGGREGATE myaggp18b(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, 189 INITCOND = '{}'); 190ERROR: cannot determine transition data type 191DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 192-- P P P N 193-- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int) 194CREATE AGGREGATE myaggp19a(BASETYPE = anyelement, SFUNC = tf1p, 195 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); 196ERROR: function tf1p(anyarray, anyelement) does not exist 197CREATE AGGREGATE myaggp19b(BASETYPE = anyelement, SFUNC = tf1p, 198 STYPE = anyarray, INITCOND = '{}'); 199ERROR: function tf1p(anyarray, anyelement) does not exist 200-- P P P P 201-- should CREATE 202CREATE AGGREGATE myaggp20a(BASETYPE = anyelement, SFUNC = tfp, 203 STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); 204CREATE AGGREGATE myaggp20b(BASETYPE = anyelement, SFUNC = tfp, 205 STYPE = anyarray, INITCOND = '{}'); 206-- Case3 (R = N) && (B = A) 207-- ------------------------ 208-- S tf1 209-- ------- 210-- N N 211-- should CREATE 212CREATE AGGREGATE myaggn01a(*) (SFUNC = stfnp, STYPE = int4[], 213 FINALFUNC = ffnp, INITCOND = '{}'); 214CREATE AGGREGATE myaggn01b(*) (SFUNC = stfnp, STYPE = int4[], 215 INITCOND = '{}'); 216-- P N 217-- should ERROR: stfnp(anyarray) not matched by stfnp(int[]) 218CREATE AGGREGATE myaggn02a(*) (SFUNC = stfnp, STYPE = anyarray, 219 FINALFUNC = ffnp, INITCOND = '{}'); 220ERROR: cannot determine transition data type 221DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 222CREATE AGGREGATE myaggn02b(*) (SFUNC = stfnp, STYPE = anyarray, 223 INITCOND = '{}'); 224ERROR: cannot determine transition data type 225DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 226-- N P 227-- should CREATE 228CREATE AGGREGATE myaggn03a(*) (SFUNC = stfp, STYPE = int4[], 229 FINALFUNC = ffnp, INITCOND = '{}'); 230-- P P 231-- should ERROR: ffnp(anyarray) not matched by ffnp(int[]) 232CREATE AGGREGATE myaggn04a(*) (SFUNC = stfp, STYPE = anyarray, 233 FINALFUNC = ffnp, INITCOND = '{}'); 234ERROR: cannot determine transition data type 235DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 236-- Case4 (R = N) && ((B = P) || (B = N)) 237-- ------------------------------------- 238-- S tf1 B tf2 239-- ----------------------- 240-- N N N N 241-- should CREATE 242CREATE AGGREGATE myaggn05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[], 243 FINALFUNC = ffnp, INITCOND = '{}'); 244CREATE AGGREGATE myaggn05b(BASETYPE = int, SFUNC = tfnp, STYPE = int[], 245 INITCOND = '{}'); 246-- N N N P 247-- should CREATE 248CREATE AGGREGATE myaggn06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[], 249 FINALFUNC = ffnp, INITCOND = '{}'); 250CREATE AGGREGATE myaggn06b(BASETYPE = int, SFUNC = tf2p, STYPE = int[], 251 INITCOND = '{}'); 252-- N N P N 253-- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int) 254CREATE AGGREGATE myaggn07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[], 255 FINALFUNC = ffnp, INITCOND = '{}'); 256ERROR: function tfnp(integer[], anyelement) does not exist 257CREATE AGGREGATE myaggn07b(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[], 258 INITCOND = '{}'); 259ERROR: function tfnp(integer[], anyelement) does not exist 260-- N N P P 261-- should CREATE 262CREATE AGGREGATE myaggn08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[], 263 FINALFUNC = ffnp, INITCOND = '{}'); 264CREATE AGGREGATE myaggn08b(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[], 265 INITCOND = '{}'); 266-- N P N N 267-- should CREATE 268CREATE AGGREGATE myaggn09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[], 269 FINALFUNC = ffnp, INITCOND = '{}'); 270-- N P N P 271-- should CREATE 272CREATE AGGREGATE myaggn10a(BASETYPE = int, SFUNC = tfp, STYPE = int[], 273 FINALFUNC = ffnp, INITCOND = '{}'); 274-- N P P N 275-- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int) 276CREATE AGGREGATE myaggn11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[], 277 FINALFUNC = ffnp, INITCOND = '{}'); 278ERROR: function tf1p(integer[], anyelement) does not exist 279-- N P P P 280-- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement) 281CREATE AGGREGATE myaggn12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[], 282 FINALFUNC = ffnp, INITCOND = '{}'); 283ERROR: function tfp(integer[], anyelement) does not exist 284-- P N N N 285-- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int) 286CREATE AGGREGATE myaggn13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, 287 FINALFUNC = ffnp, INITCOND = '{}'); 288ERROR: cannot determine transition data type 289DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 290CREATE AGGREGATE myaggn13b(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, 291 INITCOND = '{}'); 292ERROR: cannot determine transition data type 293DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 294-- P N N P 295-- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement) 296CREATE AGGREGATE myaggn14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, 297 FINALFUNC = ffnp, INITCOND = '{}'); 298ERROR: cannot determine transition data type 299DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 300CREATE AGGREGATE myaggn14b(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, 301 INITCOND = '{}'); 302ERROR: cannot determine transition data type 303DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 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 = '{}'); 308ERROR: function tfnp(anyarray, anyelement) does not exist 309CREATE AGGREGATE myaggn15b(BASETYPE = anyelement, SFUNC = tfnp, 310 STYPE = anyarray, INITCOND = '{}'); 311ERROR: function tfnp(anyarray, anyelement) does not exist 312-- P N P P 313-- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement) 314CREATE AGGREGATE myaggn16a(BASETYPE = anyelement, SFUNC = tf2p, 315 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}'); 316ERROR: function tf2p(anyarray, anyelement) does not exist 317CREATE AGGREGATE myaggn16b(BASETYPE = anyelement, SFUNC = tf2p, 318 STYPE = anyarray, INITCOND = '{}'); 319ERROR: function tf2p(anyarray, anyelement) does not exist 320-- P P N N 321-- should ERROR: ffnp(anyarray) not matched by ffnp(int[]) 322CREATE AGGREGATE myaggn17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, 323 FINALFUNC = ffnp, INITCOND = '{}'); 324ERROR: cannot determine transition data type 325DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 326-- P P N P 327-- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement) 328CREATE AGGREGATE myaggn18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, 329 FINALFUNC = ffnp, INITCOND = '{}'); 330ERROR: cannot determine transition data type 331DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument. 332-- P P P N 333-- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int) 334CREATE AGGREGATE myaggn19a(BASETYPE = anyelement, SFUNC = tf1p, 335 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}'); 336ERROR: function tf1p(anyarray, anyelement) does not exist 337-- P P P P 338-- should ERROR: ffnp(anyarray) not matched by ffnp(int[]) 339CREATE AGGREGATE myaggn20a(BASETYPE = anyelement, SFUNC = tfp, 340 STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}'); 341ERROR: function ffnp(anyarray) does not exist 342-- multi-arg polymorphic 343CREATE AGGREGATE mysum2(anyelement,anyelement) (SFUNC = sum3, 344 STYPE = anyelement, INITCOND = '0'); 345-- create test data for polymorphic aggregates 346create temp table t(f1 int, f2 int[], f3 text); 347insert into t values(1,array[1],'a'); 348insert into t values(1,array[11],'b'); 349insert into t values(1,array[111],'c'); 350insert into t values(2,array[2],'a'); 351insert into t values(2,array[22],'b'); 352insert into t values(2,array[222],'c'); 353insert into t values(3,array[3],'a'); 354insert into t values(3,array[3],'b'); 355-- test the successfully created polymorphic aggregates 356select f3, myaggp01a(*) from t group by f3 order by f3; 357 f3 | myaggp01a 358----+----------- 359 a | {} 360 b | {} 361 c | {} 362(3 rows) 363 364select f3, myaggp03a(*) from t group by f3 order by f3; 365 f3 | myaggp03a 366----+----------- 367 a | {} 368 b | {} 369 c | {} 370(3 rows) 371 372select f3, myaggp03b(*) from t group by f3 order by f3; 373 f3 | myaggp03b 374----+----------- 375 a | {} 376 b | {} 377 c | {} 378(3 rows) 379 380select f3, myaggp05a(f1) from t group by f3 order by f3; 381 f3 | myaggp05a 382----+----------- 383 a | {1,2,3} 384 b | {1,2,3} 385 c | {1,2} 386(3 rows) 387 388select f3, myaggp06a(f1) from t group by f3 order by f3; 389 f3 | myaggp06a 390----+----------- 391 a | {} 392 b | {} 393 c | {} 394(3 rows) 395 396select f3, myaggp08a(f1) from t group by f3 order by f3; 397 f3 | myaggp08a 398----+----------- 399 a | {} 400 b | {} 401 c | {} 402(3 rows) 403 404select f3, myaggp09a(f1) from t group by f3 order by f3; 405 f3 | myaggp09a 406----+----------- 407 a | {} 408 b | {} 409 c | {} 410(3 rows) 411 412select f3, myaggp09b(f1) from t group by f3 order by f3; 413 f3 | myaggp09b 414----+----------- 415 a | {} 416 b | {} 417 c | {} 418(3 rows) 419 420select f3, myaggp10a(f1) from t group by f3 order by f3; 421 f3 | myaggp10a 422----+----------- 423 a | {1,2,3} 424 b | {1,2,3} 425 c | {1,2} 426(3 rows) 427 428select f3, myaggp10b(f1) from t group by f3 order by f3; 429 f3 | myaggp10b 430----+----------- 431 a | {1,2,3} 432 b | {1,2,3} 433 c | {1,2} 434(3 rows) 435 436select f3, myaggp20a(f1) from t group by f3 order by f3; 437 f3 | myaggp20a 438----+----------- 439 a | {1,2,3} 440 b | {1,2,3} 441 c | {1,2} 442(3 rows) 443 444select f3, myaggp20b(f1) from t group by f3 order by f3; 445 f3 | myaggp20b 446----+----------- 447 a | {1,2,3} 448 b | {1,2,3} 449 c | {1,2} 450(3 rows) 451 452select f3, myaggn01a(*) from t group by f3 order by f3; 453 f3 | myaggn01a 454----+----------- 455 a | {} 456 b | {} 457 c | {} 458(3 rows) 459 460select f3, myaggn01b(*) from t group by f3 order by f3; 461 f3 | myaggn01b 462----+----------- 463 a | {} 464 b | {} 465 c | {} 466(3 rows) 467 468select f3, myaggn03a(*) from t group by f3 order by f3; 469 f3 | myaggn03a 470----+----------- 471 a | {} 472 b | {} 473 c | {} 474(3 rows) 475 476select f3, myaggn05a(f1) from t group by f3 order by f3; 477 f3 | myaggn05a 478----+----------- 479 a | {1,2,3} 480 b | {1,2,3} 481 c | {1,2} 482(3 rows) 483 484select f3, myaggn05b(f1) from t group by f3 order by f3; 485 f3 | myaggn05b 486----+----------- 487 a | {1,2,3} 488 b | {1,2,3} 489 c | {1,2} 490(3 rows) 491 492select f3, myaggn06a(f1) from t group by f3 order by f3; 493 f3 | myaggn06a 494----+----------- 495 a | {} 496 b | {} 497 c | {} 498(3 rows) 499 500select f3, myaggn06b(f1) from t group by f3 order by f3; 501 f3 | myaggn06b 502----+----------- 503 a | {} 504 b | {} 505 c | {} 506(3 rows) 507 508select f3, myaggn08a(f1) from t group by f3 order by f3; 509 f3 | myaggn08a 510----+----------- 511 a | {} 512 b | {} 513 c | {} 514(3 rows) 515 516select f3, myaggn08b(f1) from t group by f3 order by f3; 517 f3 | myaggn08b 518----+----------- 519 a | {} 520 b | {} 521 c | {} 522(3 rows) 523 524select f3, myaggn09a(f1) from t group by f3 order by f3; 525 f3 | myaggn09a 526----+----------- 527 a | {} 528 b | {} 529 c | {} 530(3 rows) 531 532select f3, myaggn10a(f1) from t group by f3 order by f3; 533 f3 | myaggn10a 534----+----------- 535 a | {1,2,3} 536 b | {1,2,3} 537 c | {1,2} 538(3 rows) 539 540select mysum2(f1, f1 + 1) from t; 541 mysum2 542-------- 543 38 544(1 row) 545 546-- test inlining of polymorphic SQL functions 547create function bleat(int) returns int as $$ 548begin 549 raise notice 'bleat %', $1; 550 return $1; 551end$$ language plpgsql; 552create function sql_if(bool, anyelement, anyelement) returns anyelement as $$ 553select case when $1 then $2 else $3 end $$ language sql; 554-- Note this would fail with integer overflow, never mind wrong bleat() output, 555-- if the CASE expression were not successfully inlined 556select f1, sql_if(f1 > 0, bleat(f1), bleat(f1 + 1)) from int4_tbl; 557NOTICE: bleat 1 558NOTICE: bleat 123456 559NOTICE: bleat -123455 560NOTICE: bleat 2147483647 561NOTICE: bleat -2147483646 562 f1 | sql_if 563-------------+------------- 564 0 | 1 565 123456 | 123456 566 -123456 | -123455 567 2147483647 | 2147483647 568 -2147483647 | -2147483646 569(5 rows) 570 571select q2, sql_if(q2 > 0, q2, q2 + 1) from int8_tbl; 572 q2 | sql_if 573-------------------+------------------- 574 456 | 456 575 4567890123456789 | 4567890123456789 576 123 | 123 577 4567890123456789 | 4567890123456789 578 -4567890123456789 | -4567890123456788 579(5 rows) 580 581-- another sort of polymorphic aggregate 582CREATE AGGREGATE array_larger_accum (anyarray) 583( 584 sfunc = array_larger, 585 stype = anyarray, 586 initcond = '{}' 587); 588SELECT array_larger_accum(i) 589FROM (VALUES (ARRAY[1,2]), (ARRAY[3,4])) as t(i); 590 array_larger_accum 591-------------------- 592 {3,4} 593(1 row) 594 595SELECT array_larger_accum(i) 596FROM (VALUES (ARRAY[row(1,2),row(3,4)]), (ARRAY[row(5,6),row(7,8)])) as t(i); 597 array_larger_accum 598-------------------- 599 {"(5,6)","(7,8)"} 600(1 row) 601 602-- another kind of polymorphic aggregate 603create function add_group(grp anyarray, ad anyelement, size integer) 604 returns anyarray 605 as $$ 606begin 607 if grp is null then 608 return array[ad]; 609 end if; 610 if array_upper(grp, 1) < size then 611 return grp || ad; 612 end if; 613 return grp; 614end; 615$$ 616 language plpgsql immutable; 617create aggregate build_group(anyelement, integer) ( 618 SFUNC = add_group, 619 STYPE = anyarray 620); 621select build_group(q1,3) from int8_tbl; 622 build_group 623---------------------------- 624 {123,123,4567890123456789} 625(1 row) 626 627-- this should fail because stype isn't compatible with arg 628create aggregate build_group(int8, integer) ( 629 SFUNC = add_group, 630 STYPE = int2[] 631); 632ERROR: function add_group(smallint[], bigint, integer) does not exist 633-- but we can make a non-poly agg from a poly sfunc if types are OK 634create aggregate build_group(int8, integer) ( 635 SFUNC = add_group, 636 STYPE = int8[] 637); 638-- check proper resolution of data types for polymorphic transfn/finalfn 639create function first_el_transfn(anyarray, anyelement) returns anyarray as 640'select $1 || $2' language sql immutable; 641create function first_el(anyarray) returns anyelement as 642'select $1[1]' language sql strict immutable; 643create aggregate first_el_agg_f8(float8) ( 644 SFUNC = array_append, 645 STYPE = float8[], 646 FINALFUNC = first_el 647); 648create aggregate first_el_agg_any(anyelement) ( 649 SFUNC = first_el_transfn, 650 STYPE = anyarray, 651 FINALFUNC = first_el 652); 653select first_el_agg_f8(x::float8) from generate_series(1,10) x; 654 first_el_agg_f8 655----------------- 656 1 657(1 row) 658 659select first_el_agg_any(x) from generate_series(1,10) x; 660 first_el_agg_any 661------------------ 662 1 663(1 row) 664 665select first_el_agg_f8(x::float8) over(order by x) from generate_series(1,10) x; 666 first_el_agg_f8 667----------------- 668 1 669 1 670 1 671 1 672 1 673 1 674 1 675 1 676 1 677 1 678(10 rows) 679 680select first_el_agg_any(x) over(order by x) from generate_series(1,10) x; 681 first_el_agg_any 682------------------ 683 1 684 1 685 1 686 1 687 1 688 1 689 1 690 1 691 1 692 1 693(10 rows) 694 695-- check that we can apply functions taking ANYARRAY to pg_stats 696select distinct array_ndims(histogram_bounds) from pg_stats 697where histogram_bounds is not null; 698 array_ndims 699------------- 700 1 701(1 row) 702 703-- such functions must protect themselves if varying element type isn't OK 704-- (WHERE clause here is to avoid possibly getting a collation error instead) 705select max(histogram_bounds) from pg_stats where tablename = 'pg_am'; 706ERROR: cannot compare arrays of different element types 707-- test variadic polymorphic functions 708create function myleast(variadic anyarray) returns anyelement as $$ 709 select min($1[i]) from generate_subscripts($1,1) g(i) 710$$ language sql immutable strict; 711select myleast(10, 1, 20, 33); 712 myleast 713--------- 714 1 715(1 row) 716 717select myleast(1.1, 0.22, 0.55); 718 myleast 719--------- 720 0.22 721(1 row) 722 723select myleast('z'::text); 724 myleast 725--------- 726 z 727(1 row) 728 729select myleast(); -- fail 730ERROR: function myleast() does not exist 731LINE 1: select myleast(); 732 ^ 733HINT: No function matches the given name and argument types. You might need to add explicit type casts. 734-- test with variadic call parameter 735select myleast(variadic array[1,2,3,4,-1]); 736 myleast 737--------- 738 -1 739(1 row) 740 741select myleast(variadic array[1.1, -5.5]); 742 myleast 743--------- 744 -5.5 745(1 row) 746 747--test with empty variadic call parameter 748select myleast(variadic array[]::int[]); 749 myleast 750--------- 751 752(1 row) 753 754-- an example with some ordinary arguments too 755create function concat(text, variadic anyarray) returns text as $$ 756 select array_to_string($2, $1); 757$$ language sql immutable strict; 758select concat('%', 1, 2, 3, 4, 5); 759 concat 760----------- 761 1%2%3%4%5 762(1 row) 763 764select concat('|', 'a'::text, 'b', 'c'); 765 concat 766-------- 767 a|b|c 768(1 row) 769 770select concat('|', variadic array[1,2,33]); 771 concat 772-------- 773 1|2|33 774(1 row) 775 776select concat('|', variadic array[]::int[]); 777 concat 778-------- 779 780(1 row) 781 782drop function concat(text, anyarray); 783-- mix variadic with anyelement 784create function formarray(anyelement, variadic anyarray) returns anyarray as $$ 785 select array_prepend($1, $2); 786$$ language sql immutable strict; 787select formarray(1,2,3,4,5); 788 formarray 789------------- 790 {1,2,3,4,5} 791(1 row) 792 793select formarray(1.1, variadic array[1.2,55.5]); 794 formarray 795---------------- 796 {1.1,1.2,55.5} 797(1 row) 798 799select formarray(1.1, array[1.2,55.5]); -- fail without variadic 800ERROR: function formarray(numeric, numeric[]) does not exist 801LINE 1: select formarray(1.1, array[1.2,55.5]); 802 ^ 803HINT: No function matches the given name and argument types. You might need to add explicit type casts. 804select formarray(1, 'x'::text); -- fail, type mismatch 805ERROR: function formarray(integer, text) does not exist 806LINE 1: select formarray(1, 'x'::text); 807 ^ 808HINT: No function matches the given name and argument types. You might need to add explicit type casts. 809select formarray(1, variadic array['x'::text]); -- fail, type mismatch 810ERROR: function formarray(integer, text[]) does not exist 811LINE 1: select formarray(1, variadic array['x'::text]); 812 ^ 813HINT: No function matches the given name and argument types. You might need to add explicit type casts. 814drop function formarray(anyelement, variadic anyarray); 815-- test pg_typeof() function 816select pg_typeof(null); -- unknown 817 pg_typeof 818----------- 819 unknown 820(1 row) 821 822select pg_typeof(0); -- integer 823 pg_typeof 824----------- 825 integer 826(1 row) 827 828select pg_typeof(0.0); -- numeric 829 pg_typeof 830----------- 831 numeric 832(1 row) 833 834select pg_typeof(1+1 = 2); -- boolean 835 pg_typeof 836----------- 837 boolean 838(1 row) 839 840select pg_typeof('x'); -- unknown 841 pg_typeof 842----------- 843 unknown 844(1 row) 845 846select pg_typeof('' || ''); -- text 847 pg_typeof 848----------- 849 text 850(1 row) 851 852select pg_typeof(pg_typeof(0)); -- regtype 853 pg_typeof 854----------- 855 regtype 856(1 row) 857 858select pg_typeof(array[1.2,55.5]); -- numeric[] 859 pg_typeof 860----------- 861 numeric[] 862(1 row) 863 864select pg_typeof(myleast(10, 1, 20, 33)); -- polymorphic input 865 pg_typeof 866----------- 867 integer 868(1 row) 869 870-- test functions with default parameters 871-- test basic functionality 872create function dfunc(a int = 1, int = 2) returns int as $$ 873 select $1 + $2; 874$$ language sql; 875select dfunc(); 876 dfunc 877------- 878 3 879(1 row) 880 881select dfunc(10); 882 dfunc 883------- 884 12 885(1 row) 886 887select dfunc(10, 20); 888 dfunc 889------- 890 30 891(1 row) 892 893select dfunc(10, 20, 30); -- fail 894ERROR: function dfunc(integer, integer, integer) does not exist 895LINE 1: select dfunc(10, 20, 30); 896 ^ 897HINT: No function matches the given name and argument types. You might need to add explicit type casts. 898drop function dfunc(); -- fail 899ERROR: function dfunc() does not exist 900drop function dfunc(int); -- fail 901ERROR: function dfunc(integer) does not exist 902drop function dfunc(int, int); -- ok 903-- fail: defaults must be at end of argument list 904create function dfunc(a int = 1, b int) returns int as $$ 905 select $1 + $2; 906$$ language sql; 907ERROR: input parameters after one with a default value must also have defaults 908-- however, this should work: 909create function dfunc(a int = 1, out sum int, b int = 2) as $$ 910 select $1 + $2; 911$$ language sql; 912select dfunc(); 913 dfunc 914------- 915 3 916(1 row) 917 918-- verify it lists properly 919\df dfunc 920 List of functions 921 Schema | Name | Result data type | Argument data types | Type 922--------+-------+------------------+-----------------------------------------------------------+-------- 923 public | dfunc | integer | a integer DEFAULT 1, OUT sum integer, b integer DEFAULT 2 | normal 924(1 row) 925 926drop function dfunc(int, int); 927-- check implicit coercion 928create function dfunc(a int DEFAULT 1.0, int DEFAULT '-1') returns int as $$ 929 select $1 + $2; 930$$ language sql; 931select dfunc(); 932 dfunc 933------- 934 0 935(1 row) 936 937create function dfunc(a text DEFAULT 'Hello', b text DEFAULT 'World') returns text as $$ 938 select $1 || ', ' || $2; 939$$ language sql; 940select dfunc(); -- fail: which dfunc should be called? int or text 941ERROR: function dfunc() is not unique 942LINE 1: select dfunc(); 943 ^ 944HINT: Could not choose a best candidate function. You might need to add explicit type casts. 945select dfunc('Hi'); -- ok 946 dfunc 947----------- 948 Hi, World 949(1 row) 950 951select dfunc('Hi', 'City'); -- ok 952 dfunc 953---------- 954 Hi, City 955(1 row) 956 957select dfunc(0); -- ok 958 dfunc 959------- 960 -1 961(1 row) 962 963select dfunc(10, 20); -- ok 964 dfunc 965------- 966 30 967(1 row) 968 969drop function dfunc(int, int); 970drop function dfunc(text, text); 971create function dfunc(int = 1, int = 2) returns int as $$ 972 select 2; 973$$ language sql; 974create function dfunc(int = 1, int = 2, int = 3, int = 4) returns int as $$ 975 select 4; 976$$ language sql; 977-- Now, dfunc(nargs = 2) and dfunc(nargs = 4) are ambiguous when called 978-- with 0 to 2 arguments. 979select dfunc(); -- fail 980ERROR: function dfunc() is not unique 981LINE 1: select dfunc(); 982 ^ 983HINT: Could not choose a best candidate function. You might need to add explicit type casts. 984select dfunc(1); -- fail 985ERROR: function dfunc(integer) is not unique 986LINE 1: select dfunc(1); 987 ^ 988HINT: Could not choose a best candidate function. You might need to add explicit type casts. 989select dfunc(1, 2); -- fail 990ERROR: function dfunc(integer, integer) is not unique 991LINE 1: select dfunc(1, 2); 992 ^ 993HINT: Could not choose a best candidate function. You might need to add explicit type casts. 994select dfunc(1, 2, 3); -- ok 995 dfunc 996------- 997 4 998(1 row) 999 1000select dfunc(1, 2, 3, 4); -- ok 1001 dfunc 1002------- 1003 4 1004(1 row) 1005 1006drop function dfunc(int, int); 1007drop function dfunc(int, int, int, int); 1008-- default values are not allowed for output parameters 1009create function dfunc(out int = 20) returns int as $$ 1010 select 1; 1011$$ language sql; 1012ERROR: only input parameters can have default values 1013-- polymorphic parameter test 1014create function dfunc(anyelement = 'World'::text) returns text as $$ 1015 select 'Hello, ' || $1::text; 1016$$ language sql; 1017select dfunc(); 1018 dfunc 1019-------------- 1020 Hello, World 1021(1 row) 1022 1023select dfunc(0); 1024 dfunc 1025---------- 1026 Hello, 0 1027(1 row) 1028 1029select dfunc(to_date('20081215','YYYYMMDD')); 1030 dfunc 1031------------------- 1032 Hello, 12-15-2008 1033(1 row) 1034 1035select dfunc('City'::text); 1036 dfunc 1037------------- 1038 Hello, City 1039(1 row) 1040 1041drop function dfunc(anyelement); 1042-- check defaults for variadics 1043create function dfunc(a variadic int[]) returns int as 1044$$ select array_upper($1, 1) $$ language sql; 1045select dfunc(); -- fail 1046ERROR: function dfunc() does not exist 1047LINE 1: select dfunc(); 1048 ^ 1049HINT: No function matches the given name and argument types. You might need to add explicit type casts. 1050select dfunc(10); 1051 dfunc 1052------- 1053 1 1054(1 row) 1055 1056select dfunc(10,20); 1057 dfunc 1058------- 1059 2 1060(1 row) 1061 1062create or replace function dfunc(a variadic int[] default array[]::int[]) returns int as 1063$$ select array_upper($1, 1) $$ language sql; 1064select dfunc(); -- now ok 1065 dfunc 1066------- 1067 1068(1 row) 1069 1070select dfunc(10); 1071 dfunc 1072------- 1073 1 1074(1 row) 1075 1076select dfunc(10,20); 1077 dfunc 1078------- 1079 2 1080(1 row) 1081 1082-- can't remove the default once it exists 1083create or replace function dfunc(a variadic int[]) returns int as 1084$$ select array_upper($1, 1) $$ language sql; 1085ERROR: cannot remove parameter defaults from existing function 1086HINT: Use DROP FUNCTION dfunc(integer[]) first. 1087\df dfunc 1088 List of functions 1089 Schema | Name | Result data type | Argument data types | Type 1090--------+-------+------------------+-------------------------------------------------+-------- 1091 public | dfunc | integer | VARIADIC a integer[] DEFAULT ARRAY[]::integer[] | normal 1092(1 row) 1093 1094drop function dfunc(a variadic int[]); 1095-- Ambiguity should be reported only if there's not a better match available 1096create function dfunc(int = 1, int = 2, int = 3) returns int as $$ 1097 select 3; 1098$$ language sql; 1099create function dfunc(int = 1, int = 2) returns int as $$ 1100 select 2; 1101$$ language sql; 1102create function dfunc(text) returns text as $$ 1103 select $1; 1104$$ language sql; 1105-- dfunc(narg=2) and dfunc(narg=3) are ambiguous 1106select dfunc(1); -- fail 1107ERROR: function dfunc(integer) is not unique 1108LINE 1: select dfunc(1); 1109 ^ 1110HINT: Could not choose a best candidate function. You might need to add explicit type casts. 1111-- but this works since the ambiguous functions aren't preferred anyway 1112select dfunc('Hi'); 1113 dfunc 1114------- 1115 Hi 1116(1 row) 1117 1118drop function dfunc(int, int, int); 1119drop function dfunc(int, int); 1120drop function dfunc(text); 1121-- 1122-- Tests for named- and mixed-notation function calling 1123-- 1124create function dfunc(a int, b int, c int = 0, d int = 0) 1125 returns table (a int, b int, c int, d int) as $$ 1126 select $1, $2, $3, $4; 1127$$ language sql; 1128select (dfunc(10,20,30)).*; 1129 a | b | c | d 1130----+----+----+--- 1131 10 | 20 | 30 | 0 1132(1 row) 1133 1134select (dfunc(a := 10, b := 20, c := 30)).*; 1135 a | b | c | d 1136----+----+----+--- 1137 10 | 20 | 30 | 0 1138(1 row) 1139 1140select * from dfunc(a := 10, b := 20); 1141 a | b | c | d 1142----+----+---+--- 1143 10 | 20 | 0 | 0 1144(1 row) 1145 1146select * from dfunc(b := 10, a := 20); 1147 a | b | c | d 1148----+----+---+--- 1149 20 | 10 | 0 | 0 1150(1 row) 1151 1152select * from dfunc(0); -- fail 1153ERROR: function dfunc(integer) does not exist 1154LINE 1: select * from dfunc(0); 1155 ^ 1156HINT: No function matches the given name and argument types. You might need to add explicit type casts. 1157select * from dfunc(1,2); 1158 a | b | c | d 1159---+---+---+--- 1160 1 | 2 | 0 | 0 1161(1 row) 1162 1163select * from dfunc(1,2,c := 3); 1164 a | b | c | d 1165---+---+---+--- 1166 1 | 2 | 3 | 0 1167(1 row) 1168 1169select * from dfunc(1,2,d := 3); 1170 a | b | c | d 1171---+---+---+--- 1172 1 | 2 | 0 | 3 1173(1 row) 1174 1175select * from dfunc(x := 20, b := 10, x := 30); -- fail, duplicate name 1176ERROR: argument name "x" used more than once 1177LINE 1: select * from dfunc(x := 20, b := 10, x := 30); 1178 ^ 1179select * from dfunc(10, b := 20, 30); -- fail, named args must be last 1180ERROR: positional argument cannot follow named argument 1181LINE 1: select * from dfunc(10, b := 20, 30); 1182 ^ 1183select * from dfunc(x := 10, b := 20, c := 30); -- fail, unknown param 1184ERROR: function dfunc(x => integer, b => integer, c => integer) does not exist 1185LINE 1: select * from dfunc(x := 10, b := 20, c := 30); 1186 ^ 1187HINT: No function matches the given name and argument types. You might need to add explicit type casts. 1188select * from dfunc(10, 10, a := 20); -- fail, a overlaps positional parameter 1189ERROR: function dfunc(integer, integer, a => integer) does not exist 1190LINE 1: select * from dfunc(10, 10, a := 20); 1191 ^ 1192HINT: No function matches the given name and argument types. You might need to add explicit type casts. 1193select * from dfunc(1,c := 2,d := 3); -- fail, no value for b 1194ERROR: function dfunc(integer, c => integer, d => integer) does not exist 1195LINE 1: select * from dfunc(1,c := 2,d := 3); 1196 ^ 1197HINT: No function matches the given name and argument types. You might need to add explicit type casts. 1198drop function dfunc(int, int, int, int); 1199-- test with different parameter types 1200create function dfunc(a varchar, b numeric, c date = current_date) 1201 returns table (a varchar, b numeric, c date) as $$ 1202 select $1, $2, $3; 1203$$ language sql; 1204select (dfunc('Hello World', 20, '2009-07-25'::date)).*; 1205 a | b | c 1206-------------+----+------------ 1207 Hello World | 20 | 07-25-2009 1208(1 row) 1209 1210select * from dfunc('Hello World', 20, '2009-07-25'::date); 1211 a | b | c 1212-------------+----+------------ 1213 Hello World | 20 | 07-25-2009 1214(1 row) 1215 1216select * from dfunc(c := '2009-07-25'::date, a := 'Hello World', b := 20); 1217 a | b | c 1218-------------+----+------------ 1219 Hello World | 20 | 07-25-2009 1220(1 row) 1221 1222select * from dfunc('Hello World', b := 20, c := '2009-07-25'::date); 1223 a | b | c 1224-------------+----+------------ 1225 Hello World | 20 | 07-25-2009 1226(1 row) 1227 1228select * from dfunc('Hello World', c := '2009-07-25'::date, b := 20); 1229 a | b | c 1230-------------+----+------------ 1231 Hello World | 20 | 07-25-2009 1232(1 row) 1233 1234select * from dfunc('Hello World', c := 20, b := '2009-07-25'::date); -- fail 1235ERROR: function dfunc(unknown, c => integer, b => date) does not exist 1236LINE 1: select * from dfunc('Hello World', c := 20, b := '2009-07-25... 1237 ^ 1238HINT: No function matches the given name and argument types. You might need to add explicit type casts. 1239drop function dfunc(varchar, numeric, date); 1240-- test out parameters with named params 1241create function dfunc(a varchar = 'def a', out _a varchar, c numeric = NULL, out _c numeric) 1242returns record as $$ 1243 select $1, $2; 1244$$ language sql; 1245select (dfunc()).*; 1246 _a | _c 1247-------+---- 1248 def a | 1249(1 row) 1250 1251select * from dfunc(); 1252 _a | _c 1253-------+---- 1254 def a | 1255(1 row) 1256 1257select * from dfunc('Hello', 100); 1258 _a | _c 1259-------+----- 1260 Hello | 100 1261(1 row) 1262 1263select * from dfunc(a := 'Hello', c := 100); 1264 _a | _c 1265-------+----- 1266 Hello | 100 1267(1 row) 1268 1269select * from dfunc(c := 100, a := 'Hello'); 1270 _a | _c 1271-------+----- 1272 Hello | 100 1273(1 row) 1274 1275select * from dfunc('Hello'); 1276 _a | _c 1277-------+---- 1278 Hello | 1279(1 row) 1280 1281select * from dfunc('Hello', c := 100); 1282 _a | _c 1283-------+----- 1284 Hello | 100 1285(1 row) 1286 1287select * from dfunc(c := 100); 1288 _a | _c 1289-------+----- 1290 def a | 100 1291(1 row) 1292 1293-- fail, can no longer change an input parameter's name 1294create or replace function dfunc(a varchar = 'def a', out _a varchar, x numeric = NULL, out _c numeric) 1295returns record as $$ 1296 select $1, $2; 1297$$ language sql; 1298ERROR: cannot change name of input parameter "c" 1299HINT: Use DROP FUNCTION dfunc(character varying,numeric) first. 1300create or replace function dfunc(a varchar = 'def a', out _a varchar, numeric = NULL, out _c numeric) 1301returns record as $$ 1302 select $1, $2; 1303$$ language sql; 1304ERROR: cannot change name of input parameter "c" 1305HINT: Use DROP FUNCTION dfunc(character varying,numeric) first. 1306drop function dfunc(varchar, numeric); 1307--fail, named parameters are not unique 1308create function testfoo(a int, a int) returns int as $$ select 1;$$ language sql; 1309ERROR: parameter name "a" used more than once 1310create function testfoo(int, out a int, out a int) returns int as $$ select 1;$$ language sql; 1311ERROR: parameter name "a" used more than once 1312create function testfoo(out a int, inout a int) returns int as $$ select 1;$$ language sql; 1313ERROR: parameter name "a" used more than once 1314create function testfoo(a int, inout a int) returns int as $$ select 1;$$ language sql; 1315ERROR: parameter name "a" used more than once 1316-- valid 1317create function testfoo(a int, out a int) returns int as $$ select $1;$$ language sql; 1318select testfoo(37); 1319 testfoo 1320--------- 1321 37 1322(1 row) 1323 1324drop function testfoo(int); 1325create function testfoo(a int) returns table(a int) as $$ select $1;$$ language sql; 1326select * from testfoo(37); 1327 a 1328---- 1329 37 1330(1 row) 1331 1332drop function testfoo(int); 1333-- test polymorphic params and defaults 1334create function dfunc(a anyelement, b anyelement = null, flag bool = true) 1335returns anyelement as $$ 1336 select case when $3 then $1 else $2 end; 1337$$ language sql; 1338select dfunc(1,2); 1339 dfunc 1340------- 1341 1 1342(1 row) 1343 1344select dfunc('a'::text, 'b'); -- positional notation with default 1345 dfunc 1346------- 1347 a 1348(1 row) 1349 1350select dfunc(a := 1, b := 2); 1351 dfunc 1352------- 1353 1 1354(1 row) 1355 1356select dfunc(a := 'a'::text, b := 'b'); 1357 dfunc 1358------- 1359 a 1360(1 row) 1361 1362select dfunc(a := 'a'::text, b := 'b', flag := false); -- named notation 1363 dfunc 1364------- 1365 b 1366(1 row) 1367 1368select dfunc(b := 'b'::text, a := 'a'); -- named notation with default 1369 dfunc 1370------- 1371 a 1372(1 row) 1373 1374select dfunc(a := 'a'::text, flag := true); -- named notation with default 1375 dfunc 1376------- 1377 a 1378(1 row) 1379 1380select dfunc(a := 'a'::text, flag := false); -- named notation with default 1381 dfunc 1382------- 1383 1384(1 row) 1385 1386select dfunc(b := 'b'::text, a := 'a', flag := true); -- named notation 1387 dfunc 1388------- 1389 a 1390(1 row) 1391 1392select dfunc('a'::text, 'b', false); -- full positional notation 1393 dfunc 1394------- 1395 b 1396(1 row) 1397 1398select dfunc('a'::text, 'b', flag := false); -- mixed notation 1399 dfunc 1400------- 1401 b 1402(1 row) 1403 1404select dfunc('a'::text, 'b', true); -- full positional notation 1405 dfunc 1406------- 1407 a 1408(1 row) 1409 1410select dfunc('a'::text, 'b', flag := true); -- mixed notation 1411 dfunc 1412------- 1413 a 1414(1 row) 1415 1416-- ansi/sql syntax 1417select dfunc(a => 1, b => 2); 1418 dfunc 1419------- 1420 1 1421(1 row) 1422 1423select dfunc(a => 'a'::text, b => 'b'); 1424 dfunc 1425------- 1426 a 1427(1 row) 1428 1429select dfunc(a => 'a'::text, b => 'b', flag => false); -- named notation 1430 dfunc 1431------- 1432 b 1433(1 row) 1434 1435select dfunc(b => 'b'::text, a => 'a'); -- named notation with default 1436 dfunc 1437------- 1438 a 1439(1 row) 1440 1441select dfunc(a => 'a'::text, flag => true); -- named notation with default 1442 dfunc 1443------- 1444 a 1445(1 row) 1446 1447select dfunc(a => 'a'::text, flag => false); -- named notation with default 1448 dfunc 1449------- 1450 1451(1 row) 1452 1453select dfunc(b => 'b'::text, a => 'a', flag => true); -- named notation 1454 dfunc 1455------- 1456 a 1457(1 row) 1458 1459select dfunc('a'::text, 'b', false); -- full positional notation 1460 dfunc 1461------- 1462 b 1463(1 row) 1464 1465select dfunc('a'::text, 'b', flag => false); -- mixed notation 1466 dfunc 1467------- 1468 b 1469(1 row) 1470 1471select dfunc('a'::text, 'b', true); -- full positional notation 1472 dfunc 1473------- 1474 a 1475(1 row) 1476 1477select dfunc('a'::text, 'b', flag => true); -- mixed notation 1478 dfunc 1479------- 1480 a 1481(1 row) 1482 1483-- this tests lexer edge cases around => 1484select dfunc(a =>-1); 1485 dfunc 1486------- 1487 -1 1488(1 row) 1489 1490select dfunc(a =>+1); 1491 dfunc 1492------- 1493 1 1494(1 row) 1495 1496select dfunc(a =>/**/1); 1497 dfunc 1498------- 1499 1 1500(1 row) 1501 1502select dfunc(a =>--comment to be removed by psql 1503 1); 1504 dfunc 1505------- 1506 1 1507(1 row) 1508 1509-- need DO to protect the -- from psql 1510do $$ 1511 declare r integer; 1512 begin 1513 select dfunc(a=>-- comment 1514 1) into r; 1515 raise info 'r = %', r; 1516 end; 1517$$; 1518INFO: r = 1 1519-- check reverse-listing of named-arg calls 1520CREATE VIEW dfview AS 1521 SELECT q1, q2, 1522 dfunc(q1,q2, flag := q1>q2) as c3, 1523 dfunc(q1, flag := q1<q2, b := q2) as c4 1524 FROM int8_tbl; 1525select * from dfview; 1526 q1 | q2 | c3 | c4 1527------------------+-------------------+------------------+------------------- 1528 123 | 456 | 456 | 123 1529 123 | 4567890123456789 | 4567890123456789 | 123 1530 4567890123456789 | 123 | 4567890123456789 | 123 1531 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 1532 4567890123456789 | -4567890123456789 | 4567890123456789 | -4567890123456789 1533(5 rows) 1534 1535\d+ dfview 1536 View "public.dfview" 1537 Column | Type | Collation | Nullable | Default | Storage | Description 1538--------+--------+-----------+----------+---------+---------+------------- 1539 q1 | bigint | | | | plain | 1540 q2 | bigint | | | | plain | 1541 c3 | bigint | | | | plain | 1542 c4 | bigint | | | | plain | 1543View definition: 1544 SELECT int8_tbl.q1, 1545 int8_tbl.q2, 1546 dfunc(int8_tbl.q1, int8_tbl.q2, flag => int8_tbl.q1 > int8_tbl.q2) AS c3, 1547 dfunc(int8_tbl.q1, flag => int8_tbl.q1 < int8_tbl.q2, b => int8_tbl.q2) AS c4 1548 FROM int8_tbl; 1549 1550drop view dfview; 1551drop function dfunc(anyelement, anyelement, bool); 1552