Re: SQL Spec Compliance Questions - Mailing list pgsql-advocacy

Josh Berkus wrote:

> 6) SQL-99 Distinct Types
> 7) SQL-99 Structured Types

> 9) SQL-99 Collection Types

> 10) SQL-99 Typed tables and views

> My answers:
>
> 6), 7) Not sure what these are.
Here's the section in SQL99:

4.8 User-defined types
     A user-defined type is a schema object, identified by a
     <user-defined type name>. The definition of a user-defined type
     specifies a number of components, including in particular a list of
     attribute definitions. Although the attribute definitions are said
     to define the representation of the userdefined type, in fact they
     implicitly define certain functions (observers and mutators) that
     are part of the interface of the user-defined type; physical
     representations of user-defined type values are
     implementation-dependent.

     The representation of a user-defined type is expressed either as a
     single data type (some predefined data type, called the source
     type), in which case the user-defined type is said to be a distinct
     type, or as a list of attribute definitions, in which case it is
     said to be a structured type.

So if I read that correctly, they are user defined types, that are
either scalar (distinct) or composite (structured) -- so I'd say yes.

> 9) ???

 From SQL99:

4.11 Collection types
      A collection is a composite value comprising zero or more elements
      each a value of some data type DT. If the elements of some
      collection C are values of DT, then C is said to be a collection of
      DT. The number of elements in C is the cardinality of C. The term
      ‘‘element’’ is not further defined in this part of ISO/IEC 9075.
      The term ‘‘collection’’ is generic, encompassing various types (of
      collection) in connection with each of which, individually, this
      part of ISO/IEC 9075 defines primitive type constructors and
      operators. This part of ISO/IEC 9075 supports one collection type,
      arrays.

We are not yet fully compliant with SQL99 arrays, but not too far off
either, I think. We have some extensions to SQL99 behavior, that would
require breaking backward compatibility in order to do away with them.
For example, SQL99 arrays *always* start with a lower bound of 1, if I
read the spec correctly. Also multidimensional arrays in SQL99 are
"arrays of arrays", which is not quite the same as our multidimensional
arrays.

> 10) Also not sure

SQL99:

4.16.2 Referenceable tables, subtables, and supertables
        A table BT whose row type is derived from a structured type ST is
        called a typed table. Only a base table or a view can be a typed
        table. A typed table has columns corresponding, in name and
        declared type, to every attribute of ST and one other column REFC
        that is the self-referencing column of BT; let REFCN be the
        <column name> of REFC. The declared type of REFC is necessarily
        REF(ST) and the nullability characteristic of REFC is known not
        nullable. If BT is a base table, then the table constraint
        ‘‘UNIQUE(REFCN)’’ is implicit in the definition of BT. A typed
        table is called a referenceable table. A self-referencing column
        cannot be updated. Its value is determined during the insertion
        of a row into the referenceable table. The value of a
        system-generated selfreferencing column and a derived
        self-referencing column is automatically generated when the row
        is inserted into the referenceable table. The value of a
        user-generated self-referencing column is supplied as part of the
        candidate row to be inserted into the referenceable table.

I really don't quite understand this, but I don't think we have it ;-)

HTH,

Joe