PolarDB for PostgreSQL(Compatible with Oracle) support user-defined PL/SQL subtypes and subtype aliases. A subtype is a data type with an optional set of constraints that restrict the values that can be stored in a column of that type. The rules that apply to the type on which the subtype is based are still enforced, but you can use other constraints to place limits on the precision or scale of values stored in the type.

You can define a subtype in the declaration of a PL function, procedure, anonymous block, or package. The syntax is as follows:

SUBTYPE subtype_name IS type_name[(constraint)] [NOT NULL]

where constraint is:

{precision [, scale]} | length

where:

  • subtype_name: specifies the name of the subtype.
  • type_name: specifies the name of the original type on which the subtype is based.

    type_name can be:

    • The name of any of the type supported by PolarDB for PostgreSQL(Compatible with Oracle).
    • The name of a composite type.
    • A column anchored by a %TYPE operator.
    • The name of another subtype.

Include the constraint clause to define restrictions for types that support precision or scale.

  • precision: specifies the total number of digits permitted in a value of the subtype.
  • scale: specifies the number of fractional digits permitted in a value of the subtype.
  • length: specifies the total length permitted in a value of CHARACTER, VARCHAR, or TEXT base types.

Include the NOT NULL clause to specify that NULL values may not be stored in a column of the specified subtype.

Note that a subtype that is based on a column will inherit the column size constraints, but the subtype will not inherit NOT NULL or CHECK constraints.

Unconstrained subtypes

To create an unconstrained subtype, use the SUBTYPE statement to specify the new subtype name and the name of the type on which the subtype is based. For example, the following statement creates a subtype named address that has all of the attributes of the type, CHAR:

SUBTYPE address IS CHAR;

You can also create a subtype (constrained or unconstrained) of another subtype:

SUBTYPE cust_address IS address NOT NULL;

This statement creates a subtype named cust_address that shares all of the attributes of the address subtype. Include the NOT NULL clause to specify that the value of the cust_address may not be NULL.

Constrained subtypes

Include a length value when creating a subtype that is based on a character type to define the maximum length of the subtype. Example:

SUBTYPE acct_name IS VARCHAR (15);

This example creates a subtype named acct_name that is based on a VARCHAR data type, but is limited to 15 characters in length.

Include values for precision (to specify the maximum number of digits in a value of the subtype) and optionally, scale (to specify the number of digits to the right of the decimal point) when constraining a numeric base type. Example:

SUBTYPE acct_balance IS NUMBER (5, 2);

This example creates a subtype named acct_balance that shares all of the attributes of a NUMBER type, but that cannot exceed 3 digits to the left of the decimal point and 2 digits to the right of the decimal.

An argument declaration (in a function or procedure header) is a formal argument. The value passed to a function or procedure is an actual argument. When invoking a function or procedure, the caller provides 0 or more actual arguments. Each actual argument is assigned to a formal argument that holds the value within the body of the function or procedure.

If a formal argument is declared as a constrained subtype:

  • PolarDB for PostgreSQL(Compatible with Oracle) do not enforce subtype constraints when assigning an actual argument to a formal argument during the invoking of a function.
  • PolarDB for PostgreSQL(Compatible with Oracle) enforce subtype constraints when assigning an actual argument to a formal argument during the invoking of a procedure.

Use the %TYPE operator

You can use the %TYPE notation to declare a subtype anchored to a column. Example:

SUBTYPE emp_type IS emp.empno%TYPE

This statement creates a subtype named emp_type whose base type matches the type of the empno column in the emp table. A subtype that is based on a column will share the column size constraints, while NOT NULL, and CHECK constraints are not inherited.

Subtype conversion

Unconstrained subtypes are aliases for the type on which they are based. Any type variable of unconstrained subtype is interchangeable with a variable of the base type without conversion, and vice versa.

A variable of a constrained subtype may be interchanged with a variable of the base type without conversion, but a variable of the base type can only be interchanged with a constrained subtype if the variable of the base type complies with the constraints of the subtype. A variable of a constrained subtype can be implicitly converted to another subtype if it is based on the same subtype, and the constraint values are within the values of the subtype to which it is being converted.