SQL Standard User Defined Types and Routines

This document describes standard support for user-defined types and routines in SQL:2003. The specifications are informal; for details, see the relevant portions of the standard. Much but not all of this is already implemented in Farrago.

User-Defined Types

In SQL:2003, it's possible to extend the type system in a number of ways:

Typed Tables

SQL:2003 allows structured types to be used in two different modes: This document does not cover typed tables any further, nor does it cover locators and references; instead, the focus is on the semantics of structured types and their instances.


SQL behavior can be extended by defining various kinds of SQL-invoked routines. SQL-invoked routines may be implemented in the SQL standard stored procedure language, SQL/PSM (in which case they are called SQL routines), or in another language such as Java (in which case they are called external routines, defined in SQL/OLB, also known as SQLJ Part 1). The choice of implementation language is orthogonal to the contexts in which routines may be invoked, as listed here:

Routine Modifiers

A number of modifiers can be applied to routines when they are defined:

Routine Parameters

Most routine parameters are input-only. However, procedures allow parameters to be specified as one of { IN | OUT | INOUT }. Parameter names are optional (but still recommended) when an external routine is specified, since external routine lookup is by type signature. Parameters do not take default values.

Casting Around a Type Hierarchy

The equivalent of the Java instanceof keyword is the IS [NOT] OF syntax:

SELECT p.name
FROM ParkingLots p
WHERE p.geometry IS OF (Square,Circle);
Casting is sometimes required to get the right version of a method due to overloading or overriding. The normal SQL CAST keyword is not used for this purpose. Instead, the syntax for downcasting uses the TREAT keyword:

SELECT TREAT(p.geometry AS Square).side_length
FROM ParkingLots p
WHERE p.geometry IS OF (Square);
For upcasting, the TREAT keyword is omitted:

SELECT generate_label(d.dot AS Shape)
FROM PolkaDots p;
If the generate_label function were overloaded with different versions for Circles and for generic Shapes, then the query above will invoke the generic Shape version. For the gory details on routine resolution, consult the standard.

Besides explicit type hierarchy casting, SQL:2003 also provides for


After a type has been defined, it can be maintained with the ALTER TYPE statement, which allows for Types can be dropped, but only with RESTRICT semantics (no CASCADE).

Procedures, functions, and types may be defined in any schema, but methods must be defined in the same schema as the associated type.

SQL:2003 introduces the notion of a SQL-path, which is used to resolve unqualified references to procedures, functions, and types (similar to the way the PATH environment variable is used to resolve executable names). Each schema has a SQL-path associated with it; objects defined within that schema use that path implicitly. Sessions also have their own SQL-paths used for dynamic SQL.

As with table references in views, unqualified routine references are "frozen" at the time the invoking code is validated so that subsequent path changes do not affect them. However, the situation is a little more complicated in the case of late binding; instead of freezing on a single routine, the system freezes a candidate set with final matching performed at execution time. In the examples above, an invocation of shape.area() does not know until execution whether a particular shape is a Rectangle or a Circle.

External Routines

So far all of the examples have defined SQL routines (using SQL/PSM for the procedure implementation language). For Farrago, we are also interested in using Java as a routine implementation language. SQL/OLB provides this, including the capability to embed SQL in Java. However, it does NOT allow methods to be defined in Java for normal structured types, only procedures and functions. In order to define methods in Java, you have to go all the way and use Java structured types (SQL/JRT, not covered here).

Defining a routine with a Java implementation is a multi-step process:

  1. Write the routine and compile it as a Java class (possibly using a SQLJ precompiler to translate any embedded SQL statements).
    class MathFuncs {
        public static double sqr(double d)
            return d*d;
  2. Package the compiled Java class into a JAR file.
  3. From SQL, CALL the system-defined SQLJ.INSTALL_JAR routine to install the JAR into the DBMS. (The last parameter, which controls whether deployment descriptors are used, is currently ignored since these aren't supported yet.)
    CALL SQLJ.INSTALL_JAR('file:/home/jvs/MathFuncs.jar','MathFuncsJavaLib', 0);
  4. Issue a CREATE {FUNCTION|PROCEDURE} statement referencing the routine's Java implementation in the installed JAR.
    NO SQL
    EXTERNAL NAME 'MathFuncsLib:MathFuncs.sqr'
Unfortunately, there's no way to combine all of this into a single DDL statement with the routine body inline. We may want to consider an extension for this, e.g.

        return d*d;
The schema named SQLJ is a special schema maintained by the system (just like INFORMATION_SCHEMA). It contains other procedures for JAR maintenance (REPLACE_JAR, REMOVE_JAR, ALTER_JAVA_PATH). Besides Java classes, JAR files can also contain deployment descriptors such as the DDL for creating the SQL routines and granting access to them. In a way this is the inverse of the inline routine definition mentioned above: instead of defining everything in SQL, define everything in the JAR, and then issue a single CALL from SQL to deploy it.

Access Control

SQL:2003 types have no concept of access control such as the public/private/protected of Java and C++. However, the standard (and more powerful) GRANT/REVOKE mechanism can be used at the method level instead.

When external routines execute SQL statements, the authorization with which they run can be controlled via the EXTERNAL SECURITY clause on the routine definition:

SQL routines always run with INVOKER privileges.

User Defined Aggregates

SQL:2003 does not provide a mechanism for user-defined aggregates. See the Oracle, DB2, and PostgreSQL docs for various vendor-defined extensions in this area.