如何从PL/pgSQL函数返回多行? [英] How to return multiple rows from PL/pgSQL function?

问题描述

我花了很多时间试图解决这个问题，但仍无法解决.所以，请帮忙.

I have spent good amount of time trying to figure it out and I haven't been able to resolve it. So, I need your help please.

我正在尝试编写一个返回多行的PL/pgSQL函数.我写的函数如下所示.但这不起作用.

I am trying to write a PL/pgSQL function that returns multiple rows. The function I wrote is shown below. But it is not working.

CREATE OR REPLACE FUNCTION get_object_fields()
RETURNS SETOF RECORD
AS 
$$
DECLARE result_record keyMetrics;
BEGIN
    return QUERY SELECT department_id into result_record.visits 
    from fact_department_daily 
    where report_date='2013-06-07';
    --return result_record;
END

$$ LANGUAGE plpgsql; 

SELECT * FROM get_object_fields;

它返回此错误:

错误:RETURN不能在函数返回集中设置参数；
在"QUERY"或附近使用RETURN NEXT

ERROR: RETURN cannot have a parameter in function returning set;
use RETURN NEXT at or near "QUERY"

推荐答案

我看到了更多错误:

首先，SET RETURNING FUNCTIONS调用具有以下语法

first, a SET RETURNING FUNCTIONS call has following syntax

SELECT * FROM get_object_fields()

second-RETURN QUERY将查询结果直接转发到输出.您不能将此结果存储为变量-现在在PostgreSQL中是不可能的.

second - RETURN QUERY forwards query result to output directly. You cannot store this result to variable - it is not possible ever in PostgreSQL now.

BEGIN
  RETURN QUERY SELECT ....; -- result is forwarded to output directly
  RETURN;   -- there will not be any next result, finish execution
END;

第三-这些简单的函数最好用SQL语言实现

third - these simple functions is better to implement in SQL languages

CREATE OR REPLACE FUNCTION get_object_fields()
RETURNS SETOF RECORD AS $$
SELECT department_id WHERE ...
$$ LANGUAGE sql STABLE;

这篇关于如何从PL/pgSQL函数返回多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！