Thread: bug or my ignorance ?
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 ?
--
Holger Krug
hkrug@rationalizer.com
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 ?
On Tue, Dec 11, 2001 at 09:26:31AM -0500, Robert B. Easter wrote: > 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). Thank you for your answer. I learnt a lot from it. But in one main point it seems as if you're wrong. Look: =>CREATE FUNCTION testfun() RETURNS T AS ' '>SELECT 13::int8 as id, ''your name''::text as name;' LANGUAGE sql; This works, hence I can do something with a number of type ::T : =>select name( testfun() ); But this does not work, hence I cannot do anything with a number of type ::T : =>select name(r.a) from (select testfun() as a) as r; ERROR: fmgr_info: function 137857576: cache lookup failed What's the difference between those both cases ? -- Holger Krug hkrug@rationalizer.com
Holger Krug <hkrug@rationalizer.com> writes:
> =>select name(r.a) from (select testfun() as a) as r;
> ERROR: fmgr_info: function 137857576: cache lookup failed
Works okay if you just do
regression=# select name(testfun());
name
-----------
your name
(1 row)
In the other case it's getting confused by provisions left over from the
ancient PostQUEL "set attribute" feature. AFAICT that feature is
completely broken nowadays. I've been meaning to rip it out and try to
set up a somewhat-sensible implementation of functions returning tuples.
regards, tom lane
On Tue, Dec 11, 2001 at 10:25:43AM -0500, Tom Lane wrote: > Holger Krug <hkrug@rationalizer.com> writes: > > =>select name(r.a) from (select testfun() as a) as r; > > ERROR: fmgr_info: function 137857576: cache lookup failed > > Works okay if you just do > > regression=# select name(testfun()); > name > ----------- > your name > (1 row) Thank you. Unfortunately this does not work, if I have to return several attributes of T to the client. Is there any other clean way to solve this problem: "Having a function returning a tuple return the whole tuple to the client." Not so clean ways would be: * to work with an intermediate representation of the tuple as a string (as Joe Conway did in a former version of dblink) or * to put the function result into a temporary table which is created for this purpose. Which way is the recommended one ? > I've been meaning to rip it out and try to > set up a somewhat-sensible implementation of functions returning tuples. Would be fine. Thank you for your help ! -- Holger Krug hkrug@rationalizer.com
"Robert B. Easter" <reaster@comptechnews.com> writes:
> Table types were intended to be used like the following example
Not really. What seems to have been implemented back in the PostQUEL
days was "set attributes", by which they meant function attributes:
what was stored in such a field was the OID of a *function*. Retrieval
would run the function, producing one or more tuples from which an
individual field could be selected.
The example you give could have been implemented by defining a function
that executes "select * from t1 where key = something" and then storing
its OID in a row of t2.
This approach had a number of problems, the main one being the confusion
between fields-that-are-function-references and values-that-are-tuples.
There wasn't any distinction made AFAICT, and thus any situation where
a tuple datatype appears as a field of a larger tuple is liable to be
misinterpreted as a function reference instead.
The whole thing strikes me as an academic toy rather than a seriously
usable feature. (Are you really going to want to define a new function
for every row of your table? How should the system know when to get rid
of such functions? Not to mention the horrid memory leaks all over the
place in the implementation.) I'd like to get rid of it and put in a
clean implementation of tuple-valued functions and fields instead.
regards, tom lane