The basic capabilities of a RDBMS are accessible using the SQL language. These capabilities include the basic data types, such and numeric, text, date, etc. and basic functions and operators, such as length, sqrt, = and like. It is possible to extend the capabilities of the database and of SQL by defining new data types and new functions. These integrate neatly into the syntax of SQL and allow the new data types and functions to be easily used in ways similar to the standard SQL data types and functions. The PostgreSQL RDBMS allows the use of various computer languages to create new functions, including a procedural language plpgsql native to PostgreSQL. The plpgsql language is analogous, but substantially different than the sqlplus language used in the Oracle RDBMS. Of course, it is possible to simply use SQL to define new data types and functions as well. In this chapter the focus is on PostgreSQL and the various languages available to extend its functionality.

Chapter 3 showed how SQL could be used to write a function to convert pressure data values expressed in atmospheres to kilopascals. Other functions were used in check constraints on a column containing CAS numbers. This chapter will show how new data types can be defined. This will require functions to define the method for input parsing and the method to output data values. There will also be functions to define operations on the new data types, enabling searches to be integrated easily with standard SQL syntax.