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.
Part I: General PostgreSQL Use
Introduction to PostgreSQL and SQL
Working with Data in PostgreSQL
PostgreSQL SQL Syntax and Use
Performance
Part II: Programming with PostgreSQL
Introduction to PostgreSQL Programming
Extending PostgreSQL
PL/pgSQL
The PostgreSQL C APIlibpq
A Simpler C APIlibpgeasy
The New PostgreSQL C++ APIlibpqxx
Embedding SQL Commands in C Programsecpg
Using PostgreSQL from an ODBC Client Application
Using PostgreSQL from a Java Client Application
Using PostgreSQL with Perl
Using PostgreSQL with PHP
Using PostgreSQL with Tcl and Tcl/Tk
Using PostgreSQL with Python
Npgsql: The .NET Data Provider
Other Useful Programming Tools
Part III: PostgreSQL Administration
Introduction to PostgreSQL Administration
PostgreSQL Administration
Internationalization and Localization
Security
Replicating PostgreSQL Data with Slony
Contributed Modules
Index