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)
You should return something when nothing is found. Before exception comment in the statement.
The way your routine is written it may does find nothing and then hits the end of the function without returning anything, hence the error function returned without value
.
Workaround :
Handle it with the exception
exception
when TOO_MANY_ROWS then
return 'Error bla bla '; -- do something meaningful here
when NO_DATA_FOUND then
return null; -- if you want to return null on nothing found
Best Answer
What you see is obviously a display bug in the current pgAdmin 1.20. I could reproduce it.
You must be aware that the code you see is re-engineered from system table entries.
If you look up your function in the system catalogs directly, you'll find that your the return type has been registered properly (at least it works for me in pg 9.4):
More useful functions like
pg_get_functiondef()
in the manual.You might want to ask for this on the pgAdmin support list:
http://www.postgresql.org/list/pgadmin-support/
Mail to pgadmin-support@postgresql.org
Your approach with
RETURNS TABLE(identifier int)
is the right way.RETURNS SETOF
does not take a parameter name, unless you combine it with anOUT
parameter. Like so: