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

On 12 Aug 2011, at 19:17, 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.
>>
>> 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 than laboriously 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 a perfectly sensible (and in this case much more convenient) way to define the fields that will be returned.
>
> 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.

OK, I think that answers my question. Slightly disappointing. Presumably it wouldn't be too hard to implement (?), but perhaps it's not needed often enough to be worth it.

> 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

Thanks -- this looks like a decent workaround.

Please access the attached hyperlink for an important electronic communications disclaimer: http://lse.ac.uk/emailDisclaimer