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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
