Hello,
I'd like to write a generalized function that accepts arguments of all type without producing an error if types do not
matchacross arguments.
As an example, I've written a simple function below called 'bound' (below) that returns a value 'x' bounded by the
range[lo, hi]. The function works on any single type with a defined inequality operator by making use of the
polymorphicANYELEMENT type: ex: bound(10, 0, 8)=8, and bound('A'::text, 'D'::text, 'Z'::text)='D'.
Unfortunately, the function reports an error unless 'x', 'lo', and 'hi' all share the same type, even if an inequality
operationis still sensible. For instance 'SELECT bound(1.0, 2, 3);' returns the error
'ERROR: function bound(numeric, integer, integer) does not exist'
What I would like is for the function to accept mixed types and return a value of the same type as 'x'. For example
'SELECTbound(3::real, 0::integer, 10::smallint);' would return 3::real. For just the 9 numeric data types with
well-definedinequality comparisons, I would need to define 9^3 functions to cover all possible type permutations. My
questionis, can I write a single function that accepts arbitrary and different types for its three arguments? I'm using
PostgreSQL9.1.
Thanks, Robert
CREATE OR REPLACE FUNCTION bound(x ANYELEMENT, lo ANYELEMENT, hi ANYELEMENT,
OUT bound ANYELEMENT) AS $$
BEGIN
IF x < lo THEN
bound := lo;
ELSEIF x > hi THEN
bound := hi;
ELSE
bound := x;
END IF;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
Robert McGehee, CFA
Geode Capital Management, LLC
One Post Office Square, 28th Floor | Boston, MA | 02109
Direct: (617)392-8396
