Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
If the function does not have arguments, you could wrap that into a view. –  a_horse_with_no_name Nov 5 '13 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 '13 at 21:28
    
Technically, I think, what you suggest is not possible. –  dezso Nov 5 '13 at 21:35
    
@dezso: I found a way for functions returning a single row. –  Erwin Brandstetter Nov 6 '13 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 '13 at 21:06
show 1 more comment

1 Answer

up vote 2 down vote accepted

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) AS
$func$
BEGIN
rec := f();   -- assignment succeeds where cast failed (!)
END
$func$ LANGUAGE plpgsql;

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

->SQLfiddle

share|improve this answer
    
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 '13 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 '13 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 ... –  Erwin Brandstetter Nov 6 '13 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? –  Erwin Brandstetter Nov 6 '13 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 '13 at 17:22
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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