Custom Types, Operators, Operator Classes, Aggregates, and Extension Architecture

Learning outcomes

PostgreSQL extensibility is deeper than adding functions. The parser resolves types and casts; operators bind syntax to functions and planner selectivity rules; aggregates expose state-transition algorithms; operator classes tell an index access method how a type's operators map to search strategies and support functions. Extensions package these cooperating objects so the database can install/update them as a unit.

1. Start with SQL-level type semantics: a domain

DROP DOMAIN IF EXISTS app.ch19_service_minutes CASCADE;CREATE DOMAIN app.ch19_service_minutes AS integerCHECK (VALUE >= 0 AND VALUE <= 1440);SELECT 90::app.ch19_service_minutes AS valid_minutes;SELECT (-5)::app.ch19_service_minutes;-- Expected: domain check violation

A domain reuses an existing storage type while adding constraints. A new base type is much more involved: PostgreSQL needs input/output functions and often binary I/O, comparison, casts, operators, and index semantics. Native base-type implementations commonly require C extension code.

2. Operators bind syntax to functions

Suppose ServiceHub treats two duration estimates as operationally equivalent when they differ by at most five minutes. The underlying semantics belong in a deterministic function; a custom operator adds concise syntax.

CREATE OR REPLACE FUNCTION app.ch19_within_five_minutes(integer, integer)RETURNS booleanLANGUAGE sqlIMMUTABLESTRICTPARALLEL SAFERETURN abs($1 - $2) <= 5;DROP OPERATOR IF EXISTS app.<~> (integer, integer);CREATE OPERATOR app.<~> (  LEFTARG = integer,  RIGHTARG = integer,  FUNCTION = app.ch19_within_five_minutes);SELECT  30 OPERATOR(app.<~>) 34 AS close_enough,  30 OPERATOR(app.<~>) 40 AS too_far;

A custom operator does not automatically become indexable or gain planner statistics. It is simply parser-visible syntax attached to a function plus optional planner metadata such as commutator/negator/selectivity estimators.

3. Operator naming and search_path are part of API design

SELECT n.nspname,       o.oprname,       o.oprleft::regtype AS left_type,       o.oprright::regtype AS right_type,       o.oprcode::regprocedure AS implementationFROM pg_operator AS oJOIN pg_namespace AS n ON n.oid = o.oprnamespaceWHERE n.nspname = 'app'  AND o.oprname = '<~>';

Schema-qualified operator syntax uses OPERATOR(schema.operator). That avoids accidental shadowing when multiple schemas define the same symbolic operator. Server-side security reviews should treat operators as executable code resolution, not mere punctuation.

4. Build a SQL-level custom aggregate

CREATE OR REPLACE FUNCTION app.ch19_add_minutes(bigint, integer)RETURNS bigintLANGUAGE sqlIMMUTABLEPARALLEL SAFEAS $$  SELECT COALESCE($1, 0) + COALESCE($2, 0)::bigint$$;DROP AGGREGATE IF EXISTS app.ch19_total_minutes(integer);CREATE AGGREGATE app.ch19_total_minutes(integer) (  SFUNC = app.ch19_add_minutes,  STYPE = bigint,  INITCOND = '0',  COMBINEFUNC = int8pl,  PARALLEL = SAFE);

SELECT app.ch19_total_minutes(v) AS custom_total,       sum(v)::bigint AS builtin_totalFROM (VALUES (10),(20),(NULL),(35)) AS t(v);

An ordinary aggregate maintains a state value and invokes its transition function per input row. A combine function lets partial states be merged, which is required for partial/parallel aggregation. The aggregate's own parallel label matters; PostgreSQL does not infer safety solely from support functions.

5. Inspect the aggregate's catalog contract

