如何在PostgreSQL的函数中返回查询结果的行？ [英] How to return rows of query result in PostgreSQL's function?

问题描述

我已经尝试了很多教程，但是都失败了。
有人可以给我一些例子吗？
这是我的代码，它提示错误：无效的类型名称'SETOF RECORD'

I've tried following tutorials for many times but failed. Could someone give me some examples please? Here is my code, it prompts that "ERROR:invalid type name 'SETOF RECORD'"

create or replace function find() returns SETOF RECORD
as $$
declare A SETOF RECORD;
begin
    A=(
        select x,y
        from .......

    )
    CASE WHEN EXISTS A 
    THEN returns query A
    ELSE returns query (
        select x,y
        from ......
    )
    END;

end;
$$ language plpgsql;

推荐答案

要声明集合返回函数的方式，我记得时刻：

Ways to declare set returning function that I remember at the moment:

--example 1
create or replace function test() returns SETOF RECORD
as $$
begin
    RETURN QUERY SELECT * FROM generate_series(1,100);
end;
$$ language plpgsql;
--test output
select * from test() AS a(b integer)

--example 2
create or replace function test2() returns TABLE (b integer)
as $$
begin
    RETURN QUERY SELECT * FROM generate_series(1,100);
end;
$$ language plpgsql;
--test output
select * from test2()

--example 3
create or replace function test3() returns SETOF RECORD
as $$
declare
  r record;
begin
    FOR r IN SELECT * FROM generate_series(1,100) LOOP
      RETURN NEXT r;
    END LOOP;
end;
$$ language plpgsql;
--test output
select * from test3() AS a(b integer);

--example 4
create or replace function test4() returns setof record
as $$
    SELECT * FROM generate_series(1,100)
$$ language sql;
--test output
select * from test4() AS a(b integer);

--example 5
create or replace function test5() returns setof integer
as $$
begin
    RETURN QUERY SELECT * FROM generate_series(1,100);
end;
$$ language plpgsql;
--test output
select * from test5()

--example 6
create or replace function test6(OUT b integer, OUT c integer) RETURNS SETOF record
as $$
begin
    RETURN QUERY SELECT b.b, b.b+3 AS c FROM generate_series(1,100) AS b(b);
end;
$$ language plpgsql;
--test output
select * from test6()

这篇关于如何在PostgreSQL的函数中返回查询结果的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！