Re: Polymorphic "setof record" function?

On Thu, Jan 15, 2009 at 4:57 AM, Christian Schröder <cs(at)deriva(dot)de> wrote:
> Merlin Moncure wrote:
>>> them.
>>> I need something like:
>>> select * from myfunc('mytable') as x(like mytable)
>>> or
>>> select * from myfunc('mytable') as x(mytable%TYPE)
>>>
>>> Is there any solution for PostgreSQL 8.2?
>>>
>>
>> Unfortunately to the best of my knowledge there is no way to do this.
>> I think what you want is to have sql functions that specialize on
>> type in the way that templates do in C++.
>>
>
> That would certainly be the best solution, but I would also be happy with
> some syntactic sugar: The function may still be declared as returning a set
> of records, so that I would still have to declare their actual return type
> in the query. However, I would like to have an easy way to express: "the
> record will have the same structure as table x".

There is a circuitous way to do this that sometimes works. Declare
your function to return text and do this inside the function body (for
example):

create or replace function func() returns text as
$$
select foo::text from foo limit 5;
$$ language sql;

select func::foo from (select func()) q;

Couple of notes here:
*) obviously, the idea here is to use dynamic-sql to return different
table types based on inputs
*) can only upcast to one table per function call (but can return
varying record types based if left in text)
*) record::text casts I think were introduced in 8.3. There is a more
complex way to do it in 8.2 that is probably not worth the effort.
*) record::text casts are not really reflexive. null fields are an
issue or example.

merlin