postgresql - Cast 产生 'Returned type character varying does not match expected type character varying(8)'

Question

昨天我们将 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),
    ...

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

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]

列 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)),

有没有人有任何指点？

Answer 1

你的功能..

RETURNS SETOF patient_for_registrar

返回的行类型必须与声明的类型完全匹配。您没有透露 patient_for_registrar 的定义，可能是表的关联复合类型。我引用了关于 Declaration of Composite Types 的手册:

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.

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

varchar, varchar(n) and varchar(m)是 PostgreSQL 的不同数据类型。

旧版本不强制使用类型修饰符，但是 with PostgreSQL 9.0 this was changed for 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.

解决问题的两种基本方法:

• 您可以强制转换返回值以匹配 patient_for_registrar 的定义:

  nfa.patient_id_number::varchar(8)
  

• 或者您可以更改 RETURNS 子句。我会用 RETURNS TABLE并声明一个匹配的复合类型。这是一个 example .

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

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