Cast产生“返回类型字符变化不匹配预期类型字符变化(8)”

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:

我只给出select的第一列，因为这是错误发生的地方。在今天之前，这个函数运行得很好，但是现在它给出了这个错误:

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 at RETURN QUERY [SQL State=42804]

The column nfa.patient_id_number is text and is being cast for the column patient_id_number in patient_for_registrar that 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_for_registrar中的列patient_id_number进行转换，这是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?

有人有什么建议吗?

1 个解决方案

#1

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; varchar won't do. It is irrelevant for that matter whether the string length is only 8 characters, the data type has to match.

如果该类型(表)的第一列定义为varchar(8)(带有长度修饰符)——如错误消息所示，则必须返回具有相同长度修饰符的varchar(8);varchar不行。这与字符串长度是否只有8个字符无关，数据类型必须匹配。

varchar, varchar(n) and varchar(m) are different data types for PostgreSQL.

varchar、varchar(n)和varchar(m)是PostgreSQL的不同数据类型。

Older versions did not enforce the type modifiers, but with PostgreSQL 9.0 this 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)

PL/pgSQL现在需要组合结果列来匹配预期的类型修饰符以及基本类型(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.

例如，如果结果类型的列被声明为数值(30,2)，则不再允许在该列中返回其他精度的数值。以前的版本忽略了检查类型修饰符，因此允许实际上不符合声明的限制的结果行。

Two basic ways to fix your problem:

• You can cast the returned values to match the definition of patient_for_registrar:

  您可以将返回的值转换为patient_for_registrar的定义:

  nfa.patient_id_number::varchar(8)
  

• Or you can change the RETURNS clause. I would use RETURNS TABLE and declare a matching composite type. Here is an example.

  或者您可以更改return子句。我将使用返回表并声明匹配的复合类型。这是一个例子。

  RETURNS TABLE (patient_for_registrar varchar, col2 some_type, ...)
  

As an aside: I never use varchar if I can avoid it - especially not with length modifier. It offers hardly anything that the type text couldn't do. If I need a length restriction, I use a column constraint which can be changed without rewriting the whole table.

顺便说一句:如果我可以避免使用varchar，我就不会使用它——尤其是使用长度修饰符。它几乎提供了类型文本不能提供的任何内容。如果我需要一个长度限制，我使用一个列约束，这个约束可以在不重写整个表的情况下进行更改。