diff options
Diffstat (limited to 'src/test/regress/expected/polymorphism.out')
| -rw-r--r-- | src/test/regress/expected/polymorphism.out | 2098 |
1 files changed, 2098 insertions, 0 deletions
diff --git a/src/test/regress/expected/polymorphism.out b/src/test/regress/expected/polymorphism.out new file mode 100644 index 0000000..1cd558d --- /dev/null +++ b/src/test/regress/expected/polymorphism.out @@ -0,0 +1,2098 @@ +-- +-- Tests for polymorphic SQL functions and aggregates based on them. +-- Tests for other features related to function-calling have snuck in, too. +-- +create function polyf(x anyelement) returns anyelement as $$ + select x + 1 +$$ language sql; +select polyf(42) as int, polyf(4.5) as num; + int | num +-----+----- + 43 | 5.5 +(1 row) + +select polyf(point(3,4)); -- fail for lack of + operator +ERROR: operator does not exist: point + integer +LINE 2: select x + 1 + ^ +HINT: No operator matches the given name and argument types. You might need to add explicit type casts. +QUERY: + select x + 1 + +CONTEXT: SQL function "polyf" during inlining +drop function polyf(x anyelement); +create function polyf(x anyelement) returns anyarray as $$ + select array[x + 1, x + 2] +$$ language sql; +select polyf(42) as int, polyf(4.5) as num; + int | num +---------+----------- + {43,44} | {5.5,6.5} +(1 row) + +drop function polyf(x anyelement); +create function polyf(x anyarray) returns anyelement as $$ + select x[1] +$$ language sql; +select polyf(array[2,4]) as int, polyf(array[4.5, 7.7]) as num; + int | num +-----+----- + 2 | 4.5 +(1 row) + +select polyf(stavalues1) from pg_statistic; -- fail, can't infer element type +ERROR: cannot determine element type of "anyarray" argument +drop function polyf(x anyarray); +create function polyf(x anyarray) returns anyarray as $$ + select x +$$ language sql; +select polyf(array[2,4]) as int, polyf(array[4.5, 7.7]) as num; + int | num +-------+----------- + {2,4} | {4.5,7.7} +(1 row) + +select polyf(stavalues1) from pg_statistic; -- fail, can't infer element type +ERROR: return type anyarray is not supported for SQL functions +CONTEXT: SQL function "polyf" during inlining +drop function polyf(x anyarray); +-- fail, can't infer type: +create function polyf(x anyelement) returns anyrange as $$ + select array[x + 1, x + 2] +$$ language sql; +ERROR: cannot determine result data type +DETAIL: A result of type anyrange requires at least one input of type anyrange or anymultirange. +create function polyf(x anyrange) returns anyarray as $$ + select array[lower(x), upper(x)] +$$ language sql; +select polyf(int4range(42, 49)) as int, polyf(float8range(4.5, 7.8)) as num; + int | num +---------+----------- + {42,49} | {4.5,7.8} +(1 row) + +drop function polyf(x anyrange); +create function polyf(x anycompatible, y anycompatible) returns anycompatiblearray as $$ + select array[x, y] +$$ language sql; +select polyf(2, 4) as int, polyf(2, 4.5) as num; + int | num +-------+--------- + {2,4} | {2,4.5} +(1 row) + +drop function polyf(x anycompatible, y anycompatible); +create function polyf(x anycompatiblerange, y anycompatible, z anycompatible) returns anycompatiblearray as $$ + select array[lower(x), upper(x), y, z] +$$ language sql; +select polyf(int4range(42, 49), 11, 2::smallint) as int, polyf(float8range(4.5, 7.8), 7.8, 11::real) as num; + int | num +--------------+------------------ + {42,49,11,2} | {4.5,7.8,7.8,11} +(1 row) + +select polyf(int4range(42, 49), 11, 4.5) as fail; -- range type doesn't fit +ERROR: function polyf(int4range, integer, numeric) does not exist +LINE 1: select polyf(int4range(42, 49), 11, 4.5) as fail; + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function polyf(x anycompatiblerange, y anycompatible, z anycompatible); +create function polyf(x anycompatiblemultirange, y anycompatible, z anycompatible) returns anycompatiblearray as $$ + select array[lower(x), upper(x), y, z] +$$ language sql; +select polyf(multirange(int4range(42, 49)), 11, 2::smallint) as int, polyf(multirange(float8range(4.5, 7.8)), 7.8, 11::real) as num; + int | num +--------------+------------------ + {42,49,11,2} | {4.5,7.8,7.8,11} +(1 row) + +select polyf(multirange(int4range(42, 49)), 11, 4.5) as fail; -- range type doesn't fit +ERROR: function polyf(int4multirange, integer, numeric) does not exist +LINE 1: select polyf(multirange(int4range(42, 49)), 11, 4.5) as fail... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function polyf(x anycompatiblemultirange, y anycompatible, z anycompatible); +-- fail, can't infer type: +create function polyf(x anycompatible) returns anycompatiblerange as $$ + select array[x + 1, x + 2] +$$ language sql; +ERROR: cannot determine result data type +DETAIL: A result of type anycompatiblerange requires at least one input of type anycompatiblerange or anycompatiblemultirange. +create function polyf(x anycompatiblerange, y anycompatiblearray) returns anycompatiblerange as $$ + select x +$$ language sql; +select polyf(int4range(42, 49), array[11]) as int, polyf(float8range(4.5, 7.8), array[7]) as num; + int | num +---------+----------- + [42,49) | [4.5,7.8) +(1 row) + +drop function polyf(x anycompatiblerange, y anycompatiblearray); +-- fail, can't infer type: +create function polyf(x anycompatible) returns anycompatiblemultirange as $$ + select array[x + 1, x + 2] +$$ language sql; +ERROR: cannot determine result data type +DETAIL: A result of type anycompatiblemultirange requires at least one input of type anycompatiblerange or anycompatiblemultirange. +create function polyf(x anycompatiblemultirange, y anycompatiblearray) returns anycompatiblemultirange as $$ + select x +$$ language sql; +select polyf(multirange(int4range(42, 49)), array[11]) as int, polyf(multirange(float8range(4.5, 7.8)), array[7]) as num; + int | num +-----------+------------- + {[42,49)} | {[4.5,7.8)} +(1 row) + +drop function polyf(x anycompatiblemultirange, y anycompatiblearray); +create function polyf(a anyelement, b anyarray, + c anycompatible, d anycompatible, + OUT x anyarray, OUT y anycompatiblearray) +as $$ + select a || b, array[c, d] +$$ language sql; +select x, pg_typeof(x), y, pg_typeof(y) + from polyf(11, array[1, 2], 42, 34.5); + x | pg_typeof | y | pg_typeof +----------+-----------+-----------+----------- + {11,1,2} | integer[] | {42,34.5} | numeric[] +(1 row) + +select x, pg_typeof(x), y, pg_typeof(y) + from polyf(11, array[1, 2], point(1,2), point(3,4)); + x | pg_typeof | y | pg_typeof +----------+-----------+-------------------+----------- + {11,1,2} | integer[] | {"(1,2)","(3,4)"} | point[] +(1 row) + +select x, pg_typeof(x), y, pg_typeof(y) + from polyf(11, '{1,2}', point(1,2), '(3,4)'); + x | pg_typeof | y | pg_typeof +----------+-----------+-------------------+----------- + {11,1,2} | integer[] | {"(1,2)","(3,4)"} | point[] +(1 row) + +select x, pg_typeof(x), y, pg_typeof(y) + from polyf(11, array[1, 2.2], 42, 34.5); -- fail +ERROR: function polyf(integer, numeric[], integer, numeric) does not exist +LINE 2: from polyf(11, array[1, 2.2], 42, 34.5); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function polyf(a anyelement, b anyarray, + c anycompatible, d anycompatible); +create function polyf(anyrange) returns anymultirange +as 'select multirange($1);' language sql; +select polyf(int4range(1,10)); + polyf +---------- + {[1,10)} +(1 row) + +select polyf(null); +ERROR: could not determine polymorphic type because input has type unknown +drop function polyf(anyrange); +create function polyf(anymultirange) returns anyelement +as 'select lower($1);' language sql; +select polyf(int4multirange(int4range(1,10), int4range(20,30))); + polyf +------- + 1 +(1 row) + +select polyf(null); +ERROR: could not determine polymorphic type because input has type unknown +drop function polyf(anymultirange); +create function polyf(anycompatiblerange) returns anycompatiblemultirange +as 'select multirange($1);' language sql; +select polyf(int4range(1,10)); + polyf +---------- + {[1,10)} +(1 row) + +select polyf(null); +ERROR: could not determine polymorphic type anycompatiblerange because input has type unknown +drop function polyf(anycompatiblerange); +create function polyf(anymultirange) returns anyrange +as 'select range_merge($1);' language sql; +select polyf(int4multirange(int4range(1,10), int4range(20,30))); + polyf +-------- + [1,30) +(1 row) + +select polyf(null); +ERROR: could not determine polymorphic type because input has type unknown +drop function polyf(anymultirange); +create function polyf(anycompatiblemultirange) returns anycompatiblerange +as 'select range_merge($1);' language sql; +select polyf(int4multirange(int4range(1,10), int4range(20,30))); + polyf +-------- + [1,30) +(1 row) + +select polyf(null); +ERROR: could not determine polymorphic type anycompatiblerange because input has type unknown +drop function polyf(anycompatiblemultirange); +create function polyf(anycompatiblemultirange) returns anycompatible +as 'select lower($1);' language sql; +select polyf(int4multirange(int4range(1,10), int4range(20,30))); + polyf +------- + 1 +(1 row) + +select polyf(null); +ERROR: could not determine polymorphic type anycompatiblemultirange because input has type unknown +drop function polyf(anycompatiblemultirange); +-- +-- Polymorphic aggregate tests +-- +-- Legend: +----------- +-- A = type is ANY +-- P = type is polymorphic +-- N = type is non-polymorphic +-- B = aggregate base type +-- S = aggregate state type +-- R = aggregate return type +-- 1 = arg1 of a function +-- 2 = arg2 of a function +-- ag = aggregate +-- tf = trans (state) function +-- ff = final function +-- rt = return type of a function +-- -> = implies +-- => = allowed +-- !> = not allowed +-- E = exists +-- NE = not-exists +-- +-- Possible states: +-- ---------------- +-- B = (A || P || N) +-- when (B = A) -> (tf2 = NE) +-- S = (P || N) +-- ff = (E || NE) +-- tf1 = (P || N) +-- tf2 = (NE || P || N) +-- R = (P || N) +-- create functions for use as tf and ff with the needed combinations of +-- argument polymorphism, but within the constraints of valid aggregate +-- functions, i.e. tf arg1 and tf return type must match +-- polymorphic single arg transfn +CREATE FUNCTION stfp(anyarray) RETURNS anyarray AS +'select $1' LANGUAGE SQL; +-- non-polymorphic single arg transfn +CREATE FUNCTION stfnp(int[]) RETURNS int[] AS +'select $1' LANGUAGE SQL; +-- dual polymorphic transfn +CREATE FUNCTION tfp(anyarray,anyelement) RETURNS anyarray AS +'select $1 || $2' LANGUAGE SQL; +-- dual non-polymorphic transfn +CREATE FUNCTION tfnp(int[],int) RETURNS int[] AS +'select $1 || $2' LANGUAGE SQL; +-- arg1 only polymorphic transfn +CREATE FUNCTION tf1p(anyarray,int) RETURNS anyarray AS +'select $1' LANGUAGE SQL; +-- arg2 only polymorphic transfn +CREATE FUNCTION tf2p(int[],anyelement) RETURNS int[] AS +'select $1' LANGUAGE SQL; +-- multi-arg polymorphic +CREATE FUNCTION sum3(anyelement,anyelement,anyelement) returns anyelement AS +'select $1+$2+$3' language sql strict; +-- finalfn polymorphic +CREATE FUNCTION ffp(anyarray) RETURNS anyarray AS +'select $1' LANGUAGE SQL; +-- finalfn non-polymorphic +CREATE FUNCTION ffnp(int[]) returns int[] as +'select $1' LANGUAGE SQL; +-- Try to cover all the possible states: +-- +-- Note: in Cases 1 & 2, we are trying to return P. Therefore, if the transfn +-- is stfnp, tfnp, or tf2p, we must use ffp as finalfn, because stfnp, tfnp, +-- and tf2p do not return P. Conversely, in Cases 3 & 4, we are trying to +-- return N. Therefore, if the transfn is stfp, tfp, or tf1p, we must use ffnp +-- as finalfn, because stfp, tfp, and tf1p do not return N. +-- +-- Case1 (R = P) && (B = A) +-- ------------------------ +-- S tf1 +-- ------- +-- N N +-- should CREATE +CREATE AGGREGATE myaggp01a(*) (SFUNC = stfnp, STYPE = int4[], + FINALFUNC = ffp, INITCOND = '{}'); +-- P N +-- should ERROR: stfnp(anyarray) not matched by stfnp(int[]) +CREATE AGGREGATE myaggp02a(*) (SFUNC = stfnp, STYPE = anyarray, + FINALFUNC = ffp, INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +-- N P +-- should CREATE +CREATE AGGREGATE myaggp03a(*) (SFUNC = stfp, STYPE = int4[], + FINALFUNC = ffp, INITCOND = '{}'); +CREATE AGGREGATE myaggp03b(*) (SFUNC = stfp, STYPE = int4[], + INITCOND = '{}'); +-- P P +-- should ERROR: we have no way to resolve S +CREATE AGGREGATE myaggp04a(*) (SFUNC = stfp, STYPE = anyarray, + FINALFUNC = ffp, INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +CREATE AGGREGATE myaggp04b(*) (SFUNC = stfp, STYPE = anyarray, + INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +-- Case2 (R = P) && ((B = P) || (B = N)) +-- ------------------------------------- +-- S tf1 B tf2 +-- ----------------------- +-- N N N N +-- should CREATE +CREATE AGGREGATE myaggp05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[], + FINALFUNC = ffp, INITCOND = '{}'); +-- N N N P +-- should CREATE +CREATE AGGREGATE myaggp06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[], + FINALFUNC = ffp, INITCOND = '{}'); +-- N N P N +-- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int) +CREATE AGGREGATE myaggp07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[], + FINALFUNC = ffp, INITCOND = '{}'); +ERROR: function tfnp(integer[], anyelement) does not exist +-- N N P P +-- should CREATE +CREATE AGGREGATE myaggp08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[], + FINALFUNC = ffp, INITCOND = '{}'); +-- N P N N +-- should CREATE +CREATE AGGREGATE myaggp09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[], + FINALFUNC = ffp, INITCOND = '{}'); +CREATE AGGREGATE myaggp09b(BASETYPE = int, SFUNC = tf1p, STYPE = int[], + INITCOND = '{}'); +-- N P N P +-- should CREATE +CREATE AGGREGATE myaggp10a(BASETYPE = int, SFUNC = tfp, STYPE = int[], + FINALFUNC = ffp, INITCOND = '{}'); +CREATE AGGREGATE myaggp10b(BASETYPE = int, SFUNC = tfp, STYPE = int[], + INITCOND = '{}'); +-- N P P N +-- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int) +CREATE AGGREGATE myaggp11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[], + FINALFUNC = ffp, INITCOND = '{}'); +ERROR: function tf1p(integer[], anyelement) does not exist +CREATE AGGREGATE myaggp11b(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[], + INITCOND = '{}'); +ERROR: function tf1p(integer[], anyelement) does not exist +-- N P P P +-- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement) +CREATE AGGREGATE myaggp12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[], + FINALFUNC = ffp, INITCOND = '{}'); +ERROR: function tfp(integer[], anyelement) does not exist +CREATE AGGREGATE myaggp12b(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[], + INITCOND = '{}'); +ERROR: function tfp(integer[], anyelement) does not exist +-- P N N N +-- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int) +CREATE AGGREGATE myaggp13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, + FINALFUNC = ffp, INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +-- P N N P +-- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement) +CREATE AGGREGATE myaggp14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, + FINALFUNC = ffp, INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +-- P N P N +-- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int) +CREATE AGGREGATE myaggp15a(BASETYPE = anyelement, SFUNC = tfnp, + STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); +ERROR: function tfnp(anyarray, anyelement) does not exist +-- P N P P +-- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement) +CREATE AGGREGATE myaggp16a(BASETYPE = anyelement, SFUNC = tf2p, + STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); +ERROR: function tf2p(anyarray, anyelement) does not exist +-- P P N N +-- should ERROR: we have no way to resolve S +CREATE AGGREGATE myaggp17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, + FINALFUNC = ffp, INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +CREATE AGGREGATE myaggp17b(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, + INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +-- P P N P +-- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement) +CREATE AGGREGATE myaggp18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, + FINALFUNC = ffp, INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +CREATE AGGREGATE myaggp18b(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, + INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +-- P P P N +-- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int) +CREATE AGGREGATE myaggp19a(BASETYPE = anyelement, SFUNC = tf1p, + STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); +ERROR: function tf1p(anyarray, anyelement) does not exist +CREATE AGGREGATE myaggp19b(BASETYPE = anyelement, SFUNC = tf1p, + STYPE = anyarray, INITCOND = '{}'); +ERROR: function tf1p(anyarray, anyelement) does not exist +-- P P P P +-- should CREATE +CREATE AGGREGATE myaggp20a(BASETYPE = anyelement, SFUNC = tfp, + STYPE = anyarray, FINALFUNC = ffp, INITCOND = '{}'); +CREATE AGGREGATE myaggp20b(BASETYPE = anyelement, SFUNC = tfp, + STYPE = anyarray, INITCOND = '{}'); +-- Case3 (R = N) && (B = A) +-- ------------------------ +-- S tf1 +-- ------- +-- N N +-- should CREATE +CREATE AGGREGATE myaggn01a(*) (SFUNC = stfnp, STYPE = int4[], + FINALFUNC = ffnp, INITCOND = '{}'); +CREATE AGGREGATE myaggn01b(*) (SFUNC = stfnp, STYPE = int4[], + INITCOND = '{}'); +-- P N +-- should ERROR: stfnp(anyarray) not matched by stfnp(int[]) +CREATE AGGREGATE myaggn02a(*) (SFUNC = stfnp, STYPE = anyarray, + FINALFUNC = ffnp, INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +CREATE AGGREGATE myaggn02b(*) (SFUNC = stfnp, STYPE = anyarray, + INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +-- N P +-- should CREATE +CREATE AGGREGATE myaggn03a(*) (SFUNC = stfp, STYPE = int4[], + FINALFUNC = ffnp, INITCOND = '{}'); +-- P P +-- should ERROR: ffnp(anyarray) not matched by ffnp(int[]) +CREATE AGGREGATE myaggn04a(*) (SFUNC = stfp, STYPE = anyarray, + FINALFUNC = ffnp, INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +-- Case4 (R = N) && ((B = P) || (B = N)) +-- ------------------------------------- +-- S tf1 B tf2 +-- ----------------------- +-- N N N N +-- should CREATE +CREATE AGGREGATE myaggn05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[], + FINALFUNC = ffnp, INITCOND = '{}'); +CREATE AGGREGATE myaggn05b(BASETYPE = int, SFUNC = tfnp, STYPE = int[], + INITCOND = '{}'); +-- N N N P +-- should CREATE +CREATE AGGREGATE myaggn06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[], + FINALFUNC = ffnp, INITCOND = '{}'); +CREATE AGGREGATE myaggn06b(BASETYPE = int, SFUNC = tf2p, STYPE = int[], + INITCOND = '{}'); +-- N N P N +-- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int) +CREATE AGGREGATE myaggn07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[], + FINALFUNC = ffnp, INITCOND = '{}'); +ERROR: function tfnp(integer[], anyelement) does not exist +CREATE AGGREGATE myaggn07b(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[], + INITCOND = '{}'); +ERROR: function tfnp(integer[], anyelement) does not exist +-- N N P P +-- should CREATE +CREATE AGGREGATE myaggn08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[], + FINALFUNC = ffnp, INITCOND = '{}'); +CREATE AGGREGATE myaggn08b(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[], + INITCOND = '{}'); +-- N P N N +-- should CREATE +CREATE AGGREGATE myaggn09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[], + FINALFUNC = ffnp, INITCOND = '{}'); +-- N P N P +-- should CREATE +CREATE AGGREGATE myaggn10a(BASETYPE = int, SFUNC = tfp, STYPE = int[], + FINALFUNC = ffnp, INITCOND = '{}'); +-- N P P N +-- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int) +CREATE AGGREGATE myaggn11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[], + FINALFUNC = ffnp, INITCOND = '{}'); +ERROR: function tf1p(integer[], anyelement) does not exist +-- N P P P +-- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement) +CREATE AGGREGATE myaggn12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[], + FINALFUNC = ffnp, INITCOND = '{}'); +ERROR: function tfp(integer[], anyelement) does not exist +-- P N N N +-- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int) +CREATE AGGREGATE myaggn13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, + FINALFUNC = ffnp, INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +CREATE AGGREGATE myaggn13b(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, + INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +-- P N N P +-- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement) +CREATE AGGREGATE myaggn14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, + FINALFUNC = ffnp, INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +CREATE AGGREGATE myaggn14b(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, + INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +-- P N P N +-- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int) +CREATE AGGREGATE myaggn15a(BASETYPE = anyelement, SFUNC = tfnp, + STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}'); +ERROR: function tfnp(anyarray, anyelement) does not exist +CREATE AGGREGATE myaggn15b(BASETYPE = anyelement, SFUNC = tfnp, + STYPE = anyarray, INITCOND = '{}'); +ERROR: function tfnp(anyarray, anyelement) does not exist +-- P N P P +-- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement) +CREATE AGGREGATE myaggn16a(BASETYPE = anyelement, SFUNC = tf2p, + STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}'); +ERROR: function tf2p(anyarray, anyelement) does not exist +CREATE AGGREGATE myaggn16b(BASETYPE = anyelement, SFUNC = tf2p, + STYPE = anyarray, INITCOND = '{}'); +ERROR: function tf2p(anyarray, anyelement) does not exist +-- P P N N +-- should ERROR: ffnp(anyarray) not matched by ffnp(int[]) +CREATE AGGREGATE myaggn17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, + FINALFUNC = ffnp, INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +-- P P N P +-- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement) +CREATE AGGREGATE myaggn18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, + FINALFUNC = ffnp, INITCOND = '{}'); +ERROR: cannot determine transition data type +DETAIL: A result of type anyarray requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. +-- P P P N +-- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int) +CREATE AGGREGATE myaggn19a(BASETYPE = anyelement, SFUNC = tf1p, + STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}'); +ERROR: function tf1p(anyarray, anyelement) does not exist +-- P P P P +-- should ERROR: ffnp(anyarray) not matched by ffnp(int[]) +CREATE AGGREGATE myaggn20a(BASETYPE = anyelement, SFUNC = tfp, + STYPE = anyarray, FINALFUNC = ffnp, INITCOND = '{}'); +ERROR: function ffnp(anyarray) does not exist +-- multi-arg polymorphic +CREATE AGGREGATE mysum2(anyelement,anyelement) (SFUNC = sum3, + STYPE = anyelement, INITCOND = '0'); +-- create test data for polymorphic aggregates +create temp table t(f1 int, f2 int[], f3 text); +insert into t values(1,array[1],'a'); +insert into t values(1,array[11],'b'); +insert into t values(1,array[111],'c'); +insert into t values(2,array[2],'a'); +insert into t values(2,array[22],'b'); +insert into t values(2,array[222],'c'); +insert into t values(3,array[3],'a'); +insert into t values(3,array[3],'b'); +-- test the successfully created polymorphic aggregates +select f3, myaggp01a(*) from t group by f3 order by f3; + f3 | myaggp01a +----+----------- + a | {} + b | {} + c | {} +(3 rows) + +select f3, myaggp03a(*) from t group by f3 order by f3; + f3 | myaggp03a +----+----------- + a | {} + b | {} + c | {} +(3 rows) + +select f3, myaggp03b(*) from t group by f3 order by f3; + f3 | myaggp03b +----+----------- + a | {} + b | {} + c | {} +(3 rows) + +select f3, myaggp05a(f1) from t group by f3 order by f3; + f3 | myaggp05a +----+----------- + a | {1,2,3} + b | {1,2,3} + c | {1,2} +(3 rows) + +select f3, myaggp06a(f1) from t group by f3 order by f3; + f3 | myaggp06a +----+----------- + a | {} + b | {} + c | {} +(3 rows) + +select f3, myaggp08a(f1) from t group by f3 order by f3; + f3 | myaggp08a +----+----------- + a | {} + b | {} + c | {} +(3 rows) + +select f3, myaggp09a(f1) from t group by f3 order by f3; + f3 | myaggp09a +----+----------- + a | {} + b | {} + c | {} +(3 rows) + +select f3, myaggp09b(f1) from t group by f3 order by f3; + f3 | myaggp09b +----+----------- + a | {} + b | {} + c | {} +(3 rows) + +select f3, myaggp10a(f1) from t group by f3 order by f3; + f3 | myaggp10a +----+----------- + a | {1,2,3} + b | {1,2,3} + c | {1,2} +(3 rows) + +select f3, myaggp10b(f1) from t group by f3 order by f3; + f3 | myaggp10b +----+----------- + a | {1,2,3} + b | {1,2,3} + c | {1,2} +(3 rows) + +select f3, myaggp20a(f1) from t group by f3 order by f3; + f3 | myaggp20a +----+----------- + a | {1,2,3} + b | {1,2,3} + c | {1,2} +(3 rows) + +select f3, myaggp20b(f1) from t group by f3 order by f3; + f3 | myaggp20b +----+----------- + a | {1,2,3} + b | {1,2,3} + c | {1,2} +(3 rows) + +select f3, myaggn01a(*) from t group by f3 order by f3; + f3 | myaggn01a +----+----------- + a | {} + b | {} + c | {} +(3 rows) + +select f3, myaggn01b(*) from t group by f3 order by f3; + f3 | myaggn01b +----+----------- + a | {} + b | {} + c | {} +(3 rows) + +select f3, myaggn03a(*) from t group by f3 order by f3; + f3 | myaggn03a +----+----------- + a | {} + b | {} + c | {} +(3 rows) + +select f3, myaggn05a(f1) from t group by f3 order by f3; + f3 | myaggn05a +----+----------- + a | {1,2,3} + b | {1,2,3} + c | {1,2} +(3 rows) + +select f3, myaggn05b(f1) from t group by f3 order by f3; + f3 | myaggn05b +----+----------- + a | {1,2,3} + b | {1,2,3} + c | {1,2} +(3 rows) + +select f3, myaggn06a(f1) from t group by f3 order by f3; + f3 | myaggn06a +----+----------- + a | {} + b | {} + c | {} +(3 rows) + +select f3, myaggn06b(f1) from t group by f3 order by f3; + f3 | myaggn06b +----+----------- + a | {} + b | {} + c | {} +(3 rows) + +select f3, myaggn08a(f1) from t group by f3 order by f3; + f3 | myaggn08a +----+----------- + a | {} + b | {} + c | {} +(3 rows) + +select f3, myaggn08b(f1) from t group by f3 order by f3; + f3 | myaggn08b +----+----------- + a | {} + b | {} + c | {} +(3 rows) + +select f3, myaggn09a(f1) from t group by f3 order by f3; + f3 | myaggn09a +----+----------- + a | {} + b | {} + c | {} +(3 rows) + +select f3, myaggn10a(f1) from t group by f3 order by f3; + f3 | myaggn10a +----+----------- + a | {1,2,3} + b | {1,2,3} + c | {1,2} +(3 rows) + +select mysum2(f1, f1 + 1) from t; + mysum2 +-------- + 38 +(1 row) + +-- test inlining of polymorphic SQL functions +create function bleat(int) returns int as $$ +begin + raise notice 'bleat %', $1; + return $1; +end$$ language plpgsql; +create function sql_if(bool, anyelement, anyelement) returns anyelement as $$ +select case when $1 then $2 else $3 end $$ language sql; +-- Note this would fail with integer overflow, never mind wrong bleat() output, +-- if the CASE expression were not successfully inlined +select f1, sql_if(f1 > 0, bleat(f1), bleat(f1 + 1)) from int4_tbl; +NOTICE: bleat 1 +NOTICE: bleat 123456 +NOTICE: bleat -123455 +NOTICE: bleat 2147483647 +NOTICE: bleat -2147483646 + f1 | sql_if +-------------+------------- + 0 | 1 + 123456 | 123456 + -123456 | -123455 + 2147483647 | 2147483647 + -2147483647 | -2147483646 +(5 rows) + +select q2, sql_if(q2 > 0, q2, q2 + 1) from int8_tbl; + q2 | sql_if +-------------------+------------------- + 456 | 456 + 4567890123456789 | 4567890123456789 + 123 | 123 + 4567890123456789 | 4567890123456789 + -4567890123456789 | -4567890123456788 +(5 rows) + +-- another sort of polymorphic aggregate +CREATE AGGREGATE array_larger_accum (anyarray) +( + sfunc = array_larger, + stype = anyarray, + initcond = '{}' +); +SELECT array_larger_accum(i) +FROM (VALUES (ARRAY[1,2]), (ARRAY[3,4])) as t(i); + array_larger_accum +-------------------- + {3,4} +(1 row) + +SELECT array_larger_accum(i) +FROM (VALUES (ARRAY[row(1,2),row(3,4)]), (ARRAY[row(5,6),row(7,8)])) as t(i); + array_larger_accum +-------------------- + {"(5,6)","(7,8)"} +(1 row) + +-- another kind of polymorphic aggregate +create function add_group(grp anyarray, ad anyelement, size integer) + returns anyarray + as $$ +begin + if grp is null then + return array[ad]; + end if; + if array_upper(grp, 1) < size then + return grp || ad; + end if; + return grp; +end; +$$ + language plpgsql immutable; +create aggregate build_group(anyelement, integer) ( + SFUNC = add_group, + STYPE = anyarray +); +select build_group(q1,3) from int8_tbl; + build_group +---------------------------- + {123,123,4567890123456789} +(1 row) + +-- this should fail because stype isn't compatible with arg +create aggregate build_group(int8, integer) ( + SFUNC = add_group, + STYPE = int2[] +); +ERROR: function add_group(smallint[], bigint, integer) does not exist +-- but we can make a non-poly agg from a poly sfunc if types are OK +create aggregate build_group(int8, integer) ( + SFUNC = add_group, + STYPE = int8[] +); +-- check proper resolution of data types for polymorphic transfn/finalfn +create function first_el_transfn(anyarray, anyelement) returns anyarray as +'select $1 || $2' language sql immutable; +create function first_el(anyarray) returns anyelement as +'select $1[1]' language sql strict immutable; +create aggregate first_el_agg_f8(float8) ( + SFUNC = array_append, + STYPE = float8[], + FINALFUNC = first_el +); +create aggregate first_el_agg_any(anyelement) ( + SFUNC = first_el_transfn, + STYPE = anyarray, + FINALFUNC = first_el +); +select first_el_agg_f8(x::float8) from generate_series(1,10) x; + first_el_agg_f8 +----------------- + 1 +(1 row) + +select first_el_agg_any(x) from generate_series(1,10) x; + first_el_agg_any +------------------ + 1 +(1 row) + +select first_el_agg_f8(x::float8) over(order by x) from generate_series(1,10) x; + first_el_agg_f8 +----------------- + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 +(10 rows) + +select first_el_agg_any(x) over(order by x) from generate_series(1,10) x; + first_el_agg_any +------------------ + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 +(10 rows) + +-- check that we can apply functions taking ANYARRAY to pg_stats +select distinct array_ndims(histogram_bounds) from pg_stats +where histogram_bounds is not null; + array_ndims +------------- + 1 +(1 row) + +-- such functions must protect themselves if varying element type isn't OK +-- (WHERE clause here is to avoid possibly getting a collation error instead) +select max(histogram_bounds) from pg_stats where tablename = 'pg_am'; +ERROR: cannot compare arrays of different element types +-- another corner case is the input functions for polymorphic pseudotypes +select array_in('{1,2,3}','int4'::regtype,-1); -- this has historically worked + array_in +---------- + {1,2,3} +(1 row) + +select * from array_in('{1,2,3}','int4'::regtype,-1); -- this not +ERROR: function "array_in" in FROM has unsupported return type anyarray +LINE 1: select * from array_in('{1,2,3}','int4'::regtype,-1); + ^ +select anyrange_in('[10,20)','int4range'::regtype,-1); +ERROR: cannot accept a value of type anyrange +-- test variadic polymorphic functions +create function myleast(variadic anyarray) returns anyelement as $$ + select min($1[i]) from generate_subscripts($1,1) g(i) +$$ language sql immutable strict; +select myleast(10, 1, 20, 33); + myleast +--------- + 1 +(1 row) + +select myleast(1.1, 0.22, 0.55); + myleast +--------- + 0.22 +(1 row) + +select myleast('z'::text); + myleast +--------- + z +(1 row) + +select myleast(); -- fail +ERROR: function myleast() does not exist +LINE 1: select myleast(); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +-- test with variadic call parameter +select myleast(variadic array[1,2,3,4,-1]); + myleast +--------- + -1 +(1 row) + +select myleast(variadic array[1.1, -5.5]); + myleast +--------- + -5.5 +(1 row) + +--test with empty variadic call parameter +select myleast(variadic array[]::int[]); + myleast +--------- + +(1 row) + +-- an example with some ordinary arguments too +create function concat(text, variadic anyarray) returns text as $$ + select array_to_string($2, $1); +$$ language sql immutable strict; +select concat('%', 1, 2, 3, 4, 5); + concat +----------- + 1%2%3%4%5 +(1 row) + +select concat('|', 'a'::text, 'b', 'c'); + concat +-------- + a|b|c +(1 row) + +select concat('|', variadic array[1,2,33]); + concat +-------- + 1|2|33 +(1 row) + +select concat('|', variadic array[]::int[]); + concat +-------- + +(1 row) + +drop function concat(text, anyarray); +-- mix variadic with anyelement +create function formarray(anyelement, variadic anyarray) returns anyarray as $$ + select array_prepend($1, $2); +$$ language sql immutable strict; +select formarray(1,2,3,4,5); + formarray +------------- + {1,2,3,4,5} +(1 row) + +select formarray(1.1, variadic array[1.2,55.5]); + formarray +---------------- + {1.1,1.2,55.5} +(1 row) + +select formarray(1.1, array[1.2,55.5]); -- fail without variadic +ERROR: function formarray(numeric, numeric[]) does not exist +LINE 1: select formarray(1.1, array[1.2,55.5]); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +select formarray(1, 'x'::text); -- fail, type mismatch +ERROR: function formarray(integer, text) does not exist +LINE 1: select formarray(1, 'x'::text); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +select formarray(1, variadic array['x'::text]); -- fail, type mismatch +ERROR: function formarray(integer, text[]) does not exist +LINE 1: select formarray(1, variadic array['x'::text]); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function formarray(anyelement, variadic anyarray); +-- test pg_typeof() function +select pg_typeof(null); -- unknown + pg_typeof +----------- + unknown +(1 row) + +select pg_typeof(0); -- integer + pg_typeof +----------- + integer +(1 row) + +select pg_typeof(0.0); -- numeric + pg_typeof +----------- + numeric +(1 row) + +select pg_typeof(1+1 = 2); -- boolean + pg_typeof +----------- + boolean +(1 row) + +select pg_typeof('x'); -- unknown + pg_typeof +----------- + unknown +(1 row) + +select pg_typeof('' || ''); -- text + pg_typeof +----------- + text +(1 row) + +select pg_typeof(pg_typeof(0)); -- regtype + pg_typeof +----------- + regtype +(1 row) + +select pg_typeof(array[1.2,55.5]); -- numeric[] + pg_typeof +----------- + numeric[] +(1 row) + +select pg_typeof(myleast(10, 1, 20, 33)); -- polymorphic input + pg_typeof +----------- + integer +(1 row) + +-- test functions with default parameters +-- test basic functionality +create function dfunc(a int = 1, int = 2) returns int as $$ + select $1 + $2; +$$ language sql; +select dfunc(); + dfunc +------- + 3 +(1 row) + +select dfunc(10); + dfunc +------- + 12 +(1 row) + +select dfunc(10, 20); + dfunc +------- + 30 +(1 row) + +select dfunc(10, 20, 30); -- fail +ERROR: function dfunc(integer, integer, integer) does not exist +LINE 1: select dfunc(10, 20, 30); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function dfunc(); -- fail +ERROR: function dfunc() does not exist +drop function dfunc(int); -- fail +ERROR: function dfunc(integer) does not exist +drop function dfunc(int, int); -- ok +-- fail: defaults must be at end of argument list +create function dfunc(a int = 1, b int) returns int as $$ + select $1 + $2; +$$ language sql; +ERROR: input parameters after one with a default value must also have defaults +-- however, this should work: +create function dfunc(a int = 1, out sum int, b int = 2) as $$ + select $1 + $2; +$$ language sql; +select dfunc(); + dfunc +------- + 3 +(1 row) + +-- verify it lists properly +\df dfunc + List of functions + Schema | Name | Result data type | Argument data types | Type +--------+-------+------------------+-----------------------------------------------------------+------ + public | dfunc | integer | a integer DEFAULT 1, OUT sum integer, b integer DEFAULT 2 | func +(1 row) + +drop function dfunc(int, int); +-- check implicit coercion +create function dfunc(a int DEFAULT 1.0, int DEFAULT '-1') returns int as $$ + select $1 + $2; +$$ language sql; +select dfunc(); + dfunc +------- + 0 +(1 row) + +create function dfunc(a text DEFAULT 'Hello', b text DEFAULT 'World') returns text as $$ + select $1 || ', ' || $2; +$$ language sql; +select dfunc(); -- fail: which dfunc should be called? int or text +ERROR: function dfunc() is not unique +LINE 1: select dfunc(); + ^ +HINT: Could not choose a best candidate function. You might need to add explicit type casts. +select dfunc('Hi'); -- ok + dfunc +----------- + Hi, World +(1 row) + +select dfunc('Hi', 'City'); -- ok + dfunc +---------- + Hi, City +(1 row) + +select dfunc(0); -- ok + dfunc +------- + -1 +(1 row) + +select dfunc(10, 20); -- ok + dfunc +------- + 30 +(1 row) + +drop function dfunc(int, int); +drop function dfunc(text, text); +create function dfunc(int = 1, int = 2) returns int as $$ + select 2; +$$ language sql; +create function dfunc(int = 1, int = 2, int = 3, int = 4) returns int as $$ + select 4; +$$ language sql; +-- Now, dfunc(nargs = 2) and dfunc(nargs = 4) are ambiguous when called +-- with 0 to 2 arguments. +select dfunc(); -- fail +ERROR: function dfunc() is not unique +LINE 1: select dfunc(); + ^ +HINT: Could not choose a best candidate function. You might need to add explicit type casts. +select dfunc(1); -- fail +ERROR: function dfunc(integer) is not unique +LINE 1: select dfunc(1); + ^ +HINT: Could not choose a best candidate function. You might need to add explicit type casts. +select dfunc(1, 2); -- fail +ERROR: function dfunc(integer, integer) is not unique +LINE 1: select dfunc(1, 2); + ^ +HINT: Could not choose a best candidate function. You might need to add explicit type casts. +select dfunc(1, 2, 3); -- ok + dfunc +------- + 4 +(1 row) + +select dfunc(1, 2, 3, 4); -- ok + dfunc +------- + 4 +(1 row) + +drop function dfunc(int, int); +drop function dfunc(int, int, int, int); +-- default values are not allowed for output parameters +create function dfunc(out int = 20) returns int as $$ + select 1; +$$ language sql; +ERROR: only input parameters can have default values +-- polymorphic parameter test +create function dfunc(anyelement = 'World'::text) returns text as $$ + select 'Hello, ' || $1::text; +$$ language sql; +select dfunc(); + dfunc +-------------- + Hello, World +(1 row) + +select dfunc(0); + dfunc +---------- + Hello, 0 +(1 row) + +select dfunc(to_date('20081215','YYYYMMDD')); + dfunc +------------------- + Hello, 12-15-2008 +(1 row) + +select dfunc('City'::text); + dfunc +------------- + Hello, City +(1 row) + +drop function dfunc(anyelement); +-- check defaults for variadics +create function dfunc(a variadic int[]) returns int as +$$ select array_upper($1, 1) $$ language sql; +select dfunc(); -- fail +ERROR: function dfunc() does not exist +LINE 1: select dfunc(); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +select dfunc(10); + dfunc +------- + 1 +(1 row) + +select dfunc(10,20); + dfunc +------- + 2 +(1 row) + +create or replace function dfunc(a variadic int[] default array[]::int[]) returns int as +$$ select array_upper($1, 1) $$ language sql; +select dfunc(); -- now ok + dfunc +------- + +(1 row) + +select dfunc(10); + dfunc +------- + 1 +(1 row) + +select dfunc(10,20); + dfunc +------- + 2 +(1 row) + +-- can't remove the default once it exists +create or replace function dfunc(a variadic int[]) returns int as +$$ select array_upper($1, 1) $$ language sql; +ERROR: cannot remove parameter defaults from existing function +HINT: Use DROP FUNCTION dfunc(integer[]) first. +\df dfunc + List of functions + Schema | Name | Result data type | Argument data types | Type +--------+-------+------------------+-------------------------------------------------+------ + public | dfunc | integer | VARIADIC a integer[] DEFAULT ARRAY[]::integer[] | func +(1 row) + +drop function dfunc(a variadic int[]); +-- Ambiguity should be reported only if there's not a better match available +create function dfunc(int = 1, int = 2, int = 3) returns int as $$ + select 3; +$$ language sql; +create function dfunc(int = 1, int = 2) returns int as $$ + select 2; +$$ language sql; +create function dfunc(text) returns text as $$ + select $1; +$$ language sql; +-- dfunc(narg=2) and dfunc(narg=3) are ambiguous +select dfunc(1); -- fail +ERROR: function dfunc(integer) is not unique +LINE 1: select dfunc(1); + ^ +HINT: Could not choose a best candidate function. You might need to add explicit type casts. +-- but this works since the ambiguous functions aren't preferred anyway +select dfunc('Hi'); + dfunc +------- + Hi +(1 row) + +drop function dfunc(int, int, int); +drop function dfunc(int, int); +drop function dfunc(text); +-- +-- Tests for named- and mixed-notation function calling +-- +create function dfunc(a int, b int, c int = 0, d int = 0) + returns table (a int, b int, c int, d int) as $$ + select $1, $2, $3, $4; +$$ language sql; +select (dfunc(10,20,30)).*; + a | b | c | d +----+----+----+--- + 10 | 20 | 30 | 0 +(1 row) + +select (dfunc(a := 10, b := 20, c := 30)).*; + a | b | c | d +----+----+----+--- + 10 | 20 | 30 | 0 +(1 row) + +select * from dfunc(a := 10, b := 20); + a | b | c | d +----+----+---+--- + 10 | 20 | 0 | 0 +(1 row) + +select * from dfunc(b := 10, a := 20); + a | b | c | d +----+----+---+--- + 20 | 10 | 0 | 0 +(1 row) + +select * from dfunc(0); -- fail +ERROR: function dfunc(integer) does not exist +LINE 1: select * from dfunc(0); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +select * from dfunc(1,2); + a | b | c | d +---+---+---+--- + 1 | 2 | 0 | 0 +(1 row) + +select * from dfunc(1,2,c := 3); + a | b | c | d +---+---+---+--- + 1 | 2 | 3 | 0 +(1 row) + +select * from dfunc(1,2,d := 3); + a | b | c | d +---+---+---+--- + 1 | 2 | 0 | 3 +(1 row) + +select * from dfunc(x := 20, b := 10, x := 30); -- fail, duplicate name +ERROR: argument name "x" used more than once +LINE 1: select * from dfunc(x := 20, b := 10, x := 30); + ^ +select * from dfunc(10, b := 20, 30); -- fail, named args must be last +ERROR: positional argument cannot follow named argument +LINE 1: select * from dfunc(10, b := 20, 30); + ^ +select * from dfunc(x := 10, b := 20, c := 30); -- fail, unknown param +ERROR: function dfunc(x => integer, b => integer, c => integer) does not exist +LINE 1: select * from dfunc(x := 10, b := 20, c := 30); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +select * from dfunc(10, 10, a := 20); -- fail, a overlaps positional parameter +ERROR: function dfunc(integer, integer, a => integer) does not exist +LINE 1: select * from dfunc(10, 10, a := 20); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +select * from dfunc(1,c := 2,d := 3); -- fail, no value for b +ERROR: function dfunc(integer, c => integer, d => integer) does not exist +LINE 1: select * from dfunc(1,c := 2,d := 3); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function dfunc(int, int, int, int); +-- test with different parameter types +create function dfunc(a varchar, b numeric, c date = current_date) + returns table (a varchar, b numeric, c date) as $$ + select $1, $2, $3; +$$ language sql; +select (dfunc('Hello World', 20, '2009-07-25'::date)).*; + a | b | c +-------------+----+------------ + Hello World | 20 | 07-25-2009 +(1 row) + +select * from dfunc('Hello World', 20, '2009-07-25'::date); + a | b | c +-------------+----+------------ + Hello World | 20 | 07-25-2009 +(1 row) + +select * from dfunc(c := '2009-07-25'::date, a := 'Hello World', b := 20); + a | b | c +-------------+----+------------ + Hello World | 20 | 07-25-2009 +(1 row) + +select * from dfunc('Hello World', b := 20, c := '2009-07-25'::date); + a | b | c +-------------+----+------------ + Hello World | 20 | 07-25-2009 +(1 row) + +select * from dfunc('Hello World', c := '2009-07-25'::date, b := 20); + a | b | c +-------------+----+------------ + Hello World | 20 | 07-25-2009 +(1 row) + +select * from dfunc('Hello World', c := 20, b := '2009-07-25'::date); -- fail +ERROR: function dfunc(unknown, c => integer, b => date) does not exist +LINE 1: select * from dfunc('Hello World', c := 20, b := '2009-07-25... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function dfunc(varchar, numeric, date); +-- test out parameters with named params +create function dfunc(a varchar = 'def a', out _a varchar, c numeric = NULL, out _c numeric) +returns record as $$ + select $1, $2; +$$ language sql; +select (dfunc()).*; + _a | _c +-------+---- + def a | +(1 row) + +select * from dfunc(); + _a | _c +-------+---- + def a | +(1 row) + +select * from dfunc('Hello', 100); + _a | _c +-------+----- + Hello | 100 +(1 row) + +select * from dfunc(a := 'Hello', c := 100); + _a | _c +-------+----- + Hello | 100 +(1 row) + +select * from dfunc(c := 100, a := 'Hello'); + _a | _c +-------+----- + Hello | 100 +(1 row) + +select * from dfunc('Hello'); + _a | _c +-------+---- + Hello | +(1 row) + +select * from dfunc('Hello', c := 100); + _a | _c +-------+----- + Hello | 100 +(1 row) + +select * from dfunc(c := 100); + _a | _c +-------+----- + def a | 100 +(1 row) + +-- fail, can no longer change an input parameter's name +create or replace function dfunc(a varchar = 'def a', out _a varchar, x numeric = NULL, out _c numeric) +returns record as $$ + select $1, $2; +$$ language sql; +ERROR: cannot change name of input parameter "c" +HINT: Use DROP FUNCTION dfunc(character varying,numeric) first. +create or replace function dfunc(a varchar = 'def a', out _a varchar, numeric = NULL, out _c numeric) +returns record as $$ + select $1, $2; +$$ language sql; +ERROR: cannot change name of input parameter "c" +HINT: Use DROP FUNCTION dfunc(character varying,numeric) first. +drop function dfunc(varchar, numeric); +--fail, named parameters are not unique +create function testpolym(a int, a int) returns int as $$ select 1;$$ language sql; +ERROR: parameter name "a" used more than once +create function testpolym(int, out a int, out a int) returns int as $$ select 1;$$ language sql; +ERROR: parameter name "a" used more than once +create function testpolym(out a int, inout a int) returns int as $$ select 1;$$ language sql; +ERROR: parameter name "a" used more than once +create function testpolym(a int, inout a int) returns int as $$ select 1;$$ language sql; +ERROR: parameter name "a" used more than once +-- valid +create function testpolym(a int, out a int) returns int as $$ select $1;$$ language sql; +select testpolym(37); + testpolym +----------- + 37 +(1 row) + +drop function testpolym(int); +create function testpolym(a int) returns table(a int) as $$ select $1;$$ language sql; +select * from testpolym(37); + a +---- + 37 +(1 row) + +drop function testpolym(int); +-- test polymorphic params and defaults +create function dfunc(a anyelement, b anyelement = null, flag bool = true) +returns anyelement as $$ + select case when $3 then $1 else $2 end; +$$ language sql; +select dfunc(1,2); + dfunc +------- + 1 +(1 row) + +select dfunc('a'::text, 'b'); -- positional notation with default + dfunc +------- + a +(1 row) + +select dfunc(a := 1, b := 2); + dfunc +------- + 1 +(1 row) + +select dfunc(a := 'a'::text, b := 'b'); + dfunc +------- + a +(1 row) + +select dfunc(a := 'a'::text, b := 'b', flag := false); -- named notation + dfunc +------- + b +(1 row) + +select dfunc(b := 'b'::text, a := 'a'); -- named notation with default + dfunc +------- + a +(1 row) + +select dfunc(a := 'a'::text, flag := true); -- named notation with default + dfunc +------- + a +(1 row) + +select dfunc(a := 'a'::text, flag := false); -- named notation with default + dfunc +------- + +(1 row) + +select dfunc(b := 'b'::text, a := 'a', flag := true); -- named notation + dfunc +------- + a +(1 row) + +select dfunc('a'::text, 'b', false); -- full positional notation + dfunc +------- + b +(1 row) + +select dfunc('a'::text, 'b', flag := false); -- mixed notation + dfunc +------- + b +(1 row) + +select dfunc('a'::text, 'b', true); -- full positional notation + dfunc +------- + a +(1 row) + +select dfunc('a'::text, 'b', flag := true); -- mixed notation + dfunc +------- + a +(1 row) + +-- ansi/sql syntax +select dfunc(a => 1, b => 2); + dfunc +------- + 1 +(1 row) + +select dfunc(a => 'a'::text, b => 'b'); + dfunc +------- + a +(1 row) + +select dfunc(a => 'a'::text, b => 'b', flag => false); -- named notation + dfunc +------- + b +(1 row) + +select dfunc(b => 'b'::text, a => 'a'); -- named notation with default + dfunc +------- + a +(1 row) + +select dfunc(a => 'a'::text, flag => true); -- named notation with default + dfunc +------- + a +(1 row) + +select dfunc(a => 'a'::text, flag => false); -- named notation with default + dfunc +------- + +(1 row) + +select dfunc(b => 'b'::text, a => 'a', flag => true); -- named notation + dfunc +------- + a +(1 row) + +select dfunc('a'::text, 'b', false); -- full positional notation + dfunc +------- + b +(1 row) + +select dfunc('a'::text, 'b', flag => false); -- mixed notation + dfunc +------- + b +(1 row) + +select dfunc('a'::text, 'b', true); -- full positional notation + dfunc +------- + a +(1 row) + +select dfunc('a'::text, 'b', flag => true); -- mixed notation + dfunc +------- + a +(1 row) + +-- this tests lexer edge cases around => +select dfunc(a =>-1); + dfunc +------- + -1 +(1 row) + +select dfunc(a =>+1); + dfunc +------- + 1 +(1 row) + +select dfunc(a =>/**/1); + dfunc +------- + 1 +(1 row) + +select dfunc(a =>--comment to be removed by psql + 1); + dfunc +------- + 1 +(1 row) + +-- need DO to protect the -- from psql +do $$ + declare r integer; + begin + select dfunc(a=>-- comment + 1) into r; + raise info 'r = %', r; + end; +$$; +INFO: r = 1 +-- check reverse-listing of named-arg calls +CREATE VIEW dfview AS + SELECT q1, q2, + dfunc(q1,q2, flag := q1>q2) as c3, + dfunc(q1, flag := q1<q2, b := q2) as c4 + FROM int8_tbl; +select * from dfview; + q1 | q2 | c3 | c4 +------------------+-------------------+------------------+------------------- + 123 | 456 | 456 | 123 + 123 | 4567890123456789 | 4567890123456789 | 123 + 4567890123456789 | 123 | 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 | 4567890123456789 | -4567890123456789 +(5 rows) + +\d+ dfview + View "public.dfview" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+--------+-----------+----------+---------+---------+------------- + q1 | bigint | | | | plain | + q2 | bigint | | | | plain | + c3 | bigint | | | | plain | + c4 | bigint | | | | plain | +View definition: + SELECT int8_tbl.q1, + int8_tbl.q2, + dfunc(int8_tbl.q1, int8_tbl.q2, flag => int8_tbl.q1 > int8_tbl.q2) AS c3, + dfunc(int8_tbl.q1, flag => int8_tbl.q1 < int8_tbl.q2, b => int8_tbl.q2) AS c4 + FROM int8_tbl; + +drop view dfview; +drop function dfunc(anyelement, anyelement, bool); +-- +-- Tests for ANYCOMPATIBLE polymorphism family +-- +create function anyctest(anycompatible, anycompatible) +returns anycompatible as $$ + select greatest($1, $2) +$$ language sql; +select x, pg_typeof(x) from anyctest(11, 12) x; + x | pg_typeof +----+----------- + 12 | integer +(1 row) + +select x, pg_typeof(x) from anyctest(11, 12.3) x; + x | pg_typeof +------+----------- + 12.3 | numeric +(1 row) + +select x, pg_typeof(x) from anyctest(11, point(1,2)) x; -- fail +ERROR: function anyctest(integer, point) does not exist +LINE 1: select x, pg_typeof(x) from anyctest(11, point(1,2)) x; + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +select x, pg_typeof(x) from anyctest('11', '12.3') x; -- defaults to text + x | pg_typeof +------+----------- + 12.3 | text +(1 row) + +drop function anyctest(anycompatible, anycompatible); +create function anyctest(anycompatible, anycompatible) +returns anycompatiblearray as $$ + select array[$1, $2] +$$ language sql; +select x, pg_typeof(x) from anyctest(11, 12) x; + x | pg_typeof +---------+----------- + {11,12} | integer[] +(1 row) + +select x, pg_typeof(x) from anyctest(11, 12.3) x; + x | pg_typeof +-----------+----------- + {11,12.3} | numeric[] +(1 row) + +select x, pg_typeof(x) from anyctest(11, array[1,2]) x; -- fail +ERROR: function anyctest(integer, integer[]) does not exist +LINE 1: select x, pg_typeof(x) from anyctest(11, array[1,2]) x; + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function anyctest(anycompatible, anycompatible); +create function anyctest(anycompatible, anycompatiblearray) +returns anycompatiblearray as $$ + select array[$1] || $2 +$$ language sql; +select x, pg_typeof(x) from anyctest(11, array[12]) x; + x | pg_typeof +---------+----------- + {11,12} | integer[] +(1 row) + +select x, pg_typeof(x) from anyctest(11, array[12.3]) x; + x | pg_typeof +-----------+----------- + {11,12.3} | numeric[] +(1 row) + +select x, pg_typeof(x) from anyctest(12.3, array[13]) x; + x | pg_typeof +-----------+----------- + {12.3,13} | numeric[] +(1 row) + +select x, pg_typeof(x) from anyctest(12.3, '{13,14.4}') x; + x | pg_typeof +----------------+----------- + {12.3,13,14.4} | numeric[] +(1 row) + +select x, pg_typeof(x) from anyctest(11, array[point(1,2)]) x; -- fail +ERROR: function anyctest(integer, point[]) does not exist +LINE 1: select x, pg_typeof(x) from anyctest(11, array[point(1,2)]) ... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +select x, pg_typeof(x) from anyctest(11, 12) x; -- fail +ERROR: function anyctest(integer, integer) does not exist +LINE 1: select x, pg_typeof(x) from anyctest(11, 12) x; + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function anyctest(anycompatible, anycompatiblearray); +create function anyctest(anycompatible, anycompatiblerange) +returns anycompatiblerange as $$ + select $2 +$$ language sql; +select x, pg_typeof(x) from anyctest(11, int4range(4,7)) x; + x | pg_typeof +-------+----------- + [4,7) | int4range +(1 row) + +select x, pg_typeof(x) from anyctest(11, numrange(4,7)) x; + x | pg_typeof +-------+----------- + [4,7) | numrange +(1 row) + +select x, pg_typeof(x) from anyctest(11, 12) x; -- fail +ERROR: function anyctest(integer, integer) does not exist +LINE 1: select x, pg_typeof(x) from anyctest(11, 12) x; + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +select x, pg_typeof(x) from anyctest(11.2, int4range(4,7)) x; -- fail +ERROR: function anyctest(numeric, int4range) does not exist +LINE 1: select x, pg_typeof(x) from anyctest(11.2, int4range(4,7)) x... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +select x, pg_typeof(x) from anyctest(11.2, '[4,7)') x; -- fail +ERROR: could not determine polymorphic type anycompatiblerange because input has type unknown +drop function anyctest(anycompatible, anycompatiblerange); +create function anyctest(anycompatiblerange, anycompatiblerange) +returns anycompatible as $$ + select lower($1) + upper($2) +$$ language sql; +select x, pg_typeof(x) from anyctest(int4range(11,12), int4range(4,7)) x; + x | pg_typeof +----+----------- + 18 | integer +(1 row) + +select x, pg_typeof(x) from anyctest(int4range(11,12), numrange(4,7)) x; -- fail +ERROR: function anyctest(int4range, numrange) does not exist +LINE 1: select x, pg_typeof(x) from anyctest(int4range(11,12), numra... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function anyctest(anycompatiblerange, anycompatiblerange); +-- fail, can't infer result type: +create function anyctest(anycompatible) +returns anycompatiblerange as $$ + select $1 +$$ language sql; +ERROR: cannot determine result data type +DETAIL: A result of type anycompatiblerange requires at least one input of type anycompatiblerange or anycompatiblemultirange. +create function anyctest(anycompatible, anycompatiblemultirange) +returns anycompatiblemultirange as $$ + select $2 +$$ language sql; +select x, pg_typeof(x) from anyctest(11, multirange(int4range(4,7))) x; + x | pg_typeof +---------+---------------- + {[4,7)} | int4multirange +(1 row) + +select x, pg_typeof(x) from anyctest(11, multirange(numrange(4,7))) x; + x | pg_typeof +---------+--------------- + {[4,7)} | nummultirange +(1 row) + +select x, pg_typeof(x) from anyctest(11, 12) x; -- fail +ERROR: function anyctest(integer, integer) does not exist +LINE 1: select x, pg_typeof(x) from anyctest(11, 12) x; + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +select x, pg_typeof(x) from anyctest(11.2, multirange(int4range(4,7))) x; -- fail +ERROR: function anyctest(numeric, int4multirange) does not exist +LINE 1: select x, pg_typeof(x) from anyctest(11.2, multirange(int4ra... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +select x, pg_typeof(x) from anyctest(11.2, '{[4,7)}') x; -- fail +ERROR: could not determine polymorphic type anycompatiblemultirange because input has type unknown +drop function anyctest(anycompatible, anycompatiblemultirange); +create function anyctest(anycompatiblemultirange, anycompatiblemultirange) +returns anycompatible as $$ + select lower($1) + upper($2) +$$ language sql; +select x, pg_typeof(x) from anyctest(multirange(int4range(11,12)), multirange(int4range(4,7))) x; + x | pg_typeof +----+----------- + 18 | integer +(1 row) + +select x, pg_typeof(x) from anyctest(multirange(int4range(11,12)), multirange(numrange(4,7))) x; -- fail +ERROR: function anyctest(int4multirange, nummultirange) does not exist +LINE 1: select x, pg_typeof(x) from anyctest(multirange(int4range(11... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function anyctest(anycompatiblemultirange, anycompatiblemultirange); +-- fail, can't infer result type: +create function anyctest(anycompatible) +returns anycompatiblemultirange as $$ + select $1 +$$ language sql; +ERROR: cannot determine result data type +DETAIL: A result of type anycompatiblemultirange requires at least one input of type anycompatiblerange or anycompatiblemultirange. +create function anyctest(anycompatiblenonarray, anycompatiblenonarray) +returns anycompatiblearray as $$ + select array[$1, $2] +$$ language sql; +select x, pg_typeof(x) from anyctest(11, 12) x; + x | pg_typeof +---------+----------- + {11,12} | integer[] +(1 row) + +select x, pg_typeof(x) from anyctest(11, 12.3) x; + x | pg_typeof +-----------+----------- + {11,12.3} | numeric[] +(1 row) + +select x, pg_typeof(x) from anyctest(array[11], array[1,2]) x; -- fail +ERROR: function anyctest(integer[], integer[]) does not exist +LINE 1: select x, pg_typeof(x) from anyctest(array[11], array[1,2]) ... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function anyctest(anycompatiblenonarray, anycompatiblenonarray); +create function anyctest(a anyelement, b anyarray, + c anycompatible, d anycompatible) +returns anycompatiblearray as $$ + select array[c, d] +$$ language sql; +select x, pg_typeof(x) from anyctest(11, array[1, 2], 42, 34.5) x; + x | pg_typeof +-----------+----------- + {42,34.5} | numeric[] +(1 row) + +select x, pg_typeof(x) from anyctest(11, array[1, 2], point(1,2), point(3,4)) x; + x | pg_typeof +-------------------+----------- + {"(1,2)","(3,4)"} | point[] +(1 row) + +select x, pg_typeof(x) from anyctest(11, '{1,2}', point(1,2), '(3,4)') x; + x | pg_typeof +-------------------+----------- + {"(1,2)","(3,4)"} | point[] +(1 row) + +select x, pg_typeof(x) from anyctest(11, array[1, 2.2], 42, 34.5) x; -- fail +ERROR: function anyctest(integer, numeric[], integer, numeric) does not exist +LINE 1: select x, pg_typeof(x) from anyctest(11, array[1, 2.2], 42, ... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function anyctest(a anyelement, b anyarray, + c anycompatible, d anycompatible); +create function anyctest(variadic anycompatiblearray) +returns anycompatiblearray as $$ + select $1 +$$ language sql; +select x, pg_typeof(x) from anyctest(11, 12) x; + x | pg_typeof +---------+----------- + {11,12} | integer[] +(1 row) + +select x, pg_typeof(x) from anyctest(11, 12.2) x; + x | pg_typeof +-----------+----------- + {11,12.2} | numeric[] +(1 row) + +select x, pg_typeof(x) from anyctest(11, '12') x; + x | pg_typeof +---------+----------- + {11,12} | integer[] +(1 row) + +select x, pg_typeof(x) from anyctest(11, '12.2') x; -- fail +ERROR: invalid input syntax for type integer: "12.2" +LINE 1: select x, pg_typeof(x) from anyctest(11, '12.2') x; + ^ +select x, pg_typeof(x) from anyctest(variadic array[11, 12]) x; + x | pg_typeof +---------+----------- + {11,12} | integer[] +(1 row) + +select x, pg_typeof(x) from anyctest(variadic array[11, 12.2]) x; + x | pg_typeof +-----------+----------- + {11,12.2} | numeric[] +(1 row) + +drop function anyctest(variadic anycompatiblearray); |