User Defined Types and SQL2003 - Mailing list pgsql-general

Below is an example of how a UDT can be created and used for a specific
language according to the SQL 2003 spec. The syntax is fetched from
"Routines and Types Using Java(SQL/JRT)". Aside from the "LANGUAGE JAVA"
this is not Java specific at all.

The PL/Java project would of course be interested in if Postgres could
support this syntax and willing to participate in an effort to implement
it too of course. What has been done already? What are the plans for SQL
2003 compliance with respect to UDT's?

Regards,

Thomas Hallgren



CREATE TYPE addr EXTERNAL NAME 'address_classes_jar:Address'
     LANGUAGE JAVA
     AS (
        street_attr CHARACTER VARYING(50) EXTERNAL NAME 'street',
        zip_attr CHARACTER(10) EXTERNAL NAME 'zip'
     )
     CONSTRUCTOR METHOD addr ()
        RETURNS addr SELF AS RESULT
        EXTERNAL NAME 'Address',
     CONSTRUCTOR METHOD addr (s_parm VARCHAR(50), z_parm CHAR(10))
        RETURNS addr SELF AS RESULT
        EXTERNAL NAME 'Address',
     METHOD to_string ()
        RETURNS CHARACTER VARYING(255)
        EXTERNAL NAME 'toString',
     METHOD remove_leading_blanks ()
        RETURNS addr SELF AS RESULT
        EXTERNAL NAME 'removeLeadingBlanks',
     STATIC METHOD contiguous (A1 addr, A2 addr)
        RETURNS CHARACTER(3)
        EXTERNAL NAME 'contiguous';

Given a table created as:

CREATE TABLE emps (
    name VARCHAR(30),
    home_addr addr
)

it would then be possible to write:

INSERT INTO emps VALUES (
    'Bob Smith',
    NEW addr('432 Elm Street', '95123'))

and selects like:

SELECT home_addr.to_string() FROM emps WHERE name = 'Bob Smith';