PostgreSQL and Oracle Database extensibility

PostgreSQL Extensibility Explained

PostgreSQL is often touted as the "world's most advanced open-source relational database" due to its robust support for extensibility, allowing users to define new features that integrate seamlessly with the built-in system. This object-relational model is fundamental to its design.

Custom Data Types

PostgreSQL allows the creation of several kinds of user-defined types:

• Base Types (or Scalar Types): These are completely new, fundamental data types (like a complex number or a custom spatial object). Defining a new base type requires creating two essential functions, typically written in a low-level language like C:
  • Input Function: Converts the type's external textual representation (e.g., a string) into the internal, in-memory representation.
  • Output Function: Converts the internal representation back into an external textual string for display.
  • Once the base type is defined, PostgreSQL automatically supports arrays of that type.
• Composite Types: Similar to a table's row structure, a composite type bundles a list of fields (attributes) with defined data types into a single type. They're useful for passing entire data structures as function arguments or for grouping related columns.
  • Example: CREATE TYPE address AS (street VARCHAR, city VARCHAR, zip_code INT);
• Enumerated Types (Enums): These consist of a static, ordered list of values. They are excellent for ensuring a column only contains a specific set of predefined values.
  • Example: CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
• Range Types: Represent a range of values of a certain data type (the subtype), such as a range of integers (int4range) or a custom date range. You can also define new range types over any existing or custom subtype.
• Domains: A domain is an alias for an existing data type with optional constraints (like NOT NULL or CHECK constraints). This centralizes constraint logic.
  • Example: CREATE DOMAIN us_zip_code AS text CHECK (VALUE ~ '^\d{5}(-\d{4})?$');

Custom Functions and Procedures

PostgreSQL supports defining user-defined functions and procedures using several languages, not just SQL:

• SQL (PL/pgSQL): The built-in procedural language, fully integrated with SQL.
• External Languages: Functions can be written in languages like C, Python (PL/Python), Perl (PL/Perl), R (PL/R), and Tcl (PL/Tcl), providing immense flexibility for complex logic, statistical analysis, or system-level operations.

Custom Operators

Operators are symbols (e.g., +, -, or custom symbols like ~>) that perform operations. A custom operator must be defined on top of an existing or user-defined function. This allows:

• Custom Semantics: Defining how a standard operator behaves for a new data type (e.g., what + means for a complex number).
• New Symbols: Creating entirely new operators and associating them with functions.
• Indexing: By defining an operator class for a custom type and its related operators (like comparison operators), you can create indices (e.g., GiST, GIN, BRIN) for efficient querying on that new data type.

Oracle Database Support for Extensibility

Oracle Database fully supports custom data types, operators, and functions. Like PostgreSQL, Oracle is an Object-Relational Database Management System (ORDBMS) and has extensive features for user-defined schema objects.

Custom Data Types (User-Defined Types)

Oracle primarily supports custom types through its object-relational features, known as User-Defined Types or Object Types:

• Object Types: These are analogous to classes in object-oriented programming. An object type has attributes (data elements) and methods (functions and procedures) that define the structure and behavior of the type.
  • Example: CREATE TYPE employee_t AS OBJECT (employee_id NUMBER, name VARCHAR2(100), MEMBER FUNCTION get_annual_salary RETURN NUMBER);
• Collection Types: These are types that store a collection of elements, similar to arrays or lists:
  • Nested Tables: A table that is a column within another table.
  • VARRAY (Variable-Size Array): A collection that stores a fixed maximum number of elements.
• Subtypes: Oracle also allows for defining subtypes that apply constraints to a base type, similar to PostgreSQL's Domains.

Custom Functions and Procedures

Oracle's primary language for writing procedural code and database functions is PL/SQL (Procedural Language/SQL).

• Stored Functions and Procedures: These are database objects written in PL/SQL. Functions return a single value and are typically used in SQL expressions, while procedures perform actions.
• External Procedures/Functions: Oracle can also integrate code written in other languages, such as C or Java, to execute outside the database kernel (often for performance or to access external resources). This is similar to PostgreSQL's C functions, but done through a different mechanism (e.g., the external procedure agent).

Custom Operators

Oracle allows the creation of user-defined operators using the CREATE OPERATOR statement.

• An Oracle user-defined operator is identified by a name and is bound to one or more functions that implement its behavior for specific data types (signatures).
• They are often used in conjunction with domain indexes (also known as indextypes) to support efficient searching and querying on non-standard data, such as spatial or text data. This is how Oracle implements advanced features like Oracle Text and Oracle Spatial.