Postgresql return setof record

Question

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?

Solution 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;

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;

Solution 2

You don't need a loop with current versions of PostgreSQL any more for that kind of function. Use RETURN QUERY instead:

CREATE OR REPLACE FUNCTION get_str()
  RETURNS SETOF test_table AS
$func$
BEGIN
   RETURN QUERY
   TABLE test_table;  -- shorthand for: SELECT * FROM test_table
END
$func$  LANGUAGE plpgsql;

Or an SQL function like @Quassnoi provided. Note that this returns a nested composite type (SETOF test_table, not SETOF some_type).

Oddly, this works as well:

CREATE OR REPLACE FUNCTION get_str5()
  RETURNS SETOF some_type AS
$func$
   TABLE test_table;
$func$ LANGUAGE sql;

The outer row wrapper is silently removed, which is a bit quirky of Postgres to say the least.

About the error

Your function would work like this:

CREATE OR REPLACE FUNCTION get_str()
  RETURNS TABLE (strs some_type) AS
$func$
BEGIN
   FOR strs IN
       SELECT (t.strs).* FROM test_table t
   LOOP
      RETURN NEXT r;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Note the bold part. Assignment to a row or record type in a FOR loop is done subfield by subfield. That's typically convenient, but gets confusing when working with nested composite types.

You have a composite type (some_type) inside a table row (test_table) - two levels of nesting. That's typically not what one would want, but that's what you presented us with.

You need to decompose / unwrap twice.

• Your original code only decomposes the outer row wrapper with select * from test_table.

• @Quassnoi's suggested fix select strs from test_table fails in the same way: strs is decomposed from the table row by direct reference, but strs is still a row type that needs to be decomposed itself before assignment.

Solution 3

According to its declaration, the function get_str() is supposed to return a set of record with the some_type structure, which is 2 columns with 1 boolean and 1 varchar.

Wile the function actually returns a set of record having 1 column which is typed some_type.

Try by replacing return next r; with return next r.strs;

Comments

[Erwin Brandstetter]

The suggested fix for the loop doesn't actually work.