1. Extending PostgreSQL
The previous chapters in this course have explored many of the built-in functions of PostgreSQL which provide you with powerful tools for manipulating data using SQL.
PostgreSQL also provides you with the ability to create your own custom data types, functions and operators to extend the functionality of your database.
2. User-defined data types
Let's take a look at custom or user-defined data types. A user-defined data type is created using the CREATE TYPE command which registers the type in a system table and makes it available to be used anywhere PostgreSQL expects a type name.
Enumerated data types or enums allow you to define a custom list of values that are never going to change, like the days of the week.
As you can see in this example, a new data type called dayofweek is defined as an ENUM using the CREATE TYPE command with a comma separated list of the days of the week.
3. Getting information about user-defined data types
Once your custom data type has been created, you can query the system table called pg_type to get information about all data types available in your database both user-defined and built-in.
In this query you can get the name of the data type using the typname column and the category of the data type using the typcategory column.
The results of the query return dayofweek for the name of the data type that we just created and E for the category where E represents an ENUM type.
4. Getting information about user-defined data types
You can also use the INFORMATION_SCHEMA system database, as we learned about earlier in this course, to get information about user-defined data types.
If we query INFORMATION_SCHEMA.COLUMNS and have a look at the columns in the film table specifically you'll notice that the column_name rating is a USER-DEFINED data_type with a udt_name of mpaa_rating.
The udt_name column for a user_defined data type contains the value of the name provided when creating the data type using the CREATE TYPE command.
You may sometimes find it necessary to learn about the characteristics of your data when working with a new database for the first time.
5. User-defined functions
Another way to extend the capabilities of your PostgreSQL database is with user-defined functions. A user-defined function is the PostgeSQL equivalent of a stored procedure where you can bundle several SQL queries and statements together into a single package using the CREATE FUNCTION command.
In this example we define the function squared that accepts an integer, i, as an input parameter and returns the square of that parameter as the result. The double dollar sign syntax specifies that the function will be using SQL as the language.
6. User-defined functions in the Sakila database
In addition to being an excellent sample relational database, the Sakila DVD Rental Database that you've been using as the dataset throughout this course also showcases the power of PostgreSQL extensibility and comes pre-installed with a few examples of custom or user-defined functions for you to explore and experiment with.
The get_customer_balance function takes a customer_id and a timestamp as input parameters and will calculate the current balance of a customer based on a customer_id as of the timestamp date.
The inventory_held_by_customer function takes an inventory_id as an input parameter and will determine all rows that have a return_date equal to null which means the customer still has the rental.
And finally the inventory_in_stock function takes an inventory_id as an input parameter and will determine if a specific inventory_id is in stock.
7. Let's practice!
Let's take a closer look in the exercises.