SQL Functions on Composite Types - what's the purpose??

ORIGINALLY: pgsql-sql(at)postgresql(dot)org, Tue 21 Sep 1999 16:48:57 +0200

ADDENDUM: The problem seems to remain in PostgreSQL 6.5.2. I have found
related comments here at pgsql-hackers since about a year ago; is there any
general advice??

[Chapter 39, Programmer's Guide / PostgreSQL 6.4]
WHAT DO I MAKE WRONG??

Did I try to abuse PostgreSQL dread or was there just a mistake??
How do you realize nested relations if not by OIDs??
Did I misunderstand the purpose of SQL functions on composite types??

Who has an answer??

You offer SQL functions that return tuples as composite types, but once
I try to use it by another SQL function with the same class as
parameter, I receive the error message:

Function '...' has bad return type 18826

/* Example:

CREATE TABLE class (attribute text);

CREATE FUNCTION tuple() RETURNS class
AS 'SELECT \'content\'::text AS attribute;'
LANGUAGE 'sql';

CREATE FUNCTION get_attribute(class) RETURNS text
AS 'SELECT $1.attribute AS result;'
LANGUAGE 'sql';

SELECT get_attribute(tuple());

*/

If the composite type producer is put into an attribute, whilst trying
to read from a table containing the composite attribute I will receive:

Functions on sets are not yet supported

/* Example:

CREATE TABLE container (composite_attribute class);

INSERT INTO container VALUES (tuple());

SELECT get_attribute(composite_attribute) FROM container;

*/

It seems that SQL functions with composite parameters do only work on
'raw' tuples of a relational table, don't they??

On the other hand, the attribute(class) function works quite well on
composite returns of SQL functions*.
But the interesting thing, reading from composite attributes, fails with
message:

init_fcache: cache lookup failed for procedure 136280528

/* Example:

SELECT attribute(composite_attribute) FROM container;

*/

* There are seemingly some fatal exceptions, which lead to:

pqReadData() -- backend closed the channel unexpectedly
This probably means the backend terminated
abnormally before or while processing the request.
We have lost the connection to the backend, so
further processing is impossible. Terminated.
nick(at)weg:~> ~( >;->)

/* Example:

CREATE FUNCTION get_class(class) RETURNS class
AS 'SELECT $1.attribute AS attribute;'
LANGUAGE 'sql';

SELECT attribute(get_class(composite_attribute)) FROM container;

*/

@your service(at)your service(at)your service(at)your service(at)your service(at)your service@
Jörg R. Rudnick
Software Developer
EMail: Joerg(dot)Rudnick(at)uni-essen(dot)de