Postgresql 返回 setof 记录

Question

我有一个自定义类型:

create type some_type as (
    some_bool_param     boolean, 
    str                 varchar
);

我用这种类型的字段创建了一个表并插入了一些数据:

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'));

现在我尝试返回 setof some_type 数据:

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;

我调用 get_str():

select * from get_str();

但是报错:

ERROR:  error in boolean type value: "(f,"First str")"
CONTEXT:  PL/pgSQL function "get_str" line 4 at FOR by result of SELECT

我该如何解决？

Answer 1

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;

以防万一:声明一个表也声明了它的行类型，所以你不需要在这里单独的CREATE TYPE。这也行得通:

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;