Beyond the Basics:Custom Data Types and Functions in Postgresql

What are custom data types and functions ?
In PostgreSQL custom data types and functions are powerful features that allow users to define their own data types and operations on these types.This enables the creation of specialized data structures and behaviors tailored to specific needs, which can enhance the flexibility and functionality of a database.

In this article we will cover the various custom data types and also how to create our own PostgreSQL functions.

There are two types of custom data types in PostgreSQL and they include:

1.CREATE DOMAINS
2.CREATE TYPE

1.CREATE DOMAINS
creates a new domain. A domain is essentially a data type with optional constraints (restrictions on the allowed set of values). The user who defines a domain becomes its owner.

Domains are useful for abstracting common constraints on fields into a single location for maintenance. For example, several tables might contain email address columns, all requiring the same CHECK constraint to verify the address syntax. Define a domain rather than setting up each table's constraint individually.

Here is a sample example of creating domains
1.Basic syntax of creating a create domain:

CREATE DOMAIN name [as] data_type
[ COLLATE collation ]
[ DEFAULT expression ]
[ constraint [ ... ] ]

where constraint is
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }


1. Example Implementation of CREATE DOMAIN

CREATE DOMAIN date_of_birth DATE
CHECK (VALUE <= CURRENT_DATE);

The following query statement uses the CREATE DOMAIN to create a new domain called date_of_birth which will check the date of birth against the current date

Then when you create a table you can assign the date_of_birth data type to its respective column as follows:

CREATE TABLE students(
  id serial PRIMARY KEY,
  first_name VARCHAR NOT NULL,
  last_name  VARCHAR NOT NULL,
  gender VARCHAR NOT NULL,
  date_of_birth DATE_OF_BIRTH 

);

To validate that the CREATE DOMAIN has been created and works we will write the following query to check its validity
e.g

INSERT INTO students(first_name,last_name,gender,date_of_birth)
VALUES('Morgan','Timberlake','male','19029-07-31');

Output:
ERROR: value for domain date_of_birth violates check constraint "date_of_birth_check"

The following SQL statement issued an error because it violated the date_of_birth check.

To check where all domain will be list you can type \dD

2.CREATE TYPE
Registers a new data type for use in the current database. The user who defines a type becomes its owner.

If a schema name is given then the type is created in the specified schema. Otherwise it is created in the current schema. The type name must be distinct from the name of any existing type or domain in the same schema. (Because tables have associated data types, the type name must also be distinct from the name of any existing table in the same schema.)

CREATE TYPE creates a composite type,enum type ,range type and a base type

To better understand CREATE TYPE we are going to create our own CREATE TYPE data type

Example:in this example we want to get the students information .

we need to create a suitable data type using the following query

CREATE TYPE student_info AS (
   student_id  INT NOT NULL
   student_name VARCHAR(255) NOT NULL,
   student_gpa NUMERIC(5,3),
   student_enrolled_date DATE,
   student_graduated BOOLEAN
);

This query defines a new data type called student_info with the following columns name, gpa,enrolled_date,graduated

After creating the new data type you can use the defined columns to create a table to store the students information.You can do so by writing the following query/statement

CREATE TABLE students(
   student_info student_info NOT NULL
);

This table will store a single row of student information for each student.

We can also use the enrolled_date type to define parameters and return values for functions:

CREATE FUNCTION get_student_enrolled_date(student_id INT) RETURNS enrolled_date AS $$
SELECT enrolled_date FROM student_info WHERE id = $1;
$$ LANGUAGE SQL;

This creates a function called get_student_enrolled_date() that takes a student ID as input and returns the students_enrolled_date as output. The function's return type is enrolled_date, which means that it can only return values that are valid enrolled_date.

Conclusion
Creating your own custom data types has a couple of benefits and they include:

Data integrity: User-defined types can be used to enforce constraints on data, such as ensuring that all values in a column are within a certain range or have a specific format. This can help to prevent data corruption and improve the reliability of your database.

Consistency: User-defined types can help to ensure that data is stored and used consistently across different tables and applications.

Re-usability: User-defined types can be reused in multiple tables, which can save you time and effort when defining your database schema.

Resource for reference:
[https://www.postgresql.org/docs/current/sql-createtype.html]
[https://www.postgresql.org/docs/current/sql-createdomain.html]