PostgreSQL错误：函数to_tsvector（字符变化，未知）不存在 [英] PostgreSQL ERROR: function to_tsvector(character varying, unknown) does not exist

问题描述

此psql会话片段应该是不言而喻的：

This psql session snippet should be self-explanatory:

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.

问题是Postgres函数 to_tsvector 't like varchar 字段类型，但此调用应完全正确根据文档？

The problem is that Postgres function to_tsvector doesn't like varchar field type but this call should be perfectly correct according to the documentation?

推荐答案

函数签名不能使用显式类型 varchar 来猜测。意识到无类型字符串文字与 varchar 的参数不同。

The function signature cannot be guessed with the explicit type varchar. Realize that an untyped string literal is not the same thing as parameter of type varchar.

Use instead：

Use instead:

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

为什么？

2参数表单的类型为 regconfig ，而不是 text 或 varchar ：

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

只要不使用显式类型，Postgres就会考虑所有类型字符串文字可以转换为匹配的函数签名。

As long as you don't use an explicit type, Postgres will consider all types a string literal can be cast to for a matching function signature.

但是，如果您使用显式类型（就像您在第二个示例中所做的那样： varchar ）Postgres无法找到从 varchar 到 regconfig 的隐式转换，并且不考虑您要查找的函数。你告诉Postgres：不再进一步，我想要一个 varchar 参数的函数（或者一个只是隐式转换的函数）！

However, if you use an explicit type (like you do in your second example: varchar) Postgres cannot find an implicit conversion from varchar to regconfig and does not consider the function you are looking for. You are telling Postgres: "Look no further, I want the function with a varchar argument (or one that is only an implicit cast away)!"

我引用了章节类型转换 - 功能 ：

未知文字假定可转换为任何目的。

unknown literals are assumed to be convertible to anything for this purpose.

注册的 varchar >

The registered casts for varchar, ordered by proximity:

SELECT castsource::regtype, casttarget::regtype, castcontext
FROM   pg_catalog.pg_cast
WHERE  castsource = 'varchar'::regtype
ORDER BY castsource, castcontext = 'i' DESC, castcontext

    castsource     |    casttarget     | castcontext
-------------------+-------------------+-------------
 character varying | regclass          | i
 character varying | name              | i
 character varying | text              | i
 character varying | character         | i
 character varying | character varying | i
 character varying | "char"            | a
 character varying | xml               | e

Postgres会找到一个函数， em>。

<$ c的说明$ c> castcontext ：

Postgres would find a function where the signature can be reached with an implicit cast.
Explanation for castcontext:

castcontext char

指示可以调用转换的上下文
在 e 表示仅作为显式转换（使用 CAST 或 :: 语法）。 a
意味着隐含地分配给目标列，以及明确的
。 i 表示隐含在表达式中，以及其他情况。

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.

更多关于三种不同类型的作业 CREATE CAST 一章。

Read more about the three different types of assignment in the chapter CREATE CAST.

这篇关于PostgreSQL错误：函数to_tsvector（字符变化，未知）不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！