Automatic conversion of anonymous records returned from an UDF to well known table-type

Question

Proprietary code (that we cannot change) has a bunch of user defined functions of the type:

create or replace function f() returns record as $$ ... $$

which we call in the following way (for example):

SELECT status, log FROM f() as (status boolean, log text);

(status boolean, log text) is a rowtype of table T. Is it possible to automatically convert the record (or setof record return type) into the T rowtype without listing the attributes? What I am looking for is of the kind:

SELECT * FROM f() as T%rowtype

Answer 1

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 command:

If the function has been defined as returning the record data type, then an alias or the key word AS must 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 T rowtype without listing the attributes?

Solution for single row

While a cast fails, an assignment in plpgsql works.

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 ...

db<>fiddle here
_{Old sqlfiddle}