SELECT p.oid::regprocedure AS aggregate_signature,       p.proparallel,       a.aggtransfn::regprocedure AS transition_fn,       a.aggcombinefn::regprocedure AS combine_fn,       a.aggtranstype::regtype AS state_type,       a.agginitvalFROM pg_proc AS pJOIN pg_aggregate AS a ON a.aggfnoid = p.oidWHERE p.oid = 'app.ch19_total_minutes(integer)'::regprocedure;

The planner/executor uses these metadata contracts, not the name of the aggregate. Lying about parallel safety or using a non-associative combine strategy can produce wrong results—not merely slower plans.

6. Why operator classes are a different risk tier

An operator class tells an index access method which operators occupy which strategy numbers and which support functions implement ordering/search behavior for one data type. B-tree, GiST, GIN, SP-GiST, BRIN, and Hash each have different contracts.

SELECT ns.nspname AS opclass_schema,       opc.opcname AS opclass_name,       am.amname AS access_method,       opc.opcintype::regtype AS indexed_type,       opc.opcdefault,       opf.opfname AS familyFROM pg_opclass AS opcJOIN pg_namespace AS ns ON ns.oid = opc.opcnamespaceJOIN pg_am AS am ON am.oid = opc.opcmethodJOIN pg_opfamily AS opf ON opf.oid = opc.opcfamilyWHERE opc.opcintype IN ('integer'::regtype, 'text'::regtype)ORDER BY am.amname, opc.opcdefault DESC,         ns.nspname, opc.opcnameLIMIT 30;

PostgreSQL requires superuser to create an operator class because a wrong definition can confuse or even crash the server. It also does not fully verify semantic self-consistency for you. This is why Chapter 19 does not turn a training operator into a custom B-tree contract.

7. Controlled privilege failure for operator-class creation

DROP SCHEMA IF EXISTS ch19_opclass_sandbox CASCADE;CREATE SCHEMA ch19_opclass_sandbox AUTHORIZATION servicehub_owner;GRANT USAGE, CREATE ON SCHEMA ch19_opclass_sandbox TO servicehub_app;SET ROLE servicehub_app;CREATE OPERATOR CLASS ch19_opclass_sandbox.ch19_bad_opsFOR TYPE integer USING btree AS  OPERATOR 1 < (integer, integer);-- Expected: must be superuser to create an operator class.RESET ROLE;DROP SCHEMA ch19_opclass_sandbox CASCADE;

The safe repair is not “grant superuser.” Use built-in operator classes when possible. If a true new data/index semantics is required, implement it as a separately reviewed extension with regression tests, versioned upgrade scripts, target-major CI, and a controlled server-code/toolchain process.

8. Extension architecture: package cooperating objects

A production custom type frequently needs many of these objects together. That is precisely the problem the extension mechanism solves.

9. Native C extension boundary

C extensions use PostgreSQL server headers and a shared-library ABI. They can implement data types, hooks, background workers, access methods, planner/executor integration, and performance-critical functions, but they run in or alongside PostgreSQL server processes. Build output must target the exact supported PostgreSQL major/platform/toolchain contract defined by the extension author/distributor.

10. Cleanup and checkpoint

DROP AGGREGATE IF EXISTS app.ch19_total_minutes(integer);DROP FUNCTION IF EXISTS app.ch19_add_minutes(bigint,integer);DROP OPERATOR IF EXISTS app.<~> (integer,integer);DROP FUNCTION IF EXISTS app.ch19_within_five_minutes(integer,integer);DROP DOMAIN IF EXISTS app.ch19_service_minutes;

Authoritative references

Extension, FDW, language, and server-code behavior is privilege-, package-, version-, and platform-sensitive. These PostgreSQL 18 primary sources define the mechanisms used here.

• PostgreSQL 18 — Extending SQL
• PostgreSQL 18 — CREATE OPERATOR
• PostgreSQL 18 — CREATE AGGREGATE
• PostgreSQL 18 — CREATE OPERATOR CLASS
• PostgreSQL 18 — User-Defined Types