Error: set_valued function called in context that cannot accept a set. What is it about?

Question

I use Postgresql 9.1, with ubuntu 12.04.

Inspired by Craig's answer to my question Concatenation of setof type or setof record I thought I would go well with using return query, setof record, and a series generator into this plpgsql function:

create or replace function compute_all_pair_by_craig(id_obj bigint)
    returns setof record as $$
begin
    return query select o.id, generate_series(0,o.value) from m_obj as o;     
end;
$$    language plpgsql;

During execution I get the error:

ERROR: set_valued function called in context that cannot accept a set

What is wrong ? Contrary to Craig I tell the function to return setof record.

I can achieve something that work doing exactly like Craig, i.e. by defining a type create type pair_id_value as (idx bigint, value integer) and have my plpgsql function returns a setof of pair_id_value instead of a setof record.

But even with this working solution, I still don't understand why select id, generate_series(0,13) alone will return a result in two columns... and on the contrary calling the function (returns setof pair_id_value) with return query select id, generate_series(0,my_obj.value) from my_obj will return a result in only one column which field look like this "(123123,0)" "(123123,1)" "(123123,2)" (3 rows) which are tuples obviously.

Is it a case where a temporary table must/should be created ?

Answer 1

The error message isn't very helpful:

regress=> SELECT * FROM  compute_all_pair_by_craig(100);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM  compute_all_pair_by_craig(100);

but if you rephrase the query to call it as a proper set-returning function you'll see the real problem:

regress=> SELECT * FROM compute_all_pair_by_craig(100);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM compute_all_pair_by_craig(100);

If you're using SETOF RECORD without an OUT parameter list you must specify the results in the calling statement, eg:

regress=> SELECT * FROM compute_all_pair_by_craig(100) theresult(a integer, b integer);

However, it's much better to use RETURNS TABLE or OUT parameters. With the former syntax your function would be:

create or replace function compute_all_pair_by_craig(id_obj bigint)
    returns table(a integer, b integer) as $$
begin
    return query select o.id, generate_series(0,o.value) from m_obj as o;     
end;
$$ language plpgsql;

This is callable in SELECT-list context and can be used without creating a type explicitly or specifying the result structure at the call site.

---

As for the second half of the question, what's happening is that the 1st case specifies two separate columns in a SELECT-list, wheras the second returns a single composite. It's actually not to do with how you're returning the result, but how you're invoking the function. If we create the sample function:

CREATE OR REPLACE FUNCTION twocols() RETURNS TABLE(a integer, b integer) 
AS $$ SELECT x, x FROM generate_series(1,5) x; $$ LANGUAGE sql;

You'll see the difference in the two ways to call a set-returning function - in the SELECT list, a PostgreSQL specific non-standard extension with quirky behaviour:

regress=> SELECT twocols();
 twocols 
---------
 (1,1)
 (2,2)
 (3,3)
 (4,4)
 (5,5)
(5 rows)

or as a table in the more standard way:

regress=> SELECT * FROM twocols();
 a | b 
---+---
 1 | 1
 2 | 2
 3 | 3
 4 | 4
 5 | 5
(5 rows)