Use of custom return types in a FOR loop in plpgsq

Question

The following code that I use returns an integer 1:

CREATE TYPE my_test AS (
   foo Integer
  );

CREATE FUNCTION foo_out() 
  RETURNS SETOF Integer AS
$$ 
BEGIN
RETURN QUERY
SELECT 1 as foo;
END
$$
LANGUAGE plpgsql;

CREATE FUNCTION foo1()
  RETURNS SETOF my_test
AS $$
DECLARE
    x my_test;
BEGIN
    FOR x IN    
        SELECT foo_out()
    LOOP     
        RETURN NEXT x;
    END LOOP;
END;
$$
LANGUAGE 'plpgsql';

select * from foo1();

But why does the same code return:

ERROR:  invalid input syntax for integer: (1)

if I change the return type to:

CREATE FUNCTION foo_out() 
  RETURNS SETOF my_test 

Which also should be an integer!? It could be the case that the system differs between the type integer and a custom type that includes an integer. What should be changed that the use of custom types is possible here?

Answer 1

my_test is a composite type, a row type, that contains a single integer field. It's not an integer.

When assigning to a record or row type in a FOR loop all output columns from the query form the row. Your SELECT needs to return a single, unnested integer column, which is then nested into a row type matching your custom type my_test.
You can also assign to a list of scalar variables in a FOR loop, in which case columns from the query are assigned left to right to variables as is - not forming a row.

If the column itself is a row type, you have one level of nesting to many. The text representation of a row containing an integer field is '(1)' (with parentheses!), and that's what you see in the error message.

You can fix that by extracting the integer field from the row with attribute notation:

SELECT (foo_out()).*

Or (more efficiently for multiple columns) by decomposing with:

SELECT * FROM foo_out()

Example Code

CREATE FUNCTION foo_out() 
  RETURNS SETOF my_test AS
$$ 
SELECT '(1)'::my_test
$$
LANGUAGE sql;

CREATE FUNCTION foo1()
  RETURNS SETOF my_test AS
$$
DECLARE
   x my_test;
BEGIN
   FOR x IN    
      SELECT * FROM foo_out()
   LOOP     
      RETURN NEXT x;
   END LOOP;
END
$$
LANGUAGE plpgsql;

SQL Fiddle.

Don't quote the language name plpgsql. It's an identifier.

Remember that looping is rarely needed, since most problems are more efficiently solved with a set-based approach (SQL only).

Related answers by Craig and Pavel:

• Passing array of a composite type to stored procedure