There is a way.
Given a table t and a function f() that returns an anonymous record that would match that table type:
CREATE TABLE t (id int, d date);
You cannot just cast the anonymous record, since a column definition list is required for
SELECT * FROM f()
Quoting the manual on the SELECT commandthe manual on the SELECT command:
If the function has been defined as returning the record data type, then an alias or the key word
ASmust be present, followed by a column definition list in the form ...
Bold emphasis mine.
So, while all of these queries work:
SELECT '(1,2013-11-11)'::t;
SELECT ('(1,2013-11-11)'::t).*;
SELECT f(); -- returning anonymous record
SELECT * FROM f() AS f(id int, d date);
Neither of these do:
SELECT * FROM f();
SELECT * FROM f()::t;
The latter raising an exception:
ERROR: cannot cast type record to t
You could wrap the SELECT with column definition list into a VIEW or function like @a_horse and @deszo suggested. That would work just fine:
CREATE OR REPLACE VIEW v1 AS
SELECT * FROM f() AS f(id int, d date);
But that wouldn't answer your question:
convert the record (or setof record return type) into the
Trowtype without listing the attributes?
Solution for single row
###Solution for single row WhileWhile a cast fails, an assignment in plpgsql works.
CREATE OR REPLACE function f1(OUT rec t) AS
$func$
BEGIN
rec := f(); -- assignment succeeds where cast failed (!)
END
$func$ LANGUAGE plpgsql;
CREATE OR REPLACE function f1(OUT rec t)
LANGUAGE plpgsql AS
$func$
BEGIN
rec := f(); -- assignment succeeds where cast failed (!)
END
$func$;
Call:
SELECT * FROM f1();
While you can also write a set returning function with that pattern I did not find a way to SELECT from a set returning function without supplying a column definition list ...
->SQLfiddledb<>fiddle here
Old sqlfiddle
