如何从PL/pgSQL函数中返回多行？

Question

我花了很多时间试图找出问题所在，但是我没有能够解决它。所以，请你帮我一下。

我正在尝试编写一个返回多行的PL/pgSQL函数。我编写的函数如下所示。但是它没有起作用。

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"

- Asma Zubair

---

一如既往，您使用的Postgres版本是一个重要细节。 - Erwin Brandstetter

这个问题在 stackexchange上已经被回答。 - Eugen Konkov

Answer 1

在修复@Pavel指出的错误后，还要正确定义返回类型，否则您必须在每次调用时提供列定义列表。

这个调用：

SELECT * FROM get_object_fields()

假设Postgres知道如何扩展*。由于您正在返回匿名记录，因此会出现异常：

ERROR:  a column definition list is required for functions returning "record"

解决这个问题的一种方法（有多种）是使用RETURNS TABLE（Postgres 8.4+）：

CREATE OR REPLACE FUNCTION get_object_fields()
  <b>RETURNS TABLE (department_id int)</b> AS 
$func$
BEGIN
   RETURN QUERY
   SELECT department_id
   FROM   fact_department_daily 
   WHERE  report_date = '2013-06-07';
END
$func$ LANGUAGE plpgsql;

SQL函数同样适用。

相关：

• PostgreSQL：错误：42601：返回“record”的函数需要列定义列表

- Erwin Brandstetter

Answer 2

我看到了更多的错误：

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

SELECT * FROM get_object_fields()

其次，RETURN QUERY 直接将查询结果转发到输出。您无法将此结果存储到变量中 - 这在 PostgreSQL 中现在是不可能的。

BEGIN
  RETURN QUERY SELECT ....; -- 结果直接转发到输出 
  RETURN;   -- 不会有下一个结果，结束执行
END;

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

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

- Pavel Stehule

Answer 3

这里是一个方法

drop function if exists get_test_type();

drop type if exists test_comp;
drop type if exists test_type;
drop type if exists test_person;

create type test_type as (
  foo int, 
  bar int
);

create type test_person as (
  first_name text, 
  last_name text
);

create type test_comp as 
(
  prop_a test_type[], 
  prop_b test_person[]
);


create or replace function get_test_type()
returns test_comp
as $$
declare
  a test_type[];
  b test_person[];
  x test_comp;
begin

  a := array(
    select row (m.message_id, m.message_id) 
    from message m
  );

  -- alternative 'strongly typed'
  b := array[
    row('Bob', 'Jones')::test_person,
    row('Mike', 'Reid')::test_person
  ]::test_person[];

  -- alternative 'loosely typed'
  b := array[
    row('Bob', 'Jones'),
    row('Mike', 'Reid')
  ];

  -- using a select
  b := array (
    select row ('Jake', 'Scott')
    union all 
    select row ('Suraksha', 'Setty')
  );  

  x := row(a, b);

  return x;  
end;
$$
language 'plpgsql' stable;


select * from get_test_type();

- superlogical

Answer 4

创建或替换函数 get_object_fields() 返回表格 (department_id integer) AS $$ DECLARE result_record keyMetrics; BEGIN return QUERY SELECT department_id from fact_department_daily where report_date='2013-06-07'; --return result_record; END;

$$ LANGUAGE plpgsql;

SELECT * FROM get_object_fields()

- Ajay

---

目前你的回答不够清晰，请编辑并添加更多细节，以帮助其他人理解它如何回答问题。你可以在帮助中心找到有关如何编写好答案的更多信息。 - Community