Postgres函数结束循环并返回错误 [英] Postgres Function End Loop and return Error

问题描述

我尝试创建此函数，但系统返回"LOOP错误"，并且我不知道如何同时返回3个变量.我已经尽力解决了这个问题，但是我在任何地方都找不到答案.

I have tried to create this function but the system is returning a "LOOP error" and I don't know how to return 3 variables at the same time. I've tried hard to figure this out but I didn't find an answer anywhere.

CREATE OR REPLACE FUNCTION conta_relatos(fator_normativo integer, fator_determinativo integer)
  RETURNS integer AS
$BODY$
DECLARE
    vinculos_encontrados RECORD;
    rel_pri INT;
rel_sec INT;
rel_ref INT;
no_item INT;
tipo_relato TEXT;
BEGIN
    rel_pri := 0;
    rel_sec := 0;
    rel_ref := 0;    
FOR vinculos_encontrados IN SELECT * FROM "Vinculos" WHERE ("Vinculos"."Fator_Normativo" =   Fator_Normativo AND "Vinculos"."Fator_Determinativo" = Fator_Determinativo) LOOP
    no_item := vinculos_encontrados."Item";
SELECT "Fontes"."Tipo_Relato" INTO tipo_relato FROM "Fontes" WHERE "Fontes"."ID" = no_item;
--IF tipo_relato = "1 - Relato Primário" THEN 
   rel_pri := rel_pri + 1;
--ELSE IF tipo_relato = "2 - Relato Secundário" THEN 
   rel_sec := rel_sec + 1;
--ELSE IF tipo_relato = "3 - Relato Referencial" THEN 
   rel_ref := rel_ref + 1;
    --END IF;
    END LOOP;
    RETURN rel_pri, rel_sec, rel_ref;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

推荐答案

使用 OUT参数可返回包含多列的单行.在这种情况下，RETURN类型是可选的，我在此处:

Use OUT parameters to return a single row with multiple columns. The RETURN type is optional in this case, I quote the manual here:

当有OUT或INOUT参数时，可以省略RETURNS子句.

When there are OUT or INOUT parameters, the RETURNS clause can be omitted.

CREATE OR REPLACE FUNCTION conta_relatos(
    _fator_normativo integer
   ,_fator_determinativo integer
   ,OUT rel_pri integer
   ,OUT rel_sec integer
   ,OUT rel_ref integer
   ) AS
$func$
DECLARE
   tipo_relato text;
BEGIN

rel_pri := 0;
rel_sec := 0;
rel_ref := 0;

FOR tipo_relato IN
   SELECT f."Tipo_Relato"
   FROM   "Vinculos" v
   JOIN   "Fontes"   f ON f."ID" = v."Item"
   WHERE  v."Fator_Normativo" = _fator_normativo
   AND    v."Fator_Determinativo" = _fator_determinativo
LOOP
   CASE tipo_relato
   WHEN '1 - Relato Primário' THEN 
      rel_pri := rel_pri + 1;
   WHEN '2 - Relato Secundário' THEN 
      rel_sec := rel_sec + 1;
   WHEN '3 - Relato Referencial' THEN 
      rel_ref := rel_ref + 1;
   END CASE;
END LOOP;

-- No RETURN needed, OUT parameters are returned automatically.

END
$func$ LANGUAGE plpgsql;

致电:

SELECT * FROM conta_relatos(1,2);

我也大大简化了您的功能.除其他外:

I also largely simplified your function. Among others:

• 将简单案例" 用于您的作业.
• 通过联接将两个查询简化为一个查询.

• Use "Simple CASE" for your assignments.
• Simplify two queries into one with a join.

可以很容易地将整个函数重写为单个SQL语句.

The whole function could easily be rewritten as a single SQL statement.

这篇关于Postgres函数结束循环并返回错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！