Postgresql return setof record

boolean, plpgsql, postgresql, row, select

Solution

create or replace function get_str() returns setof some_type as
$$
declare
    r some_type;
begin
    for r in 
        select strs from test_table loop
    return next r;
    end loop;
    return;
end;

Just in case: declaring a table declares its rowtype as well, so you don't need a separate `CREATE TYPE` here. This would work as well:

create table test_table (
    some_bool_param     boolean, 
    str                 varchar
);

insert into test_table values (false, 'First str');
insert into test_table values (false, 'Second str ');
insert into test_table values (false, 'Third str');
insert into test_table values (false, 'Yet another str');

create or replace function get_str()
returns setof test_table as
$$
    SELECT  *
    FROM    test_table;
$$
LANGUAGE sql;

Problem

I have a custom type: ``` create type some_type as ( some_bool_param boolean, str varchar ); ``` I create a table with fields of this type and insert some data: ``` create table test_table ( strs some_type ); insert into test_table(strs) values ((false, 'First str')) , ((false, 'Second str ')) , ((false, 'Third str')) , ((false, 'Yet another str')); ``` And now I try to return setof `some_type` data: ``` create or replace function get_str() returns setof some_type as $$ declare r some_type; begin for r in select * from test_table loop return next r; end loop; return; end; ``` I call `get_str()`: ``` select * from get_str(); ``` But get an error: ``` ERROR: error in boolean type value: "(f,"First str")" CONTEXT: PL/pgSQL function "get_str" line 4 at FOR by result of SELECT ``` How can I fix it?

Original source