In PostgreSQL, stored procedures can be used for pretty much everything. In this chapter, you have already learned about the CREATE DOMAIN clause, and so on, but it is also possible to create your own operators, type casts, and even collations.

In this section, you will see how a simple type cast can be created, and how it can be used to your advantage. To define a type cast, consider taking a look at the CREATE CAST clause. The syntax of this command is shown in the following code:

test=# h CREATE CAST
Command: CREATE CAST
Description: define a new cast
Syntax:
CREATE CAST (source_type AS target_type)
    WITH FUNCTION function_name [ (argument_type [, ...]) ]
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)
    WITHOUT FUNCTION
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)
    WITH INOUT
    [ AS ASSIGNMENT | AS IMPLICIT ]

URL: https://www.postgresql.org/docs/12/sql-createcast.html

Using this stuff is very simple. You simply tell PostgreSQL which procedure it is supposed to call in order to cast whatever type to your desired data type.

In standard PostgreSQL, you cannot cast an IP address to a Boolean. Therefore, it makes for a good example. First, the stored procedure has to be defined:

CREATE FUNCTION inet_to_boolean(inet) 
RETURNS boolean AS 
$$ 
        BEGIN 
                RETURN true; 
        END; 
$$ LANGUAGE 'plpgsql'; 

For simplicity, it returns true. However, you can use any code in any language to do the actual transformation.

In the next step, it is already possible to define the CAST type:

CREATE CAST (inet  AS boolean) 
WITH FUNCTION inet_to_boolean(inet) AS IMPLICIT; 

The first thing we need to do is tell PostgreSQL that we want to cast inet to boolean. Then, the function is listed and we tell PostgreSQL that we prefer an implicit cast.

It is a simple and straightforward process, and we can test the cast as follows:

test=# SELECT '192.168.0.34'::inet::boolean; 
 bool 
------  
 t 
(1 row) 

Basically, the same logic can also be applied to define collations. Again, a stored procedure can be used to perform whatever has to be done:

test=# h CREATE COLLATION
Command: CREATE COLLATION
Description: define a new collation
Syntax:
CREATE COLLATION [ IF NOT EXISTS ] name (
    [ LOCALE = locale, ]
    [ LC_COLLATE = lc_collate, ]
    [ LC_CTYPE = lc_ctype, ]
    [ PROVIDER = provider, ]
    [ DETERMINISTIC = boolean, ]
    [ VERSION = version ]
)
CREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation

URL: https://www.postgresql.org/docs/12/sql-createcollation.html