Re: Functions returning setof record -- can I use a table type as my return type hint?

On Fri, Aug 12, 2011 at 12:01 PM, George MacKerron
<g.j.mackerron@lse.ac.uk> wrote:
> On 12 Aug 2011, at 17:43, Merlin Moncure wrote:
>
>> you can't have it both ways. at the time the function call is
>> executed, the return type/fields must be known.  you can do this by
>> either a. explicitly defining the function return type or b.
>> describing the function return type in the function call, or c. use a
>> generic type to hold the output record structure which can be
>> parsed/dealt with later, like text or hstore.
>
> Thanks.
>
> I'm trying to do your option (a) -- defining the function return type.
>
> But I want to do this by referring to an existing table type -- which I know the returned fields must match -- rather
thanlaboriously retype the field definition list for that table. 
>
> The problem is that I can't make the database accept the table type as a field definition list, when that seems like
aperfectly sensible (and in this case much more convenient) way to define the fields that will be returned. 
>
> (With apologies for thoughtless top-posting in reply to Ray's earlier message).

yes -- you should be able to do this but you can't since there is no
way to transform the return type from record to type t outside the
function call.  your best bet is to apply method c. above:

postgres=# create type t as (a int, b int, c int);
CREATE TYPE

postgres=# create or replace function r() returns setof text as $$
begin
  return query select row(1,2,3)::t::text;
end; $$ language plpgsql;
CREATE FUNCTION

postgres=# select r();
    r
---------
 (1,2,3)
(1 row)

Time: 18.000 ms
postgres=# select r()::t;
    r
---------
 (1,2,3)
(1 row)

Time: 0.000 ms
postgres=# select (r()::t).*;
 a | b | c
---+---+---
 1 | 2 | 3
(1 row)

as a bonus you can call the function more flexibly since it returns a scalar.

merlin