Re: bug or my ignorance ? - Mailing list pgsql-general

When you use a table name type as a column of another table, that column can
hold a number like xxxx::mytable that is the same number as a row's oid in
mytable. xxxx::oid and xxxx::mytable are the same number but of different
data types. They both refer to a row in mytable. The database has some
support functions for handling type OID, but almost no support functions for
your own types like mytable. Attempts to use them in any functions fail like
you have seen.

Table types were intended to be used like the following example but this
functionality was either removed a long time ago or it never was fully
implemented:

create table t1 (n text, a int);
create table t2 (m text, b int, t1 o);

select m, b, o.n, o.a from t2;
-- returns stuff from both tables (this will not work)

select m, b, o from t1;
-- o returns a number, same as oid of a t1 row (this works now)
-- but the type of the number o is ::t1 and no functions
-- know what to do with it. You can select it, but thats about it.

Anyhow, you are returning a T in your functions, so all you will get out of
them is a number of type ::T which you will not be able to do anything with
except select it.  Trying to access the members of a T using dot won't work.
The number cannot even be cast to an oid or any other data type (the cast
functions don't even understand how to do anything with table types).

Bob


On Tuesday 11 December 2001 08:20 am, Holger Krug wrote:
>     The versions of PostgreSQL I used:
>     * CVS of Dec. 11, virgin installation without any further changes
>     * 7.1.3
>
> I have simple SQL functions `testfun()' and `testfun1()' returning values
> of the type of relation `T':
>
>   =>CREATE TABLE T ( id int8, name text );
>
>   =>insert into T VALUES ( 15, 'my name' );
>
>   =>CREATE FUNCTION testfun() RETURNS T AS '
>   '>SELECT 13::int8 as id, ''your name''::text as name;' LANGUAGE sql;
>
>   -- or: 'sql' for PostgreSQL 7.1.3
>
>   =>CREATE FUNCTION testfun1() RETURNS T AS '
>   '>SELECT  id,  name from T;' LANGUAGE sql;
>
>   -- or: 'sql' for PostgreSQL 7.1.3
>
> Now I try to call the first function and return the values to the user:
>
>   =>select name(r.a) from (select testfun() as a) as r;
>   ERROR:  fmgr_info: function 137857576: cache lookup failed
>
> Does not work. For the second function I first try a simpler call:
>
>   => select r.a from (select testfun1() as a) as r;
>        a
>   -----------
>    137835800
>   (1 Zeile)
>
> Works but does not help. Retrying it gives two alternating return values:
>
>   => select r.a from (select testfun1() as a) as r;
>        a
>   -----------
>    137857448
>   (1 Zeile)
>
>   => select r.a from (select testfun1() as a) as r;
>        a
>   -----------
>    137835800
>   (1 Zeile)
>
>   => select r.a from (select testfun1() as a) as r;
>        a
>   -----------
>    137857448
>   (1 Zeile)
>
> Now lets look if can return the result of the second function to the
> user:
>
>   =>select name(r.a) from (select testfun1() as a) as r;
>   ERROR:  fmgr_info: function 137835928: cache lookup failed
>   => select name(r.a) from (select testfun1() as a) as r;
>   ERROR:  fmgr_info: function 137857576: cache lookup failed
>   =>select name(r.a) from (select testfun1() as a) as r;
>   ERROR:  fmgr_info: function 137835928: cache lookup failed
>   => select name(r.a) from (select testfun1() as a) as r;
>   ERROR:  fmgr_info: function 137857576: cache lookup failed
>   =>select name(r.a) from (select testfun1() as a) as r;
>   ERROR:  fmgr_info: function 137835928: cache lookup failed
>   => select name(r.a) from (select testfun1() as a) as r;
>   ERROR:  fmgr_info: function 137857576: cache lookup failed
>   =>select name(r.a) from (select testfun1() as a) as r;
>   ERROR:  fmgr_info: function 137835928: cache lookup failed
>   => select name(r.a) from (select testfun1() as a) as r;
>   ERROR:  fmgr_info: function 137857576: cache lookup failed
>
> Unfortunately I cannot.
>
> Is this a sign of insufficient experience from my side or a real bug ?