Refactor Type System

Requirements

Rich type modifier

This is a requirement from PostGIS for creaky typmod system:

One 32-bit integer does not contain enough structure to describe conveniently what a type should be. Pushing information into userland, as PostGIS is forced to do, exposes these types to the vicissitudes of user environment, search_path, etc.

Typmod is not preserved over all operations, leading to surprising behavior. Need to store a separate entry for a type's array and other possible multiplicities

Type with collation

In addition to typmod, collation attributes might be required to support type or expr with collation. See also Todo:Collate for details.

CREATE TABLE .. ( ..., field COLLATE <collation>, ... )
expr COLLATE <collation>

Anonymous MULTISET

The SQL standard provides anonymous multiset:

CREATE TABLE .. ( .... ROW(integer, varchar(32)) MULTISET )

Generics

A real subtyping system might also be nice to support some kind of generics, type generators, or type interfaces.

Note that the current pseudo types (anyelement, anyarray, etc.) can be used only for arguments or result types for functions. In addition, we can use only one pseudo type for each function.

References

Associative array

hstore in PostgreSQL is a text-to-text associative array, but other databases support typed assoc array data types.

Syntax by DB2:

CREATE TYPE char2int AS INTEGER ARRAY[VARCHAR(20)];

Syntax by Oracle:

TYPE char2int IS TABLE OF NUMBER(10) INDEX BY VARCHAR2(20);

Note that Oracle supports assoc array only as variables in PL/SQL.