本文介绍了PostgreSQL函数调用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有PostgreSQL函数 test(integer)采用整数参数和同名的重载函数 test(字符变化)



当使用空值调用此函数时,Postgres总是执行函数 integer 参数。为什么会发生这种情况?为什么Postgres不选择带有 varchar 参数的函数?



函数调用示例:

  select test(null); 

解决方案

http://www.postgresql.org/docs/current/interactive/typeconv-func.htmlrel =nofollow> 功能类型解析 。详细说明在手册中。相关:







NULL没有显式类型转换开始类型未知:

  SELECT pg_typeof(NULL)

pg_typeof
-----------
unknown

实际上,我有可疑,并进行了一个快速测试,只是为了在Postgres 9.3和9.4找到不同的结果。 varchar 取自 integer (其中 奇怪 结果):





我认为相应的规则是在列表中的点4e(没有更早的点决定匹配):

函数与输入类型 text 到重载的混合, text 将被选中 varchar



个人我几乎总是使用文本 code> varchar 。虽然是二进制兼容的(因此几乎但不完全相同), text 在各方面都更接近Postgres的核心。



我添加到小提琴,以及另一个例子,其中Postgres不能决定和引发一个发脾气。



如果你想选择一个特定的函数,add一个显式的类型转换(这就是这里的方式!):

  select test(null :: int)AS func_int 
,test(null :: varchar)AS func_vc;


I have PostgreSQL function named test(integer) taking an integer parameter and an overloaded function of the same name test(character varying).

When calling this function with a null value, Postgres always executes the function taking an integer parameter. Why does this happen? Why doesn't Postgres chose the function with a varchar parameter?

Function call example:

select test(null);
解决方案

That's decided by the rules of Function Type Resolution. Detailed explanation in the manual. Related:

NULL without explicit type cast starts out as type "unknown":

SELECT pg_typeof(NULL)

 pg_typeof
-----------
 unknown

Actually, I got suspicious and ran a quick test, just to find different results in Postgres 9.3 and 9.4. varchar is picked over integer (which oddly contradicts your findings):

SQL Fiddle.

I would think the according rule is point 4e in the list (none of the earlier points decide the match):

If you added another function with input type text to the overloaded mix, text would be picked over varchar.

Personally I almost always use text instead of varchar. While being binary compatible (so almost but not quite the same), text is closer to the heart of Postgres in every respect.

I added that to the fiddle, as well as another example where Postgres cannot decide and throws a tantrum.

If you want to pick a particular function, add an explicit type cast (that's the way to go here!):

select test(null::int)     AS func_int
     , test(null::varchar) AS func_vc;

这篇关于PostgreSQL函数调用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-01 11:46