User-defined PL/SQL subtypes v16
EDB Postgres Advanced Server supports user-defined PL/SQL subtypes and subtype aliases.
About subtypes
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 additional 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:
SUBTYPE <subtype_name> IS <type_name>[(<constraint>)] [NOT NULL]
Where constraint is:
{<precision> [, <scale>]} | <length>subtype_name
subtype_name specifies the name of the subtype.
type_name
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 types supported by EDB Postgres Advanced Server.
- The name of any composite type.
- A column anchored by a
%TYPEoperator. - The name of another subtype.
Include the constraint clause to define restrictions for types that support precision or scale.
precision
precision specifies the total number of digits permitted in a value of the subtype.
scale
scale specifies the number of fractional digits permitted in a value of the subtype.
length
length specifies the total length permitted in a value of CHARACTER, VARCHAR, or TEXT base types.
Include the NOT NULL clause to specify that you can't store NULL values in a column of the specified subtype.
A subtype that is based on a column inherits the column size constraints, but the subtype doesn't inherit NOT NULL or CHECK constraints.
Unconstrained subtypes
To create an unconstrained subtype, use the SUBTYPE command to specify the new subtype name and the name of the type on which the subtype is based. For example, the following command 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) that's a subtype of another subtype:
SUBTYPE cust_address IS address NOT NULL;
This command creates a subtype named cust_address that shares all of the attributes of the address subtype. Include the NOT NULL clause to specify that a value of the cust_address can't be NULL.
Constrained subtypes
Include a length value when creating a subtype that's based on a character type to define the maximum length of the subtype. For example:
SUBTYPE acct_name IS VARCHAR (15);
This example creates a subtype named acct_name that's based on a VARCHAR data type but is limited to 15 characters.
Include values for precision to specify the maximum number of digits in a value of the subtype. Optionally, include values for scale to specify the number of digits to the right of the decimal point when constraining a numeric base type. For 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 can't exceed three digits to the left of the decimal point and two 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 in the body of the function or procedure.
If a formal argument is declared as a constrained subtype, EDB Postgres Advanced Server:
- Enforces subtype constraints when assigning an actual argument to a formal argument when invoking a procedure.
- Doesn't enforce subtype constraints when assigning an actual argument to a formal argument when invoking a function.
Using the %TYPE operator
You can use %TYPE notation to declare a subtype anchored to a column. For example:
SUBTYPE emp_type IS emp.empno%TYPE
This command creates a subtype named emp_type whose base type matches the type of the empno column in the emp table. A subtype that's based on a column shares the column size constraints. NOT NULL and CHECK constraints aren't inherited.
Subtype conversion
Unconstrained subtypes are aliases for the type on which they are based. Any variable of type subtype (unconstrained) is interchangeable with a variable of the base type without conversion, and vice versa.
You can interchange a variable of a constrained subtype with a variable of the base type without conversion. However, you can interchange a variable of the base type with a constrained subtype only if it complies with the constraints of the subtype. You can implicitly convert a variable of a constrained subtype to another subtype if it's based on the same subtype and the constraint values are within the values of the subtype to which it is being converted.