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
  • 1
    If the function does not have arguments, you could wrap that into a view.
    – user1822
    Nov 5, 2013 at 17:26
  • 1
    Or you could just as well wrap these into functions in an other schema but an identical signature. Or if you know the source, create your own, usable set of them.
    – dezso
    Nov 5, 2013 at 21:28
  • Technically, I think, what you suggest is not possible.
    – dezso
    Nov 5, 2013 at 21:35
  • @dezso: I found a way for functions returning a single row. Nov 6, 2013 at 16:31
  • @ErwinBrandstetter Yes, and it is as simple as it can be, but still a wrapping :). Assignment to and from record/row variables are full of surprises by the way.
    – dezso
    Nov 6, 2013 at 21:06

1 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


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()::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:

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
   rec := f();   -- assignment succeeds where cast failed (!)



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

  • Thanks a lot for the answer. Your solution to "cast" the signature of the UDF so it returs a table (row) type instead of the record type works. In addition I must say that I successfully used this approach to return a set of records too. In your answer one must replace the line CREATE OR REPLACE function f1(OUT rec t) AS table type with CREATE OR REPLACE function f1() returns setof T as....
    – arthur
    Nov 6, 2013 at 9:15
  • The posted question is a simplification of the original problem that I indeed have. My original problem is that the function takes one parameter of type regclass as input and returns a setof records (the row type of the corresponding input regclass). In this case I cannot create a wrapper function since the return type is known only at the execution time, can't I?
    – arthur
    Nov 6, 2013 at 9:21
  • @arthur: That's tricky stuff. I suggest you create a new question with all the necessary details. You can always reference this one for context. I think I have a solution for that ... Nov 6, 2013 at 15:48
  • @arthur: I would also be very interested in how you managed to SELECT from a set returning function without supplying a column definition list. Can you post an answer with a code example here? Nov 6, 2013 at 16:19
  • I do not have an answer how one can do a select query for a function returning setof record. I was thinking of possible workarounds and created inserting-values-from-a-record-variable-into-a-table ticket. selecting a function returning setof T is relatively straight forward. But I assume that your expression set retuning function indeed means setof record returning function...
    – arthur
    Nov 6, 2013 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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