Creating Models › Target Servers for Physical Models › Database Property Editors › PostgreSQL Physical Property Editors › PostgreSQL Support Summary › Define PostgreSQL User-Defined Types

Define PostgreSQL User-Defined Types

PostgreSQL can be extended to support new data types. You can define new base types, which are data types defined below the level of the SQL language.

Use the PostgreSQL User Defined Type Editor to define a user-defined type in PostgreSQL physical model.

To define a PostgreSQL user-defined type

1. In the Model Explorer, right-click User Defined Types and click New.

   An instance of User Defined Type is created.

2. Right-click the instance and click Properties.

   The PostgreSQL User Defined Type Editor opens.

3. In the navigation grid, select the user-defined type that you want to define and work with the following options:

   Note: Click New on the toolbar to create a user-defined type. Use the Enter filter text box to filter a very large list of user-defined types to quickly locate the one that you want to define.

   Name

   Specifies the name of the user-defined type. Change the name of the user-defined type in this field.

   Generate

   Generates SQL during forward engineering. Clear the check box if you do not want to generate SQL.

4. On the PostgreSQL tab, work with the following options:

   UDT Type

   Specifies the user-defined type. Select Select the type from the drop-down list.

   Schema

   Specifies the schema of the database to which the user-defined type belongs. Select the schema from the drop-down list or click New to create one.

   Owner

   Specifies the role or user that owns the user-defined type. Select the owner from the drop-down list or click New to create one.

   Enumerated Values

   Specifies value for the Enumerated Type in the form of a static, ordered set of values.

   Subtype

   Specifies the name of the element type that the range type will represent ranges of.

   Subtype Diff Function

   Specifies the name of a difference function for the subtype.

   Canonical Function

   Specifies the canonicalization function for the range type.

   Collation

   Specifies the desired collation in cases where one wants to use a non-default collation in the range's ordering. Select a collation from the drop-down list or click New to create one.

   Input Function

   Specifies the name of a function that converts data from the type's external textual form to its internal form.

   Output Function

   Specifies the name of a function that converts data from the type's internal form to its external textual form.

   Receive Function

   Specifies the name of a function that converts data from the type's external binary form to its internal form. Select a function from the drop-down list or click New to create one.

   Send Function

   Specifies the name of a function that converts data from the type's internal form to its external binary form. Select a function from the drop-down list or click New to create one.

   Modifier Input Function

   Specifies the name of a function that converts an array of modifier(s) for the type into internal form. Select a function from the drop-down list or click New to create one.

   Modifier Output Function

   Specifies the name of a function that converts the internal form of the type's modifier(s) to external textual form. Select a function from the drop-down list or click New to create one.

   Analyze Function

   Specifies the name of a function that performs statistical analysis for the data type. Select a function from the drop-down list or click New to create one.

   Internal Length

   Specifies a numeric constant that indicates the length in bytes of the new type's internal representation. The default assumption is that it is variable-length.

   Passed By Value

   Indicates that values of the data type are passed by value, rather than by reference.

   Alignment

   Specifies the storage alignment requirement of the data type. If specified, it must be char, int2, int4, or double; the default is int4. Select a storage alignment from the drop-down list.

   Storage Strategy

   Specifies the storage strategy for the data type. If specified, must be plain, external, extended, or main; the default is plain. Select a storage strategy from the drop-down list.

   Like Type

   Specifies the name of an existing data type that the new type will have the same representation as. Select a data type from the drop-down list or click New to create one.

   Category

   Specifies the category code (a single ASCII character) for the type. Select a category code from the drop-down list.

   Is Preferred

   Specifies whether the type is a preferred type within its type category. The default is false.

   Type Default

   Specifies the default value for the data type. If this is omitted, the default is null.

   Element

   Indicates that the type being created is an array; this specifies the type of the array elements. Select a user-defined type from the drop-down list or click New to create one.

   Delimiter

   Specifies the delimiter character to be used between values in arrays made of this type.

   Collatable

   Specifies whether the type's operations can use collation information. The default is false.

5. (Optional) Click the Comment tab and enter any comments that you want to associate with the object.
6. (Optional) Click the Where Used tab to view where the object is used within the model.
7. (Optional) Click the UDP tab to work with user-defined properties for the object.
8. (Optional) Click the Notes tab to view and edit user notes.
9. (Optional) Click the Extended Notes tab to view or edit user notes.
10. Click Close.

    The user-defined type is defined and the PostgreSQL User Defined Type Editor closes.

For more information, refer to PostgreSQL documentation.