Обсуждение: Functions returning complex types.

I'm trying to use a function that returns a complex type. I have no problem
creating the function but when I try to use it I get the message:

ERROR:  function in FROM has unsupported return type

Apparently, this message stems from the parser. Changing the function so
that it returns a SETOF the same complex type works fine.

Is this the expected behavior? Is SETOF a requirement when using complex
types?

Regards,

- thomas

"Thomas Hallgren" <thhal@mailblocks.com> writes:
> I'm trying to use a function that returns a complex type. I have no problem
> creating the function but when I try to use it I get the message:

> ERROR:  function in FROM has unsupported return type

AFAICS it's not possible to get that message for a function returning a
composite type.  You'd better show exactly what you did.
        regards, tom lane

The unsupported return type was all my fault. The Form_pg_type typrelid
attribute points to the class of the relation, not the relation as such.
Duh...

But now, when I actually can return complex types, I encounter another
problem. It happens when I pass a complex type returned from one function as
a complex parameter to another function, i.e. something like:

SELECT printMyComplexType(obtainMyComplexType());

Some research shows that the TupleTableSlot* that I create in
obtainMyComplexType() using the following code:
  TupleDesc tupleDesc = TypeGetTupleDesc(typeId, NIL);  TupleTableSlot* slot = TupleDescGetSlot(tupleDesc);  return
TupleGetDatum(slot,tuple);
 

is exactly the same TupleTableSlot* that is passed into my
printMyComplextType function. This is of course extremely bad since the
MemoryContext where it was allocated has gone out of scope (I guess, since
this is another call). AFAICS, the way I do it is by the book. What am I
doing wrong?

Regards,

Thomas Hallgren

"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:8715.1075138866@sss.pgh.pa.us...
> "Thomas Hallgren" <thhal@mailblocks.com> writes:
> > I'm trying to use a function that returns a complex type. I have no
problem
> > creating the function but when I try to use it I get the message:
>
> > ERROR:  function in FROM has unsupported return type
>
> AFAICS it's not possible to get that message for a function returning a
> composite type.  You'd better show exactly what you did.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

"Thomas Hallgren" <thhal@mailblocks.com> writes:
> ... exactly the same TupleTableSlot* that is passed into my
> printMyComplextType function. This is of course extremely bad since the
> MemoryContext where it was allocated has gone out of scope (I guess, since
> this is another call).

I don't think so; unless you are hacking memory contexts internally to
your function.  Here's some empirical proof that the function call
mechanism is not broken:

regression=# create type mytype as (f1 int ,f2 int);
CREATE TYPE
regression=# create function obtaintype(int,int) returns mytype as
regression-# 'select $1,$2' language sql;
CREATE FUNCTION
regression=# select * from obtaintype(1,2);f1 | f2
----+---- 1 |  2
(1 row)

regression=# create function usetype(mytype) returns int as
regression-# 'select $1.f1 + $1.f2' language sql;
CREATE FUNCTION
regression=# select usetype(obtaintype(1,2));usetype
---------      3
(1 row)
        regards, tom lane

I found the following piece of code in the plpgsql pl_comp.c module:
/* * This is a bit ugly --- need a permanent copy of the rel's tupdesc. * Someday all these mallocs should go away in
favorof a per-function * memory context ... */oldcxt = MemoryContextSwitchTo(TopMemoryContext);row->rowtupdesc =
CreateTupleDescCopy(RelationGetDescr(rel));MemoryContextSwitchTo(oldcxt);

My guess is that the SQL functions do something similar (pre-compile and
store all TupleDesc's in TopMemoryContext), hence there's no problem with
your example. Writing a C function I get a TupleDesc from the
TypeGetTupleDesc() function that has been allocated using palloc(). I'm
quite sure I don't do anything with memory contexts and the TupleDesc really
seems to go out of scope when the function returns.

If I create a copy of the TupleDesc in the TopMemoryContext directly after
the call to TypeGetTupleDesc, then everything works just fine.

I still suspect that something is broken with the calling mechanism. At
least for languages that do not compile the stuff into permanent structures
prior to evaluating.

Regards,

Thomas Hallgren

"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:14875.1075149255@sss.pgh.pa.us...
> "Thomas Hallgren" <thhal@mailblocks.com> writes:
> > ... exactly the same TupleTableSlot* that is passed into my
> > printMyComplextType function. This is of course extremely bad since the
> > MemoryContext where it was allocated has gone out of scope (I guess,
since
> > this is another call).
>
> I don't think so; unless you are hacking memory contexts internally to
> your function.  Here's some empirical proof that the function call
> mechanism is not broken:
>
> regression=# create type mytype as (f1 int ,f2 int);
> CREATE TYPE
> regression=# create function obtaintype(int,int) returns mytype as
> regression-# 'select $1,$2' language sql;
> CREATE FUNCTION
> regression=# select * from obtaintype(1,2);
>  f1 | f2
> ----+----
>   1 |  2
> (1 row)
>
> regression=# create function usetype(mytype) returns int as
> regression-# 'select $1.f1 + $1.f2' language sql;
> CREATE FUNCTION
> regression=# select usetype(obtaintype(1,2));
>  usetype
> ---------
>        3
> (1 row)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>