Thread: Opaque not working as documented

Hi, I'm having an odd problem with CREATE FUNCTION. Here are my statements:

CREATE FUNCTION ENCODE_BURY_TYPE (OPAQUE) RETURNS INT
    AS 'SELECT CASE
        WHEN $1 = \'EXACT\' THEN 1
        WHEN $1 = \'APPROX\' THEN 2
        WHEN $1 = \'PSUEDO\' THEN 3
        ELSE NULL
        END;'
    LANGUAGE 'SQL';

CREATE FUNCTION DECODE_BURY_TYPE (INT) RETURNS OPAQUE
    AS 'SELECT CASE
        WHEN $1 = 1 THEN \'EXACT\'
        WHEN $1 = 2 THEN \'APPROX\'
        WHEN $1 = 3 THEN \'PSUEDO\'
        ELSE \'NONE\'
        END;'
    LANGUAGE 'SQL';

CREATE TYPE BURY_TYPE (
    INPUT = ENCODE_BURY_TYPE,
    OUTPUT = DECODE_BURY_TYPE,
    INTERNALLENGTH = 16
);

This results in the error messages

ERROR: SQL functions cannot have arguments of type "opaque"
ERROR: SQL functions cannot return type "opaque"

Changing opaque to text results in the CREATE FUNCTION statements
working, while the CREATE TYPE statements throw a fit.

I suspect a configuration problem, but I have no idea where to look and
searches on google and the list archives haven't turned up anything. I'm
running the redhat rpms for Postgresql 7.2.1 on redhat 7.3.

Thank you,

Steve

Steven Blundy <sblundy@CLEMSON.EDU> writes:
> Changing opaque to text results in the CREATE FUNCTION statements
> working, while the CREATE TYPE statements throw a fit.

Yup.  You cannot use SQL functions to write I/O operations for a new
datatype, because the I/O converters run at a lower logical level than
SQL.  The only way to code them is in C.

            regards, tom lane