SQL Customization

Understanding the intricacies of database management systems is crucial for building robust and efficient applications. In this chapter, we will delve into the powerful features of PostgreSQL that allow us to define custom datatypes, operators, and functions, thereby enhancing the flexibility and expressiveness of our database.

1. User-Defined Datatypes

User-Defined Datatypes (UDTs) in PostgreSQL empower us to tailor our data structures to fit specific needs. It allows us to craft two different data types: Composite (aka row types) and ENUM. Imagine having a "Color" datatype that stores RGB values, or having a 'Status' datatype that store the states of a product in an inventory system both of which will be making our database more expressive and readable.

1.1. Creating User-Defined Datatypes

Let's dive into creating a "RGB Color" datatype. We can enforce a specific format and validation rules, ensuring consistency across our database.

-- Create the custom color datatype
CREATE TYPE rgb_color AS (
    red INT,
    green INT,
    blue INT
);

In addition, you might want to represent different statuses for items in an inventory management system. In this case, we create a custom ENUM datatype named "status_enum":

-- Create the custom ENUM datatype
CREATE TYPE status_enum AS ENUM (
'In Stock', 
'Out of Stock', 
'Backordered', 
'Discontinued'
);

1.2. Managing User-Defined Datatypes

Need to add another attribute/feature to rgb_color datatype? With ALTER TYPE and ADD ATTRIBUTE we can modify our existing UDT easily:

ALTER TYPE rgb_color
ADD ATTRIBUTE alpha FLOAT;

To add a new value, such as 'In-transit', to an existing ENUM type in PostgreSQL, we need to use use ALTER TYPE with ADD VALUE. Here's how you can do it:

-- Add 'In-transit' to the status_enum ENUM type
ALTER TYPE status_enum 
ADD VALUE 'In-transit';

After running this statement, the status_enum ENUM type will include the 'In-transit' value. You can now use this updated type in your table or any other part of your schema.

ADD ATTRIBUTE is used when altering a composite type (like a custom datatype). It allows you to add a new attribute (field) to the existing composite type. In the previous example, we used ADD ATTRIBUTE to add the 'alpha_channel' attribute to the rgb_color type.

ADD VALUE is used when altering an ENUM type. It allows you to add a new value to the existing ENUM type. In your previous question, you were asking about adding a new value ('In-transit') to an ENUM type.

In the following example, we've used our new datatype status_enum, which can only take on one of the specified values ('In Stock', 'Out of Stock', 'Backordered', 'Discontinued', 'In-transit'), to create a table called inventory_items:

-- Create a table with the custom ENUM datatype
CREATE TABLE inventory_items (
    item_id SERIAL PRIMARY KEY,
    item_name VARCHAR(50),
    item_status status_enum
);

-- Insert rows with different statuses
INSERT INTO inventory_items (item_name, item_status) VALUES ('Widget A', 'In Stock');
INSERT INTO inventory_items (item_name, item_status) VALUES ('Gadget B', 'Backordered');


-- Query the table
SELECT item_name,item_status  FROM inventory_items;


-- Output
item_name | item_status 
------------+--------------
 Widget A    | In Stock
 Gadget B    | Backordered

For more details: https://www.postgresql.org/docs/current/xtypes.html

2. User-defined Functions

Functions are the unsung heroes of databases. Think of them as mini-programs that make your life easier. For instance, if we need to calculate the total price after tax, we can simply have a function handle that for us.

Custom operators add flair to your SQL queries. Picture this: a "price_diff" operator that subtracts discounts to give you the real deal.

2.1 Creating User-Defined Functions

Let's design a function that calculates the total price with tax included.

CREATE FUNCTION calculate_total_price(item_price money, tax_rate numeric)
RETURNS money AS $$
BEGIN
    RETURN item_price + (item_price * tax_rate);
END;
$$ LANGUAGE plpgsql;


-- or create a function to cube an integer
CREATE FUNCTION cubed(i integer) RETURNS integer AS $$
BEGIN
    RETURN i * i * i;
END;    
$$ LANGUAGE plpgsql;
 
SELECT cubed(2); -- 8

2.2 Managing Functions

Need to update the function? Depending on the feature we need to change, many DBMS systems such Postgres and Mysql would require us to drop/delete the function and recreate it. PostgreSQL does support ALTER FUNCTION for changing function properties like SECURITY, SET, and OWNER. But it does not allow changes to the function's name or parameters.

For more details: https://www.postgresql.org/docs/current/xfunc.html

3. User-defined Operators

3.1 Creating User-defined Operators

In PostgreSQL, you can create custom operators using symbols as well as keywords. Let's modify the example to create a custom operator for price difference calculation using the ^^ symbol.

-- Creating a custom operator for price difference calculation using a symbol
CREATE OPERATOR ^^ (
    PROCEDURE = public.calculate_price_diff,
    leftarg = money,
    rightarg = numeric
);

3.2 Modifying Custom Operators

Keep in mind that if you want to change the symbol to another one, you first need to drop, i.e. delete, the existing one and create another operator with your new symbol. This, however, doesn't apply to the operators created with a keyword. For any changes you can use ALTER OPERATOR statement.

-- Deleting a custom operator
DROP OPERATOR IF EXISTS ^^ (money, numeric);
-- Creating a custom operator
CREATE OPERATOR !!! (
    PROCEDURE = public.calculate_price_diff,
    LEFTARG = money,
    RIGHTARG = numeric
);

-- Creating a custom operator with a keyword
CREATE OPERATOR my_operator (
    PROCEDURE = public.calculate_price_diff,
    LEFTARG = money,
    RIGHTARG = numeric
);

ALTER OPERATOR old_operator (money, numeric) 
RENAME TO price_diff;

For more details: https://www.postgresql.org/docs/current/xoper.html

4. Checking Existence and Deletion

4.1.1 Datatypes

To check if a user-defined datatype exists, you can query the pg_type system catalog table. The following query will return information about the "rgb_color" datatype if it exists.

SELECT * FROM pg_type WHERE typname = 'rgb_color';

To delete a user-defined datatype, we use use the DROP TYPE statement.

DROP TYPE IF EXISTS rgb_color;

4.1.2 Functions

To check for the existence of a user-defined function, we query the pg_proc system catalog table. The following query will return information about the "calculate_total_price" function if it exists.

SELECT * FROM pg_proc WHERE proname = 'calculate_total_price';

To delete a user-defined function, you can use the DROP FUNCTION statement.

DROP FUNCTION IF EXISTS calculate_total_price(money, numeric);

4.1.3 Operators

For operators, you can query the pg_operator system catalog table. Here's how you can do it:

SELECT * FROM pg_operator WHERE oprname = '^^';
SELECT * FROM pg_operator WHERE oprname = 'price_diff';

To delete a user-defined operators, we can use the DROP OPERATOR statement. The following statement will delete the "price_diff" operator if it exists.

DROP OPERATOR IF EXISTS price_diff (money, numeric);

Remember to exercise caution when deleting objects from your database, especially if they are being used in production systems. Always ensure you have proper backups and understand the consequences of your actions. Continue exploring the features of PostgreSQL to become a proficient database developer and administrator.

Last updated 2 months ago