Search Postgresql Archives

Re: Polymorphic "setof record" function?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Jan 15, 2009 at 4:57 AM, Christian Schröder <cs@xxxxxxxxx> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux