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.
CREATE OR REPLACE FUNCTION bound(x ANYELEMENT, lo ANYELEMENT, hi ANYELEMENT,
OUT bound ANYELEMENT) AS $$
IF x < lo THEN
bound := lo;
ELSEIF x > hi THEN
bound := hi;
bound := x;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
Robert McGehee, CFA
Geode Capital Management, LLC
One Post Office Square, 28th Floor | Boston, MA | 02109
pgsql-general by date
|Next:||From: dhaval jaiswal||Date: 2012-02-29 16:23:09|
|Subject: Re: archive_cleanup_command recovery.conf Standby server
|Previous:||From: David Greco||Date: 2012-02-29 15:51:56|
|Subject: Calling Functions With OUT paramaters|