Extension

PostgreSQL can be extended to support new data types. PostgreSQL provides the CREATE EXTENSION command to load extensions to the current database. Also, PostgreSQL has a central distribution network (PGXN)—www.pgxn.org—which allows users to explore and download extensions. When installing the PostgreSQL binaries, the postgresql-contib package contains many useful extensions such as tablefunc, which allows table pivoting and pgcrypto extension; the README file in the installation directory contains the summary information.

The ability of PostgreSQL to support extensions is a result of the following features:

• PostgreSQL data types: PostgreSQL has very rich data types. It supports primitive data types as well as some primitive data structures, such as arrays, out of the box. In addition to that, it supports the following complex data types:
  • Geometric data types: Including point, line segment (lseg), path, polygon, and box
  • Network address types: Including cidr, inet, and macaddr
  • tsvector and tsquery: This is a sorted list of lexemes that enables postgres to perform full text search
  • Universal unique identifiers (UUID): UUID solves many problems related to databases, such as offline data generation
  • NoSQL: It supports several NoSQL data types including XML, Hstore, and JSONB.Enum, range and domain are user-defined data types with specific constraints, such as a set of allowed values, data range constraint, and check constraints
  • Composite data type is a user-defined data type, where an attribute is composed of several attributes

• Supported languages: PostgreSQL allows functions to be written in several languages. The PostgreSQL community supports the following languages: SQL, C, Python, PL/pgSQL, Perl, and Tcl. In addition to these, there are many externally maintained procedural languages, including Java, R, PHP, Ruby, and UNIX shell.

The following example shows you how to create a new composite type called phone_number. An equality operator is also created to check whether two phone numbers are equal by comparing the area code and the line number:

--This example shows how to create a composite data type.
CREATE TYPE phone_number AS (
    area_code varchar(3),
    line_number varchar(7)
);
CREATE OR REPLACE FUNCTION phone_number_equal (phone_
number,phone_number) RETURNS boolean AS $$
BEGIN
    IF $1.area_code=$2.area_code AND $1.line_number=$2.line_number THEN
        RETURN TRUE ;
    ELSE
        RETURN FALSE;
    END IF;
END; $$ LANGUAGE plpgsql;
CREATE OPERATOR = (
LEFTARG = phone_number,
RIGHTARG = phone_number,
PROCEDURE = phone_number_equal
);
--For test purpose
SELECT row('123','222244')::phone_number = row('1','222244')::phone_number;

The above examples show how one can create new data types. a Data type called phone_number is created. The phone_number datatype is composed of two atomic datatypes which are area_code and line_number. The operator = is overloaded to handle also the equality of the new datatype. To define the behavior of = operator , one need to define the operator arguments and the function that handles the arguments, and in this case the function is phone_number_equal and the arguments are of type phone_number.