Arbitrary and mixed data types in function

Поиск
Список
Период
Сортировка
От McGehee, Robert
Тема Arbitrary and mixed data types in function
Дата
Msg-id 17B09E7789D3104E8F5EEB0582A8D66FBAA3FCEC16@MSGRTPCCRF2WIN.DMN1.FMR.COM
обсуждение исходный текст
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: David Greco
Дата:
Сообщение: Calling Functions With OUT paramaters
Следующее
От: dhaval jaiswal
Дата:
Сообщение: Re: archive_cleanup_command recovery.conf Standby server error