postgresql - RETURN NEXT 出错

Question

对于给菜鸟带来的不便，我们深表歉意。在结果数据的指定出现一些问题后，我收到以下消息:错误:查询“SELECT _Patient_Name、_Doc_Name、_Specialty、_Total_visit、_Total_price、_Max_price”返回了 6 列上下文:PL/pgSQL 函数 list_top_patients_visit() 第 35 行，返回下一个

运行这个函数时:

CREATE OR REPLACE FUNCTION list_top_Patients_Visit()
RETURNS SETOF PatientsList AS $$

DECLARE
_Pat_Number visit.Pat_Number%type;
_Patient_Name patient.NAme%type; 
_Doc_Number doctor.Doc_Number%type;
_Doc_Name doctor.Name%type;
_Specialty doctor.Specialty%type;
_Total_visit INTEGER;
_Total_price visit.Price%type;
_Max_price visit.Price%type;
_Max_metge INTEGER;

BEGIN


FOR _Pat_Number IN (SELECT t_Pat_Number FROM (SELECT v.pat_number as t_Pat_Number, count((v.pat_number)) as t_Total_visit
FROM visit v, patient p
GROUP BY v.pat_number, p.Name
ORDER BY t_Total_visit DESC, v.pat_number
LIMIT 5) A)
    LOOP
        SELECT INTO  _Patient_Name, _Doc_Name, _Specialty, _Total_visit, _Total_price, _Max_price, _Max_metge Patient.Name  , doctor.name  , doctor.specialty  , count((visit.pat_number)) ,  sum(visit.price)  , max(visit.price)  ,  count((visit.doc_number)) as max_metge
        FROM visit, doctor, patient
        WHERE visit.pat_number=_Pat_Number and visit.doc_number=doctor.doc_number and patient.pat_number=_Pat_Number
        GROUP BY visit.doc_number, doctor.name, specialty, patient.Name
        ORDER BY max_metge DESC, visit.doc_number
        LIMIT 1;
        RETURN NEXT  _Patient_Name, _Doc_Name, _Specialty, _Total_visit, _Total_price, _Max_price;
    END LOOP;

END;
$$LANGUAGE plpgsql; 

---

PatientsList 在哪里

CREATE TYPE PatientsList AS (
t_Patient_Name VARCHAR(50) ,
t_Doc_Name VARCHAR(50) ,
t_Specialty VARCHAR(50),
t_Total_visit INTEGER,
t_Total_price DECIMAL(7,2),
t_Max_price DECIMAL(7,2));

我不明白 ERROR 的含义，因为 TYPE 是用 6 个参数精确定义的。

Answer 1

出于某种原因，PL/pgSQL 坚持在返回它们之前明确地将它们组合成一个复合值:

RETURN NEXT (_Patient_Name, _Doc_Name, _Specialty, _Total_visit, _Total_price, _Max_price);

但在您的情况下，您可以摆脱那些中间变量并直接返回查询结果(此处不需要括号):

RETURN QUERY
  SELECT Patient.Name, doctor.name, doctor.specialty ...