如何在Postgres 9.1+函数中使用混合的int和数字参数 [英] How to use mixed int and numeric arguments in a Postgres 9.1+ function

问题描述

我正在寻找一种创建icase()函数的方法，该函数可与任何第二个和第三个参数兼容的数据类型一起使用.
我在Postgres 9.4中尝试过:

I'm looking for a way to create an icase() function which works with any second and third parameter compatible data types.
I tried in Postgres 9.4:

CREATE OR REPLACE FUNCTION public.icase(
    cond1 boolean,
    res1 anyelement,
    conddefault anyelement)
  RETURNS anyelement AS
' SELECT CASE WHEN $1 THEN $2 ELSE $3 END; '
  LANGUAGE sql IMMUTABLE;

但是:

select icase( true, 1.0, 0 )

导致错误:

ERROR:  function icase(boolean, numeric, integer) does not exist
LINE 9: select icase( true, 1.0, 0 )
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

如何在9.1+中修复此问题，以便第二个和第三个参数可以是int或数字?

How to fix this in 9.1+ so that second and third arguments can be either int or numeric?

如果第二个和第三个参数均为text，char(n)，date，numeric或int类型，则可以调用此方法.

This method may be called if both second and third parameters are text, char(n), date, numeric or int types.

推荐答案

此时多态类型很严格-在其他情况下，PostgreSQL尝试将常量转换为最常见的类型，但是多态类型缺少此步骤-因此在这种情况下，当您描述问题时，必须显式进行强制转换，或者不应该使用多态类型.方案B超出了功能超载.

The polymorphic types are strict in this moment - in other cases, PostgreSQL try to cast constants to most common type, but this step is missing for polymorphic types - so in this case, when you have described issue, you have to cast explicitly or you should not to use polymorphic types. Plan B is over function overloading.

CREATE OR REPLACE FUNCTION public.icase1(cond1 boolean,
                                         res1 integer, conddefault integer)
RETURNS integer AS $$
SELECT CASE WHEN $1 THEN $2 ELSE $3 END;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION public.icase1(cond1 boolean,
                                         res1 numeric, conddefault numeric)
RETURNS numeric AS $$
SELECT CASE WHEN $1 THEN $2 ELSE $3 END;
$$ LANGUAGE sql;

然后您的代码将按预期工作:

Then your code will work as expected:

postgres=> select icase1(true, 1.0, 0);
 icase1 
--------
    1.0
(1 row)

postgres=> select icase1(true, 1.0, 1.0);
 icase1 
--------
    1.0
(1 row)

postgres=> select icase1(true, 1, 0);
 icase1 
--------
      1
(1 row)

这篇关于如何在Postgres 9.1+函数中使用混合的int和数字参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！