The Pyrrho Database Management System

Malcolm Crowe, University of the West of Scotland


Structured Types

SQL2003 supports structured types. Structured types, multisets and arrays can be stored in tables. There is a difference between (say) a table with certain columns, a multiset of rows with similarly named fields and a multiset of a structured type with similarly named attributes, even though in an element of each of these the value of a column, field or attribute respectively is referenced by syntax of the form a.b . Some constructs within SQL2003 overcome these differences: for example the INSERT statement uses a set of values of a compatible row type to insert data into a table, and TABLE v constructs a table out of a multiset v.

To use structured types, it is necessary to CREATE TYPE for the structured type: this indicates the attributes and methods that instances of the type will have. Then a table (for example) can be defined that has a column whose vales belong to this type. At this stage the table could even be populated since (there is an implicit constructor for any structured type); but before any methods can be invoked they need to be given bodies using the CREATE METHOD construct. Note that you cannot have a type with the same name as a table or a domain (since a type has features of both).

Values of a structured type can be created (using NEW), assigned to variables, used as parameters to suitably declared routines, used as the source of methods, and placed in suitably declared fields or columns.

Arrays and multisets of known types do not need explicit type declaration. Their use can be specified by the use of the keyword ARRAY or MULTISET following the type definition of a column or domain.

Other uses of User-Defined Types

User-defined types need not be structured. The following code defines a type for fixing the ordering of surnames, using the standard SQL2003 create ordering construct. (Recall that [ ] can be used to enclose multiline SQL statements when using the PyrrhoCmd tool.)

create type surname as char

create function fixname(a char) returns char
 if a like 'Mc%' then
  return 'Mac' || substring (a from 3)
 elseif a like 'O''%' then
  return 'O' || substring (a from 3)
 else return a
end if

create function nameorder(a surname,b surname) returns int
  declare ac,bc char;
   set ac = upper(fixname(a));
   set bc = upper(fixname(b));
  if ac<bc then return -1
  elseif ac>bc then return 1
  else return 0 end if

create ordering for surname order full by relative with function nameorder(surname,surname)

A URI can be associated with a type, by adding a with clause to the above type declaration. SQL2003 contains good support for subtypes: the TREAT function allows different records or cells to use different subtypes, and the IS OF predicate allows selection on subtyped values.