Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
That can't be the exact text of the function you're running because it doesn't compile; there's an excess semicolon after BEGIN and a missing one after the RETURN QUERY. After correcting those errors I confirm the error when returning record; will explain in answer. –  Craig Ringer Mar 26 '13 at 11:32
    
@CraigRinger I put the semicolon back in place. –  Stephane Rolland Mar 26 '13 at 11:35
add comment

1 Answer

up vote 2 down vote accepted

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)
share|improve this answer
    
Just tested, works perfect. And I like this syntax with returns table. –  Stephane Rolland Mar 26 '13 at 11:45
    
@StephaneRolland Updated with explanation of latter part of question too. –  Craig Ringer Mar 26 '13 at 11:49
    
thx for the support. Now it's much much clearer. –  Stephane Rolland Mar 26 '13 at 11:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.