PostgreSQL 错误：函数 to_tsvector（字符变化，未知）不存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14523624/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL ERROR: function to_tsvector(character varying, unknown) does not exist
提问by Dfr
This psql session snippet should be self-explanatory:
这个 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, to_tsvector(language, 'hello world')...
^
HINT: No function matches the given name and argument types.
You might need to add explicit type casts.
The problem is that Postgres function to_tsvectordoesn't like varcharfield type but this call should be perfectly correct according to the documentation?
问题是 Postgres 函数to_tsvector不喜欢varchar字段类型，但根据文档，这个调用应该是完全正确的？
回答by Erwin Brandstetter
Use an explicit type cast:
使用显式类型转换：
SELECT language, to_tsvector(language::regconfig, 'hello world') FROM languages;Or change the column languages.languageto type regconfig. See @Swav's answer.
或者将列更改languages.language为类型regconfig。请参阅@Swav 的回答。
Why?
为什么？
Postgres allows function overloading. Function signatures are defined by their (optionally schema-qualified) nameplus (the list of) input parameter type(s). The 2-parameter form of to_tsvector()expects type regconfigas first parameter:
Postgres 允许函数重载。函数签名由它们的（可选模式限定的）名称加上（列表）输入参数类型定义。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
If no existing function matches exactly, the rules of Function Type Resolutiondecide the best match - if any. This is successful for to_tsvector('english', 'hello world'), with 'english'being an untyped string literal. But fails with a parameter typed varchar, because there is no registered implicitcast from varcharto regconfig. The manual:
如果没有现有函数完全匹配，则函数类型解析规则决定最佳匹配 - 如果有的话。这是成功的 to_tsvector('english', 'hello world')，因为'english'它是一个无类型的字符串文字。但是由于输入varchar了参数而失败，因为没有从to注册的隐式强制varchar转换regconfig。手册：
Discard candidate functions for which the input types do not match and cannot be converted (using an implicitconversion) to match. unknown literalsare assumed to be convertible to anything for this purpose.
丢弃输入类型不匹配且无法转换（使用隐式转换）以匹配的候选函数。为此，假定未知文字可转换为任何内容。
Bold emphasis mine.
The registered casts for regconfig:
大胆强调我的。
已注册的演员阵容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).ameans implicitly in assignment to a target column, as well as explicitly.imeans implicitly in expressions, as well as the other cases.
castcontext char
指示可以在哪些上下文中调用强制转换。e仅表示作为显式强制转换（使用CAST或::语法）。a意味着隐式分配给目标列，以及显式。i意味着隐含在表达式中，以及其他情况。
Read more about the three different types of assignmentin the chapter CREATE CAST.
在 CREATE CAST 一章中阅读有关三种不同类型分配的更多信息。
回答by Swav
Alternative approach to Erwin Brandstetter's answer
Erwin Brandstetter 答案的替代方法
You could define your language column to be of type regconfig which would make your query a bit less verbose i.e.:
您可以将语言列定义为 regconfig 类型，这将使您的查询不那么冗长，即：
CREATE TABLE languages(language regconfig NOT NULL DEFAULT 'english'::regconfig)
I have set english as default above, but that's not required. Afterwards your original query
我在上面将英语设置为默认值，但这不是必需的。之后您的原始查询
SELECT language, to_tsvector(language, 'hello world') FROM languages;
would work just fine.
会工作得很好。
