469,090 Members | 1,196 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

User Defined Types and SQL2003

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';
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #1
0 765

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by arabub | last post: by
2 posts views Thread by Paolo Nunberg | last post: by
2 posts views Thread by Les Stockton | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.