--- title: "Variadic and polymorphic subprograms" url: "https://docs.yugabyte.com/stable/api/ysql/user-defined-subprograms-and-anon-blocks/variadic-and-polymorphic-subprograms/" --- # Variadic and polymorphic subprograms Defines Variadic and polymorphic subprograms and explains their purpose [YSQL]. A *variadic* subprogram allows one of its formal arguments to have any number of actual values, provided as a comma-separated list, just like the built-in functions *least()* and *greatest()* allow. A *polymorphic* subprogram allows both its formal arguments and, for a function, its return data type to be declared using so-called polymorphic data types (for example, *anyelement* or *anyarry*) so that actual arguments with various different data types can be used, in different invocations of the subprogram, for the same formal arguments. Correspondingly, the data type(s) of the actual arguments will determine the data type of a function's return value. You can declare *variadic* or *polymorphic* arguments, or *polymorphic* return values, for both *language sql* and *language plpgsql* subprograms. ## Variadic subprograms You mark a subprogram's formal argument with the keyword *variadic* as an alternative to marking it with *in*, *out*, or *inout*. See the [*arg\_mode*](/stable/api/ysql/syntax_resources/grammar_diagrams/#arg-mode "arg_mode") syntax rule. A subprogram can have at most just one *variadic* formal argument; and it must be last in the list of arguments. Breaking this rule causes this syntax error: ```outout 42P13: VARIADIC parameter must be the last input parameter ``` ### User-defined *mean()* variadic function Though there are built-in *least()* and *greatest()* *variadic* functions (corresponding to the built-in *min()* and *max()* aggregate functions), there is no built-in *mean()* *variadic* function to correspond to the built-in *avg()* aggregate function. It therefore provides a nice example of how to implement your own. Try this: ```plpgsql create function mean( arr variadic numeric[] = array[1.1, 2.1, 3.1]) returns numeric immutable set search_path = pg_catalog, pg_temp language sql as $body$ with c(v) as ( select unnest(arr)) select avg(v) from c; $body$; select to_char(mean(17.1, 6.5, 3.4), '99.99') as "mean"; ``` The result is *9.00*, as is expected. You can see immediately that each actual value that you provide for the *variadic* formal argument must have (or be typecastable to) the data type of the array with which this argument is declared. You can transform the *language sql* version trivially into a *language plpgsql* function simply by re-writing the body thus: ```output ... language plpgsql as $body$ begin return ( with c(v) as ( select unnest(arr)) select avg(v) from c); end; $body$; ``` The example defines a default value for the *variadic* formal argument simply to make the point that this is legal. It's probably unlikely that you'll have a use case that benefits from this. ### Choosing between the *variadic* form and the non-*variadic* equivalent form The example makes the point that declaring a formal argument as *variadic* is simply syntax sugar for what you could achieve without this device by declaring the argument ordinarily as an array. Try this: ```plpgsql create function mean(arr numeric[]) returns numeric immutable set search_path = pg_catalog, pg_temp language sql as $body$ with c(v) as ( select unnest(arr)) select avg(v) from c; $body$; select to_char(mean(array[17.1, 6.5, 3.4]), '99.99') as "mean"; ``` Its body is identical to that of the *variadic* *language sql* version—and it produces the same result. The choice between the two approaches is determined entirely by usability: it's less cluttered to invoke the function with a bare list of values than to surround them with the *array\[]* constructor. On the other hand, if the common case is that the values are already available as an array, then you may just as well not choose the *variadic* implementation. Notice, though, that syntax is defined that lets you invoke a *variadic* function using an array of values as the actual argument. Re-create the *variadic* form of *mean()*. (It doesn't matter whether you use the *language sql* or the *language plpgsql* version.) Now invoke it thus: ```plpgsql select to_char(mean(variadic array[17.1, 6.5, 3.4]), '99.99') as "mean"; ``` This runs without error and produces the expected *9.00* result. ## Polymorphic subprograms These are the polymorphic data types: - *anyelement* - *anyarray* - *anynonarray* - *anyenum* - *anyrange* See the PostgreSQL documentation section [38.2.5. Polymorphic Types](https://www.postgresql.org/docs/15/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC "38.2.5. Polymorphic Types"). These data types are a subset of the so-called pseudo-Types. See the PostgreSQL documentation section [8.21. Pseudo-Types](https://www.postgresql.org/docs/15/datatype-pseudo.html "8.21. Pseudo-Types"). Notice this from the Polymorphic Types account: > Note that *anynonarray* and *anyenum* do not represent separate type variables; they are the same type as *anyelement*, just with an additional constraint. For example, declaring a function as *f(anyelement, anyenum)* is equivalent to declaring it as *f(anyenum, anyenum)*: both actual arguments have to be the same *enum* type. This section illustrates the use of just *anyelement* and *anyarray*. See the PostgreSQL documentation section [38.5.10. Polymorphic SQL Functions](https://www.postgresql.org/docs/15/xfunc-sql.html#id-1.8.3.8.18 "38.5.10. Polymorphic SQL Functions") for more information on this topic. ### Proof-of-concept example #1 Try this. It simply shows an example of how to use the feature. ```plpgsql create function my_typeof(v in text) returns text immutable set search_path = pg_catalog, pg_temp language sql as $body$ select 'non-polymorphic text: '||pg_typeof(v)::text; $body$; create function my_typeof(v in int) returns text immutable set search_path = pg_catalog, pg_temp language sql as $body$ select 'non-polymorphic int: '||pg_typeof(v)::text; $body$; create function my_typeof(v in anyelement) returns text immutable set search_path = pg_catalog, pg_temp language sql as $body$ select 'polymorphic: '||pg_typeof(v)::text; $body$; create type s.ct as (b boolean, i int); \x on \t on select (select my_typeof('dog')) as "dog", (select my_typeof(42)) as "42", (select my_typeof(true)) as "true", (select my_typeof((true, 99)::s.ct)) as "(true, 99)::s.ct"; \t off \x off ``` It runs without error and produces this result: ```output dog | non-polymorphic text: text 42 | non-polymorphic int: integer true | polymorphic: boolean (true, 99)::s.ct | polymorphic: s.ct ``` The function *my\_typeof()* adds no functionality beyond what the *pg\_typeof()* built-in function exposes. So the code example has no value beyond this pedagogy: - The built-in function *pg\_typeof()* is itself polymorphic. The \\*df* meta-command shows that its input formal argument has the data type *"any"*. (The double quotes are used because *any* is a SQL reserved word.) Notice that the designer of this built-in could just as well have defined it with an input formal argument of data type *anyelement*. - A user-defined subprogram with an input formal argument of data type *anyelement* can be among a set of distinguishable overloads where others in the set have input formal arguments of data type, for example, *text* or *int*. ### Proof-of-concept example #2 Try this. It simply shows another example of how to use the feature. ```plpgsql create function array_from_two_elements( e1 in anyelement, e2 in anyelement) returns anyarray immutable set search_path = pg_catalog, pg_temp language sql as $body$ select array[e1, e2]; $body$; create type s.ct as (k int, t text); create view pg_temp.v(arr) as select array_from_two_elements((17, 'dog')::ct, (42, 'cat')::ct); with c(e) as ( select unnest(arr) from pg_temp.v) select (e).k, (e).t from c; ``` It produces this result: ```plpgsql k | t ----+----- 17 | dog 42 | cat ``` Here, too, the function *array\_from\_two\_elements()* adds no functionality beyond what the native array constructor exposes. So the code example has no value beyond this pedagogy: - It shows that when two elements (i.e. scalar values) of data type *ct* are listed in an array constructor, the emergent data type of the result is *ct\[]*. - In other words, when the input formal arguments have the data type *anyelement*, the *returns anyarray* clause automatically accommodates this correctly. This makes the same point more explicitly: ```plpgsql select pg_typeof(arr) from pg_temp.v; ``` This is the result: ```output pg_typeof ----------- ct[] ``` ## Combining variadic and polymorphic functionality in a single user-defined function A *variadic* function can be *polymorphic* too. Simply declare its last formal argument as *variadic anyarray*. Argument matching, and the determination of the actual result type, behave as you'd expect. ### Example: *mean()* for numeric values or single character values Don't be concerned about the artificiality of this example. Its aim is simply to illustrate how the low-level functionality works. If the *variadic* input is a list of *numeric* values, then the body code detects this (using the *pg\_typeof()* built-in function) and branches to use the code that the non-*polymorphic* implementation shown in the section [User-defined *mean()* variadic function](#user-defined-mean-variadic-function "User-defined mean() variadic function") above uses. But if the *variadic* input is a list of *s.one\_character* values (where *s.one\_character* is a user-defined domain based on the native *text* data type whose constraint ensures that the length is exactly one character), then the implementation uses a different method: - It converts each single character *text* value to a *numeric* value with the *ascii()* built in function. - It calculates the mean of these numeric values in the same way that the implementation for the mean of *numeric* input values does. - It uses the *round()* built in function, together with the *::int* typecast to convert this mean to the nearest *int* value. - It converts this *int* value back to a single character *text* value using the *chr()* built in function. Create the constraint function, the domain, and the *variadic polymorphic* function thus: ```plpgsql create schema s; create function s.is_one_character(t in text) returns boolean set search_path = pg_catalog, pg_text language plpgsql as $body$ declare msg constant text not null := '«%s» is not exactly one character character'; begin assert length(t) = 1, format(msg, t); return true; end; $body$; create domain s.one_character as text constraint is_one_character check(s.is_one_character(value)); create function s.mean(arr variadic anyarray) returns anyelement immutable set search_path = pg_catalog, pg_temp language plpgsql as $body$ declare n numeric not null := 0.0; t s.one_character not null := 'a'; begin assert cardinality(arr) > 0, 'cardinality of arr < 1'; declare d_type constant regtype not null := pg_typeof(arr[1]); begin case d_type when pg_typeof(n) then with c(v) as (select unnest(arr)) select avg(v) from c into n; return n; when pg_typeof(t) then with c(v) as (select unnest(arr)) select chr(round(avg(ascii(v)))::int) from c into t; return t; end case; end; end; $body$; ``` Test it with a list of *numeric* values. ```plpgsql select to_char(s.mean(17.1, 6.5, 3.4), '99.99') as "mean"; ``` This is the result: ```output mean -------- 9.00 ``` Now test it with a list of *s.one\_character* values: ```plpgsql select s.mean( 'c'::s.one_character, 'e'::s.one_character, 'g'::s.one_character); ``` This is the result: ```output mean ------ e ``` Notice that the argument of the *returns* keyword in the function header is *anyelement*. This has a strictly defined meaning. The internal implementation detects the data type of the input array's elements. And then it checks that, however the user-defined implementation manages this, the data type of the computed to-be-returned value matches the data type of the input array's elements. If this requirement isn't met, then you're likely to get a run-time error when the run-time interpretation of your code attempts to typecast the value that you attempt to return to the expected actual data type for *anyelement* in the present execution. For example, if you simply use this hard-coded return: ```output return true::boolean; ``` then you'll get this run-time error: ```output P0004: «true» is not exactly one character character ``` because, of course, the *text* typecast of the boolean value has four characters. You can experiment by adding a leg to the *case* statement to handle a *variadic* input list of some user-defined composite type values—and then hard-code a non-composite value for the *return* argument. You'll see this run-time error: ``` 42804: cannot return non-composite value from function returning composite type ``` ### Example: *mean()* for numeric values or rectangle area values This example emphasizes the meaning of writing *returns anyelement* by specifying that the returned mean should always be *numeric*—both when the *variadic* input is a list of scalar *numeric* values and when it's a list of *rectangle* composite type values. Here, you simply write what you want: *returns numeric*. Create the function thus: ```plpgsql create schema s; create type s.rectangle as (len numeric, wid numeric); create function s.mean(arr variadic anyarray) returns numeric immutable set search_path = pg_catalog, pg_temp language plpgsql as $body$ declare c constant int not null := cardinality(arr); n numeric not null := 0.0; r s.rectangle not null := (1.0, 1.0); tot numeric not null := 0.0; begin assert c > 0, 'cardinality of arr < 1'; declare d_type constant regtype not null := pg_typeof(arr[1]); begin case d_type when pg_typeof(n) then with c(v) as (select unnest(arr)) select avg(v) from c into n; return n; when pg_typeof(r) then foreach r in array arr loop tot := tot + r.len*r.wid; end loop; return tot/c::numeric; end case; end; end; $body$; ``` Test it with the same list of *numeric* values that you used to test the previous version of *mean()*: ```plpgsql select to_char(s.mean(17.1, 6.5, 3.4), '99.99') as "mean"; ``` Of course, the result is the same for this version of *mean()* as for the previous version: ```output mean -------- 9.00 ``` Now test it with a list of *rectangle* values: ```plpgsql select to_char(s.mean( (2.0, 3.0)::s.rectangle, (3.0, 9.0)::s.rectangle, (5.0, 7.0)::s.rectangle), '99.99') as "mean"; ``` This is the result: ```output mean -------- 22.67 ``` ## One polymorphic subprogram *versus* an overload set of non-polymorphic subprograms Recast the previous *polymorphic* implementation of the *variadic* *mean()* as two separate non-*polymorphic* implementations, thus: ```plpgsql drop function if exists s.mean(anyarray) cascade; create function s.mean(arr variadic numeric[]) returns numeric immutable set search_path = pg_catalog, pg_temp language plpgsql as $body$ begin assert cardinality(arr) > 0, 'cardinality of arr < 1'; return ( with c(v) as (select unnest(arr)) select avg(v) from c); end; $body$; create function s.mean(arr variadic s.rectangle[]) returns numeric immutable set search_path = pg_catalog, pg_temp language plpgsql as $body$ declare c constant int not null := cardinality(arr); r s.rectangle not null := (1.0, 1.0); tot numeric not null := 0.0; begin assert cardinality(arr) > 0, 'cardinality of arr < 1'; foreach r in array arr loop tot := tot + r.len*r.wid; end loop; return tot/c::numeric; end; $body$; ``` Test it with the same two *select* statements that you used to test the *polymorphic* version. The results are identical. Choosing between the different approaches can only be a judgement call—informed by intuition born of experience. - The total source code size of the two non-*polymorphic* implementations, here, is greater than that of the single *polymorphic* implementation. But this is largely explained by the repetition, with the non-*polymorphic* implementations, of the boilerplate source text of the function headers. - The implementation of each non-*polymorphic* implementation is simpler than that of the *polymorphic* version because no user-written code is needed to detect the data type of the *variadic* argument list; rather, this is done behind the scenes by the run-time system when it picks the appropriate overload. - The *polymorphic* implementation encapsulates all the implementation variants in a single code unit. This is beneficial because (short of using dedicated schemas to group functionally related subprogram overloads) you'd have to rely on external documentation to explain that the different *name()* variants belonged together as a set.