mysql - 错误 : structure of query does not match function result type

Question

我正在尝试将 mySQL 存储过程转换为 postgrSQL 存储函数。我是 postgreSQL 的新手。我第一次尝试将连接存储过程转换为存储函数。但是我收到以下错误

ERROR:  structure of query does not match function result type
DETAIL:  Returned type text does not match expected type character varying in column 3.
CONTEXT:  PL/pgSQL function getsummarypagecontentsurveyform(numeric) line 3 at RETURN QUERY 

存储过程

CREATE PROCEDURE [dbo].[GetSummaryPageContentSurveyForm]     
    @nRoomAllocationID bigint
AS
BEGIN
   SELECT  Employee.sEmpName, RoomInvestigatorMapping.sComment,
           Employee.sEmpName + ' : ' + RoomInvestigatorMapping.sComment as CommentsToDisplay
   FROM    RoomInvestigatorMapping INNER JOIN Employee 
        ON RoomInvestigatorMapping.nInvestigatorID = Employee.nEmpID
   Where    RoomInvestigatorMapping.nRoomAllocationID = @nRoomAllocationID 
END   
GO

商店功能

CREATE OR REPLACE FUNCTION GetSummaryPageContentSurveyForm (        
    p_nroom_allocation_id numeric)

    RETURNS Table(res_semp_name character varying,res_scomment character varying,
                  res_comments_to_display character varying)
AS $$
BEGIN
 Return Query    
   SELECT  employee.semp_name, roominvestigatormapping.scomment,
           employee.semp_name || ' : ' || roominvestigatormapping.scomment as comments_to_display
   FROM    roominvestigatormapping INNER JOIN employee 
           ON roominvestigatormapping.ninvestigator_id = employee.nemp_id
   Where   roominvestigatormapping.nroom_allocation_id = p_nroom_allocation_id; 
END;

$$ LANGUAGE plpgsql;

Answer 1

PostgreSQL 函数必须有定义的结果类型。运行时检查输出是否与定义的类型相同。在您的情况下，第三个表达式返回文本而不是 varchar。您需要将此表达式显式转换为 varchar:

$$
BEGIN
  RETURN QUERY    
    SELECT  employee.semp_name, roominvestigatormapping.scomment,
         (employee.semp_name || ' : ' || roominvestigatormapping.scomment)::varchar as comments_to_display
      FROM    roominvestigatormapping INNER JOIN employee 
        ON roominvestigatormapping.ninvestigator_id = employee.nemp_id
     WHERE   roominvestigatormapping.nroom_allocation_id = p_nroom_allocation_id; 
END;
$$

我使用了转换运算符 :: :

somevalue::varchar