PostgreSQL错误：函数to_tsvector(字符变化,未知)不存在

这个psql会话片段应该是不言自明的：

psql (9.1.7)
Type "help" for help.
=> CREATE TABLE languages(language VARCHAR NOT NULL);
CREATE TABLE
=> INSERT INTO languages VALUES ('english'),('french'),('turkish');
INSERT 0 3
=> SELECT language,to_tsvector('english','hello world') FROM languages;
 language|     to_tsvector     
---------+---------------------
 english | 'hello':1 'world':2
 french  | 'hello':1 'world':2
 turkish | 'hello':1 'world':2
(3 rows)

=> SELECT language,to_tsvector(language,'hello world') FROM languages;
ERROR:  function to_tsvector(character varying,unknown) does not exist
LINE 1: select language,'hello world')...
                         ^
HINT:  No function matches the given name and argument types.  
You might need to add explicit type casts.

问题是Postgres函数to_tsvector不喜欢varchar字段类型,但这个调用应该完全正确according to the documentation？

使用显式类型转换：

SELECT language,to_tsvector(language::regconfig,'hello world') FROM languages;

或者将列languages.language更改为键入regconfig. See @Swav’s answer.

为什么？

Postgres允许function overloading.函数签名由它们(可选的模式限定的)名称加上(列表)输入参数类型定义. to_tsvector()的2参数形式需要将类型regconfig作为第一个参数：

SELECT proname,pg_get_function_arguments(oid)
FROM   pg_catalog.pg_proc
WHERE  proname = 'to_tsvector'

   proname   | pg_get_function_arguments
-------------+---------------------------
 to_tsvector | text
 to_tsvector | regconfig,text             -- you are here

如果没有现有函数完全匹配,则Function Type Resolution的规则决定最佳匹配 – 如果有的话.这对于to_tsvector(‘english’,’hello world’)是成功的,’english’是一个无类型的字符串文字.但是使用参数类型varchar失败,因为没有从varchar注册到regconfig的隐式转换. The manual：

Discard candidate functions for which the input types do not match and
cannot be converted (using an implicit conversion) to match. unknown
literals are assumed to be convertible to anything for this purpose.

大胆强调我的.
regconfig的注册强制转换：

SELECT castsource::regtype,casttarget::regtype,castcontext
FROM   pg_catalog.pg_cast
WHERE  casttarget = 'regconfig'::regtype;

 castsource | casttarget | castcontext
------------+------------+-------------
 oid        | regconfig  | i
 bigint     | regconfig  | i
 smallint   | regconfig  | i
 integer    | regconfig  | i

Explanation for castcontext：

castcontext char
Indicates what contexts the cast can be invoked
in. e means only as an explicit cast (using CAST or :: Syntax). a
means implicitly in assignment to a target column,as well as
explicitly. i means implicitly in expressions,as well as the other cases.

阅读有关三种不同类型的任务in the chapter CREATE CAST的更多信息.

原文链接：https://www.f2er.com/postgresql/192779.html

PostgreSQL错误：函数to_tsvector(字符变化,未知)不存在

猜你在找的Postgre SQL相关文章