postgresql Cast 产生“返回的类型字符变化与预期的类型字符变化（8）”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10758149/
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
Cast produces 'Returned type character varying does not match expected type character varying(8)'
提问by hermes the goat
Yesterday we had a PostgreSQL database upgraded to version 9.1.3. We thought we had everything tested and ready, but there is a function we missed. It returns a table type like this:
昨天我们有一个 PostgreSQL 数据库升级到版本 9.1.3。我们以为我们已经测试并准备好了一切，但是我们错过了一个功能。它返回一个像这样的表类型：
CREATE OR REPLACE FUNCTION myfunc( patient_number varchar
, tumor_number_param varchar, facility_number varchar)
RETURNS SETOF patient_for_registrar
LANGUAGE plpgsql
AS
$body$
BEGIN
RETURN QUERY
SELECT cast(nfa.patient_id_number as varchar),
...
I only only give the first column of the select because that is where the error happens. Before today this function ran fine, but now it gives this error:
我只给出选择的第一列，因为那是发生错误的地方。在今天之前，此功能运行良好，但现在出现此错误：
ERROR: structure of query does not match function result type
Detail: Returned type character varying does not match expected type character varying(8) in column 1. Where: PL/pgSQL function "getwebregistrarpatient_withdeletes" line 3 at RETURN QUERY [SQL State=42804]
错误：查询的结构与函数结果类型不匹配
详细信息：返回的类型字符变化与第 1 列中的预期类型字符变化（8）不匹配。其中：PL/pgSQL 函数“getwebregistrarpatient_withdeletes”第 3 行在 RETURN QUERY [SQL State=42804 ]
The column nfa.patient_id_numberis text and is being cast for the column patient_id_numberin patient_for_registrarthat is varchar(8). After reading about this some I think the problem is because the column length isn't being specified when casting from text. But the problem is I've tried various combinations of substrings to fix this and none are solving the problem:
列nfa.patient_id_number是文本，并正在施放的列patient_id_number中patient_for_registrar是varchar(8)。在阅读了一些之后，我认为问题是因为从文本转换时没有指定列长度。但问题是我尝试了各种子字符串组合来解决这个问题，但都没有解决问题：
substring(cast(nfa.patient_id_number as varchar) from 1 for 8),
cast(substring(nfa.patient_id_number from 1 for 8) as varchar),
cast(substring(nfa.patient_id_number from 1 for 8) as varchar(8)),
Does anyone have any pointers?
有没有人有任何指示？
回答by Erwin Brandstetter
Your function ..
你的功能..
RETURNS SETOF patient_for_registrar
The returned row type must match the declared type exactly. You did not disclose the definition of patient_for_registrar, probably the associated composite type of a table. I quote the manual about Declaration of Composite Types:
返回的行类型必须与声明的类型完全匹配。您没有透露 的定义patient_for_registrar，可能是表的关联复合类型。我引用了关于复合类型声明的手册：
Whenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type.
无论何时创建表，都会自动创建一个与表同名的复合类型，以表示表的行类型。
If the first column of that type (table) is defined varchar(8)(with length modifier) - as the error message indicates, you have to return varchar(8)with the same length modifier; varcharwon't do. It is irrelevant for that matter whether the string length is only 8 characters, the data typehas to match.
如果定义了该类型（表）的第一列varchar(8)（使用长度修饰符） - 如错误消息所示，您必须varchar(8)使用相同的长度修饰符返回；varchar不会。字符串长度是否只有8个字符无关紧要，数据类型必须匹配。
varchar, varchar(n)and varchar(m)are differentdata types for PostgreSQL.
varchar,varchar(n)和varchar(m)是PostgreSQL 的不同数据类型。
Older versions did not enforce the type modifiers, but with PostgreSQL 9.0this was changed for plpgsql:
旧版本没有强制执行类型修饰符，但在 PostgreSQL 9.0 中，这对 plpgsql 进行了更改：
PL/pgSQL now requires columns of composite results to match the expected type modifier as well as base type (Pavel Stehule, Tom Lane)
For example, if a column of the result type is declared as NUMERIC(30,2), it is no longer acceptable to return a NUMERIC of some other precision in that column. Previous versions neglected to check the type modifier and would thus allow result rows that didn't actually conform to the declared restrictions.
PL/pgSQL 现在需要复合结果的列来匹配预期的类型修饰符和基本类型（Pavel Stehule，Tom Lane）
例如，如果结果类型的列被声明为 NUMERIC(30,2)，则在该列中返回某个其他精度的 NUMERIC 不再可接受。以前的版本忽略了检查类型修饰符，因此会允许实际不符合声明限制的结果行。
Two basic ways to fix your problem:
解决问题的两种基本方法：
You can cast the returned values to match the definition of
patient_for_registrar:nfa.patient_id_number::varchar(8)Or you can change the
RETURNSclause. I would useRETURNS TABLEand declare a matching composite type. Here is an example.RETURNS TABLE (patient_for_registrar varchar, col2 some_type, ...)
您可以将返回的值转换为匹配的定义
patient_for_registrar：nfa.patient_id_number::varchar(8)或者您可以更改
RETURNS条款。我会使用RETURNS TABLE并声明一个匹配的复合类型。这是一个例子。RETURNS TABLE (patient_for_registrar varchar, col2 some_type, ...)
As an aside: I neveruse varcharif I can avoid it - especially not with length modifier. It offers hardly anything that the type textcouldn't do. If I needa length restriction, I use a column constraint which can be changed without rewriting the whole table.
顺便说一句：如果可以避免的话，我从不使用varchar它 - 特别是不使用长度修饰符。它几乎提供了该类型text无法提供的任何内容。如果我需要长度限制，我会使用一个列约束，它可以在不重写整个表的情况下进行更改。
