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
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
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
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 ?
BEGINand a missing one after the
RETURN QUERY. After correcting those errors I confirm the error when returning
record; will explain in answer.