PostgreSQL Extensions - Design and Use of Relational Databases in Chemistry - page 109

chapter 10
PostgreSQL Extensions
10.1 Introduction
The basic capabilities of an RDBMS are accessible using the structured
query language (SQL) language. These capabilities include the basic data
types, such and numeric , text , date , etc. and basic functions and opera-
tors, such as length , sqrt , = and like . It is possible to extend the capa-
bilities 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 relational database man-
agement systems (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 dif-
ferent 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 lan-
guages available to extend its functionality.
Chapter 3 showed how SQL could be used to write a function to con-
vert 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 eas-
ily with standard SQL syntax.
10.2 Composite Data Types
A composite type is defined in terms of existing data types. For example,
the following SQL defines a new data type for concentration values.
Create Type conc As (val float, unit text);
This definition is similar to how a table is defined with its columns having
names and data types. It might be possible to collect all concentration val-
ues into one table, but concentration values are very common and used in
109