Thread: Polymorphic "setof record" function?
Hi list,
I have written a function that returns a setof record. The function has
a table name as a parameter and the resulting records have the same
structure as this table. Is there any easy way to specify this when I
call the function? If the table has many columns then it's annoying to
specify all of 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?
Regards,
Christian
--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen
Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
2009/1/13 Christian Schröder <cs@deriva.de>:
> Hi list,
> I have written a function that returns a setof record. The function has a
> table name as a parameter and the resulting records have the same structure
> as this table. Is there any easy way to specify this when I call the
> function? If the table has many columns then it's annoying to specify all of
> 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?
>
Can make the function with text parametter (table name) and
search in the catalog for the table?. Then you will have the table
to get %TYPE...
> Regards,
> Christian
>
> --
> Deriva GmbH Tel.: +49 551 489500-42
> Financial IT and Consulting Fax: +49 551 489500-91
> Hans-Böckler-Straße 2 http://www.deriva.de
> D-37079 Göttingen
>
> Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Emanuel Calvo Franco
ArPUG / AOSUG Member
Postgresql Support & Admin
On Tue, Jan 13, 2009 at 02:50:49PM +0100, Christian Schrrrder wrote:
> I have written a function that returns a setof record. The function has
> a table name as a parameter and the resulting records have the same
> structure as this table. Is there any easy way to specify this when I
> call the function? If the table has many columns then it's annoying to
> specify all of them.
I'm struggling to understand PG as well. I'd expect to be able to
use the normal :: cast operator, but it doesn't seem to function as
expected. I came up with the following code:
CREATE TEMP TABLE foo (
cola INTEGER, colb TEXT
);
INSERT INTO foo (cola, colb) VALUES
(1, 'hi'), (2, 'bye'),
(3, 'hello'), (4, 'testing');
SELECT (x::foo).cola
FROM (SELECT x::record FROM foo x LIMIT 10) x;
CREATE FUNCTION bar() RETURNS SETOF RECORD LANGUAGE SQL AS $$
SELECT * FROM foo LIMIT 10; $$;
SELECT (x::foo).cola FROM (
SELECT bar()) AS xxx(x);
I get "cannot cast type record to foo" from the final query, which I
don't understand at all. It suggests that casting something to a value
of type RECORD sometimes keeps the real type information around, and
sometimes not. Why?
Sam
On 1/13/09, Christian Schröder <cs@deriva.de> wrote:
> Hi list,
> I have written a function that returns a setof record. The function has a
> table name as a parameter and the resulting records have the same structure
> as this table. Is there any easy way to specify this when I call the
> function? If the table has many columns then it's annoying to specify all of
> 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++.
This is _not_ the same as polymorhphic functions(anyelement, etc),
because you would _end_up_with_as_separate_plan_per_type_ (and other
reasons). Polymorphic functions are more similar to how inheritance
in c++ works...you operate on the 'base' type.
The type inferring operator (%type) is only part of the problem, you
need to be able to create functions that it is known to the planner
that it's template style:
IMHO, this is a better abstraction than our current anyX pseudotypes,
outside of the anyarray tricks.
merlin
On Wed, 14 Jan 2009 11:46:29 -0500
"Merlin Moncure" <mmoncure@gmail.com> wrote:
> On 1/13/09, Christian Schröder <cs@deriva.de> wrote:
> > Hi list,
> > I have written a function that returns a setof record. The
> > function has a table name as a parameter and the resulting
> > records have the same structure as this table. Is there any easy
> > way to specify this when I call the function? If the table has
> > many columns then it's annoying to specify all of 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++.
> This is _not_ the same as polymorhphic functions(anyelement, etc),
> because you would _end_up_with_as_separate_plan_per_type_ (and
> other reasons). Polymorphic functions are more similar to how
> inheritance in c++ works...you operate on the 'base' type.
> The type inferring operator (%type) is only part of the problem,
> you need to be able to create functions that it is known to the
> planner that it's template style:
>
> IMHO, this is a better abstraction than our current anyX
> pseudotypes, outside of the anyarray tricks.
I still haven't got the time to use them, but wouldn't refcursor
help?
Unfortunately I didn't find very enlightening examples of refcursors
use around.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
Merlin Moncure wrote:
> On 1/13/09, Christian Schröder <cs@deriva.de> wrote:
>
>> Hi list,
>> I have written a function that returns a setof record. The function has a
>> table name as a parameter and the resulting records have the same structure
>> as this table. Is there any easy way to specify this when I call the
>> function? If the table has many columns then it's annoying to specify all of
>> 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".
Regards,
Christian
--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen
Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
On Thu, Jan 15, 2009 at 4:57 AM, Christian Schröder <cs@deriva.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