3.10. Data Types

In the object hierarchy under every schema are Data Types. You can create, comment on, and drop Data Types.

Data types can be created in two ways: The first allows you to create a type from scratch by defining the I/O functions and other parameters. The second relies on types that already exist in the database. The second method creates what are called Composite Data Types. Refer to the PostgreSQL 7.3 Reference Manual for more information.

In this section you will create two data types using the two different CREATE DATA TYPE dialogs.

The first data type will be called widget. Before proceeding, you must first create the input and output functions for this data type. To do this, launch a psql shell to the database (right-click on the database in which you wish to create the type and select LAUNCH SHELL (psql)). This opens a connection to the database. In the shell, run:

CREATE FUNCTION widget_in(cstring)
   RETURNS widget
   AS '/usr/lib/pgsql/test/regress/regress.so'
   LANGUAGE 'c';

CREATE FUNCTION widget_out(widget)
   RETURNS cstring
   AS '/usr/lib/pgsql/test/regress/regress.so'
   LANGUAGE 'c';

	Note
	The above works only if you have all the libraries installed in the default locations. If not, you have to change the location of the function handler to point to the regress.so file.

1. A data type is an object under a schema, so to create a data type, first expand the database under which you wish to create the data type (click the [O-] beside the database name), then expand the public schema.

2. Right-click on Data Types and select CREATE DATA TYPE.

3. The first field is the name of the data type. Type: widget.

4. Next, you must define the Input Function and Output Function. The Input Function is the function that converts the external representation of the user-defined data type into an internal representation for PostgreSQL's use. The Output Function describes the function that converts data from the internal representation to an external form that is analogous to the user-defined type.

   For this example, use widget_in(pg_catalog.cstring) as the input function, and widget_out(public.widget) as the output function.

5. Specify the Internal Length of the new data type. Variable allows the length to vary depending on the data being stored. If a value is entered in the Set To field, then that many bytes are used to store the data type, regardless of how much space it would have otherwise taken.

   For this example, set the Internal Length to 4.

6. The Default field specifies the default value for the data type. The value entered in this field is used in instances that make use of this data type but where the value for the type was not specified. For example, if a column was created with this data type and an entry was made into the table containing that column with the value for the column left blank, then the value would automatically be set to this "default value" of the data type. (Note that if you have specified the default value for that column, that takes precedence and the default value for the data type is ignored.)

   For this example, leave the default field blank.

7. The Element field is used when the type being created is to be an array of another type; this field holds that other type.

   This example does not use an element.

   	Note
   	When a data type is created, PostgreSQL automatically creates another data type that is an array of the newly created type. (So when this data type is created, the backend automatically creates a type called _widget, which is an array of type widget.)

8. Next is the Delimiter field. When creating an array of a user-defined type, a delimiter can be specified so that the elements can be distinguished. If a value is entered in the Delimiter field of the Create dialog, that value must be used as the delimiter when specifying arrays of the user type. If no delimiter is specified, comma (,) is used as the delimiter.

   For this example there is no delimiter.

9. The Send Function and Receive Function are not implemented in backends below PostgreSQL 7.4. On PostgreSQL 7.4 and higher backends, the Send and Receive functions are used to specify machine-independent binary representations.

10. The Pass By Value check box signifies how the data type is passed around. If checked, the data type's "value" is passed around. If left unchecked, a reference to the data type is passed around. Pass By Value may be specified only when the internal length of the data type is less than the width of the datum on the machine running the database server.

    For this example, leave Pass By Value unchecked.

11. Alignment specifies the storage alignment required for the data type. If the data type has a variable internal length, the Alignment must be at least 4 bytes because the Variable length uses at least 4 bytes.

    For this example, specify the alignment; as the internal length has been set to 4, set the Alignment to Double.

12. Storage specifies the storage strategy for the data type. If the data type is of fixed-length, only Plain Storage can be used. If not specified, Plain is used as the default by the backend.

    Since you have specified internal length for this example, leave storage set to Plain.

    

    Figure 3-36. The CREATE TYPE Dialog

13. Click OK to create the data type.

Note that the function widget has been added under the Functions node in the Tree View.

For the second data (composite) type, you will create one called widget_int4_combination.

1. A data type is an object under a schema, so to create a data type, first expand the database under which you wish to create the data type (click the [O-] beside the database name), then expand the public schema.

2. Right-click on Data Types and select CREATE DATA TYPE AS.

3. The first field is the name of the data type. Type: widget_int4_combination

4. Next, you must define the column names and types for this composite type. The column names and data types can be added one by one by defining a column name, its data type, and clicking Add Column.

   For this data type, use two columns. For the first one:

   1. For the Column Name, type: widget_col

   2. For the Data Type, select widget.

   3. Click Add Column.

   For the second one:

   1. For the Column Name, type: int4_col

   2. For the Data Type, select int4.

   3. Click Add Column.

5. Click OK to create the data type.

	Note
	Currently Administrator does not show composite types under data types. This will be implemented in the next version. However, you can confirm that the type was created by connecting to the database using psql and executing: select count(*) from pg_type where typname='widget_int4_combination'; This returns 1 if the type was created successfully, 0 otherwise.

Administrator also enables you to perform the following operations on existing data types:

To learn more about data types, refer to the PostgreSQL 7.3 Reference Manual.