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

From: "Jrg R(dot) Rudnick" <jrudnick(at)swt(dot)wi-inf(dot)uni-essen(dot)de>
To: pgsql-sql(at)postgresql(dot)org, joerg(dot)rudnick(at)uni-essen(dot)de, unlandr(at)informatik(dot)uni-essen(dot)de
Subject: SQL Functions on Composite Types - what's the purpose??
Date: 1999-09-21 14:48:57
Message-ID: 37E79AD9.13E1D632@swt.wi-inf.uni-essen.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

[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;

*/

Browse pgsql-sql by date

  From Date Subject
Next Message Herouth Maoz 1999-09-21 14:57:56 Re: [SQL] Re: [BUGS] Running queries on inherited tables
Previous Message Tom Lane 1999-09-21 14:15:32 Re: [SQL] Subselect performance