CODEWITHSUNDEEP
postgresqlpostgresql-9.3
Chen
Chen

Reputation: 191

How to return rows of query result in PostgreSQL's function?

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;

Upvotes: 3

Views: 14569

Answers (1)

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

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()

Upvotes: 13

Related Questions