Polymorphic Functions

Starting with PostgreSQL version 8.0, you can write polymorphic functions in PL/pgSQL. A polymorphic function is a function with at least one parameter of type ANYELEMENT or ANYARRAY. The types ANYELEMENT and ANYARRAY are called polymorphic types because they can assume different "shapes" at run-time.

Here's a simple polymorphic function that will return the greater of two arguments:

-- ch07.sql
CREATE OR REPLACE FUNCTION max( arg1 ANYELEMENT, arg2 ANYELEMENT ) 
 RETURNS ANYELEMENT AS $$
 BEGIN

 IF( arg1 > arg2 ) THEN
 RETURN( arg1 );
 ELSE
 RETURN( arg2 );
 END IF;

 END;
$$ LANGUAGE 'plpgsql';

When you call this function with two INTEGER values, PL/pgSQL treats the function as if you had defined it as

CREATE OR REPLACE FUNCTION max( arg1 INTEGER, arg2 INTEGER ) 
 RETURNS INTEGER AS $$

The polymorphic arguments arg1 and arg2 are assumed to be of type INTEGER.

If you call this function with two TEXT values, arg1 and arg2 are considered to be of type TEXT and the return value is also assumed to be of type TEXT. In fact, you can call this function with two arguments of almost any type. The only restriction is that the function must compile properly for a given type. In the case of the max() function, that means that there must be a > operator that compares two values of that type (since the function compares arg1 and arg2 using the > operator).

When you call a polymorphic function, the actual values that you provide for polymorphic parameters must all be of the same type. You can't call the max() function with an INTEGER and a TEXT argument because arg1 and arg2 are both defined as ANYELEMENT parameters. You can mix polymorphic arguments with other data types, you just have to ensure that all polymorphic arguments are of the same type. If you define ANYARRAY arguments, the elements within those arrays must match the type of other polymorphic parameters.

You can also write functions that return a value of type ANYELEMENT or ANYARRAY. When you call such a function, PostgreSQL infers the date type of the return value from the data type of the polymorphic arguments. You can't write a function that returns a polymorphic value unless the function expects at least one ANYELEMENT (or ANYARRAY) argument.

Here's a function that returns a polymorphic value. firstSmaller() finds the first element in arg2 that's smaller than arg1. arg2 must be a one-dimensional array:

-- ch07.sql
CREATE OR REPLACE FUNCTION firstSmaller( arg1 ANYELEMENT, arg2 ANYARRAY )
 RETURNS ANYELEMENT AS $$
 BEGIN

 FOR i IN array_lower( arg2, 1 ) .. array_upper( arg2, 1 ) LOOP

 IF arg2[i] < arg1 THEN
 RETURN( arg2[i] );
 END IF;

 END LOOP;

 RETURN NULL;

 END;
$$ LANGUAGE 'plpgsql';

You can call this function with an INTEGER value and array of INTEGERS, or a TEXT value and array of TEXT values, or a NUMERIC value and an array of NUMERIC values, and so on. If the polymorphic arguments (arg1 and arg2) are of type INTEGER, the return value will be of type INTEGER. If you call firstSmaller() with NUMERIC values, the return value will be of type NUMERIC.

A function that returns a polymorphic value automatically inherits an extra variable named $0. You can ALIAS $0 to a more descriptive name, such as result, to make it easier to read your code. The type of $0 is the same as the type of the return value; in other words, the data type of $0 matches the data type of the polymorphic arguments.

The sum() function, shown here, returns a polymorphic value.

-- ch07.sql
CREATE OR REPLACE FUNCTION sum( arg1 ANYARRAY ) RETURNS ANYELEMENT AS $$
 DECLARE
 result ALIAS FOR $0;
 BEGIN

 result := 0;

 FOR i IN array_lower( arg1, 1 ) .. array_upper( arg1, 1 ) LOOP

 IF arg1[i] IS NOT NULL THEN
 result := result + arg1[i];
 END IF;

 END LOOP;

 RETURN( result );

 END;

$$ LANGUAGE 'plpgsql';

The data type for $0 is inferred from the type of arg1. PL/pgSQL always initializes the return value to NULLbecause this function accumulates result as it progresses through the arg1 array, you must re-initialize result to 0 before you can add to it (remember, NULL + 5 is not the same as 0 + 5NULL+ 5 = NULL).

Note that you can't call the sum() function with an array of TEXT values because PostgreSQL doesn't define a TEXT + TEXT operator. You can call sum() with an array of any numeric type (INTEGER, REAL, NUMERIC, and so on).