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
castcontext char
Indicates what contexts the cast can be invoked
in.emeans only as an explicit cast (usingCASTor::Syntax).a
means implicitly in assignment to a target column,as well as
explicitly.imeans implicitly in expressions,as well as the other cases.
阅读有关三种不同类型的任务in the chapter CREATE CAST的更多信息.
