This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

With uDTS, you'll need some way of assigning UDT values to predefined <data type> targets, or vice versa. Further, if you have two distinct UDTs say UDT1 and UDT2 then you'll also need some way to assign values based on UDT1 to UDT2 targets, or vice versa. This might all be complicated by the fact that subtypes contain their supertypes' Attributes, which should imply a degree of mutual assignability. In sum, you need the ability to "cast" a UDT to or from another data type. The solution is to create a user-defined cast for the chosen <data type>s, with the CREATE CAST statement. The required syntax for the CREATE CAST statement is as follows.

CREATE CAST (<source type> AS <target type>)
WITH <specific routine designator>

A UDT value is assignable to a UDT target only if the source value is a subtype of the target UDT. There can be only one user-defined cast for any given combination of source and target types. Either <source type> or <target type> must be either UDT or REF, but the other operand can be any <data type>. To execute CREATE CAST, you must be the owner of both the cast function identified by <specific routine designator>) and the <target type> (if it is a UDT).

The <specific routine designator> is usually a signature for example, "FUNCTION f (SMALLINT)" can be a <specific routine designator>. It is also possible to identify a routine using a specific name, which is a unique, possibly mangled, name that is usually assigned by the DBMS. The cast function identified by the <specific routine designator> must have the general form:

FUNCTION <name> (<source type>) RETURNS <target type>

AS ASSIGNMENT (which is not the default) means that the cast is "implicitly invocable" during assignment operations. That is, if x is UDT_1, and there is an implicitly-invocable cast, then this is a legal assignment:

  • x = 'F'

Otherwise, this is a legal assignment:

  • x = CAST ('F' AS UDT_1)

[Obscure Rule] An assignment might involve a choice between several possible "implicitly invocable" cast functions. The DBMS picks the one that fits these criteria:

  • It's an implicitly-invocable cast function i.e.: it was mentioned with AS ASSIGNABLE.
  • The cast function's result <data type> is the target's declared type.
  • The cast function has one parameter, and that parameter has a "declared type", and that declared type is in the "type precedence list" of the declared type of the source value. If there are two cast functions which meet all these requirements, the DBMS picks the one whose declared type is highest in the "type precedence list".

TIP: If you decide to make a user-defined cast for "a to b", be reciprocal: make a user-defined cast for "b to a" as well. Users expect all casts to be two-way.

TIP: For hierarchy's sake: if A1 is a supertype of A2, and B1 is a supertype of B2, then make casts from A1 to B1 and from A2 to B2 not A1 to B2 nor A2 to B1. That is, cast from super to super and from sub to sub.

Here is a user-defined cast for the BOOK_UDT type which we used in earlier examples. The UDT has three Attributes (title CHAR(40), buying_price DECIMAL(9,2), selling_price DECIMAL(9,2)). Since each Attribute's type is either CHAR or castable to CHAR, we'll make a function which simply concatenates each Attribute into one big character string. Here's how:

    DECLARE c CHAR(60);
    SET c = book_udt.title()
          CAST(book_udt.buying_price() AS char(20))
          CAST(book_udt.selling_price() AS char(20));
    RETURN (u);

Now that we have a function, we can make a cast for the function:

CREATE CAST (book_udt AS CHAR(60))
WITH FUNCTION f (book_udt);

Now that we have a cast, we can use BOOK_UDT in a CAST expression:

SELECT CAST(book_udt AS CHAR(60))
FROM   Books;

Thus, we can design our own external representation of a UDT, without worrying about its internal representation.

For distinct type UDTs only, the DBMS automatically creates two casts. For example, if you make this UDT:


the DBMS will make these two casts for it:


Now, suppose you have a variable "lo" of type LONGITUDE. Because of the automatic cast, it's legal to cast a <literal> to LONGITUDE, for example:

SET lo = CAST (33.59 AS longitude);

Not only that, though! The casts are AS ASSIGNMENT casts, so this SQL statement is also legal:

SET lo = 33.59;

The AS ASSIGNMENT feature, which got into the SQL Standard at a very late stage, is bound to confuse some people: they'll think that "distinct types" are just a way to rename predefined <data type>s. That would be a delusion. The reason that "SET lo = 33.59;" is legal is that there is an implicit cast (the DBMS makes it silently) which happens to be an implicitly invoked cast (it contains an AS ASSIGNMENT clause).

TIP: Implicitly invocable casts are convenient but error-prone. If you don't want to allow loosely-typed phrases like "money_column = 5.00", you should DROP the cast that the DBMS created and then explicitly make it again - - but without specifying "AS ASSIGNMENT" in the definition.


Comments loading...