Defining a Simple Data Type in PostgreSQL

Now that you understand the difference between internal and external forms, it should be obvious that PostgreSQL needs to convert values between these forms. When you define a new data type, you tell PostgreSQL how to convert a value from external form to internal form and from internal form to external form.

Let's create a simple type that mimics the built-in TEXT data type. Data type descriptions are stored in the pg_type system table. We are interested in three of the columns :

 movies=# SELECT typinput, typoutput, typlen movies-#   FROM pg_type movies-#   WHERE typname = 'text';  typinput  typoutput  typlen ----------+-----------+--------  textin    textout        -1 

The typinput column tells you the name of the function that PostgreSQL uses to convert a TEXT value from external form to internal form; in this case, the function is named textin . The typoutput column contains the name of the function ( textout ) that PostgreSQL uses to convert from internal to external form. Finally, typlen specifies how much space is required to hold the internal form of a TEXT value. TEXT values are of variable length, so the space required to hold the internal form is also variable ( “1 in this column means variable length ). If TEXT were a fixed-length type, the typlen column would contain the number of bytes required to hold the internal form.

Now you have enough information to create a new data type. Here is the command that you'll use to create a type named mytexttype:

 movies=# CREATE TYPE mytexttype movies-# ( movies-#   INPUT=textin, movies-#   OUTPUT=textout, movies-#   INTERNALLENGTH=VARIABLE movies-# ); 

The INPUT=textin clause tells PostgreSQL which function to call when it needs to convert a mytexttype value from external to internal form. The OUTPUT=textout clause tells PostgreSQL which function converts a mytexttype value from internal to external form. The final clause, INTERNALLENGTH=VARIABLE , tells PostgreSQL how much space is required to hold the internal form of a mytexttype value; you specify VARIABLE here to tell PostgreSQL that you are not defining a fixed length data type.

You have essentially cloned the TEXT ^[3] data type. Because you are using the same input and output functions as the TEXT type, the internal and external form of a mytexttype value is identical to the internal and external form of a TEXT value.

^[3] You have created an extremely limited clone. At this point, you can enter and display mytexttype values, but you can't do anything else with them. You have not defined any operators that can manipulate mytexttype values.

After you execute this CREATE TYPE command, you can use the mytexttype data type to create new columns:

 movies=# CREATE TABLE myTestTable movies-# ( movies(#    pkey  INTEGER, movies(#    value  mytexttype movies(# ); CREATE 

You can also enter mytexttype values. Because you borrowed the textin and textout functions, you have to enter values according to the rules for a TEXT value:

 movies=# INSERT INTO myTestTable movies-#   VALUES ( 1, 'This is a mytexttype value in external form' ); 

Now, let's define a new data type from scratch.