Hello,
I'd like to write a generalized function that accepts arguments of all type without producing an error if types do not match across 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 polymorphic ANYELEMENT 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 operation is 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 'SELECT bound(3::real, 0::integer, 10::smallint);' would return 3::real. For just the 9 numeric data types with well-defined inequality comparisons, I would need to define 9^3 functions to cover all possible type permutations. My question is, can I write a single function that accepts arbitrary and different types for its three arguments? I'm using PostgreSQL 9.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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
