我正在使用PL / pgsql创建一个SP：
CREATE OR REPLACE FUNCTION get_performance_achieve (p_month INT,p_year INT,p_uid INT)
RETURNS TABLE (
field1 INT,field2 INT
)
AS $datarows$
DECLARE var_r record;
BEGIN
field1 :=0;
field2 :=0;
FOR var_r IN(select COUNT(id) as counter from "TABLE_A"
)
LOOP
field1 := (var_r.counter) ;
RETURN NEXT;
END LOOP;
FOR var_r IN(select COUNT(id) as counter from "TABLE_B"
)
LOOP
field2 := (var_r.counter) ;
RETURN NEXT;
END LOOP;
END;
$datarows$
LANGUAGE 'plpgsql';
当我执行SP时,它将返回2条记录：
> field1,0
> field1,field2
如何只返回field1,field2的单个记录
解决方法
在这种情况下,循环是多余的,使用简单的赋值：
CREATE OR REPLACE FUNCTION get_performance_achieve (p_month INT,p_uid INT)
RETURNS TABLE (
field1 INT,field2 INT
)
AS $datarows$
BEGIN
field1 := (select COUNT(id) as counter from "TABLE_A");
field2 := (select COUNT(id) as counter from "TABLE_B");
RETURN NEXT;
END;
$datarows$
LANGUAGE 'plpgsql';
