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 can do something along the following lines:
test=> CREATE OR REPLACE FUNCTION temptabl(cnt integer)
RETURNS SETOF integer AS
$body$
BEGIN
CREATE TEMPORARY TABLE tmp_container ON COMMIT DROP AS
SELECT a
FROM generate_series(1, cnt) t(a);
IF (SELECT count(1) FROM tmp_container) > 5
THEN
RETURN QUERY SELECT a FROM tmp_container;
END IF;
END;
$body$
LANGUAGE plpgsql;
test=> SELECT * FROM temptabl(4);
temptabl
----------
(0 rows)
test=> SELECT * FROM temptabl(6);
temptabl
----------
1
2
3
4
5
6
(6 rows)
This way you have to perform your original query only once. All other statements work on the temporary table.
Best Answer
No, I am afraid that's not possible. I have been wishing this was possible myself on several occasions. Either you have a registered row (composite) type that matches the return type or you have to list columns individually.
There is a somewhat awkward workaround with returning a nested composite type, but it's not exactly the same. See:
Or you create a composite type:
A
VIEW
also registers its row type automatically. For the simple test case you present, you might just use a view instead of the function to begin with (like @a_horse commented):Or you create the view for the purpose of registering the row type conveniently. You might add
LIMIT 0
to document that the view is not meant to return rows, but that's optional.Be aware that this introduces a dependency (like with any other view) and Postgres will prevent dropping the table and some other manipulations, unless you drop depending views first.