Extensions

PostgreSQL can be extended to support new data types and functionality. Extensions in PostgreSQL are a great way to add new functions, not being a core developer. PostgreSQL provides the CREATE EXTENSION command to load extensions to the current database. Also, PostgreSQL has a central distribution network (PGXN: www.pgxn.org) that 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 the 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, 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 JSON.  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.

A 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, 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. The following 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 preceding examples show how one can create new data types. A data type called phone_number is created. The phone_number data type is composed of two atomic data types, area_code and line_number. The = operator is overloaded to handle the equality of the new data type. To define the behavior of the = operator, you need to define the operator arguments and the function that handles the arguments. In this case, the function is phone_number_equal and the arguments are of the phone_number type.