Jan 3, 2025 by Robert Gravelle

Composite Types

Welcome to the second installment of this series on creating custom data types in PostgreSQL using Navicat Premium 17. In part 1, we learned how to create a custom Domain for the free DVD Rental database. A Domain is a user-defined data type with constraints such as NOT NULL and CHECK. In today's blog, we'll create a Composite Type to return complex data from a user-defined function.

PostgreSQL Types Defined

Types are generated using the CREATE TYPE command. It creates a Composite Type that may be used in stored procedures and functions as the data types of input parameters as well as returned values.

PostgreSQL's CREATE TYPE supports four primary variations:

• Composite Types: Define composite data which combines two or more data types. allowing creation of complex, multi-field data types that can represent intricate data structures.
• Enumeration Types: Defined as a fixed set of predefined, named values, restricting input to only those specific options.
• Range Types: Representing continuous intervals between values, enabling sophisticated operations on contiguous data ranges like dates or numbers.
• Base Types: User-defined types may be created based on existing base types like int, varchar, or numeric. While there isn't a specific "Base Type" for user-defined types, new types are essentially extensions or constraints applied to these underlying PostgreSQL base types.

In the next few sections we'll explore Composite Types in more detail by creating a Type and using it in a function.

The CREATE TYPE Statement

All Types are created using the CREATE TYPE statement. Let's say that we wanted to have a function that returns several values about a film such as the film ID, title, and release_year. Here is the statement that creates a type named "film_summary":

CREATE TYPE film_summary AS (
    film_id INT4,
    title VARCHAR(255),
    release_year CHAR(4)
);

Creating a Type in Navicat 17

Navicat Premium 17 and Navicat for PostgreSQL 17 both offer a GUI-based tools for generating types without having to know all of the exact syntax. You'll find it under "Others" in the main toolbar:

Next, we'll click on the arrow next to the "New Type" item in the "Objects" toolbar. That bring up the four different options for creating a type. Select the "Composite" item from the context menu:

That will bring up a grid in which we can enter the field details. Since the three fields which make up the "film_summary" Type already exist, we can bring up the "film" table in the Table Designer and copy the Type and Length data from there. Here are the three fields highlighted in red:

The grid will already have an empty row for the first field. Once we've entered its details, we can add a new row by clicking on "Add Member". Here is the completed grid:

Before clicking the "Save" button we can take a look at the statement that Navicat will generate by clicking on the "SQL Preview" tab:

Notice that the Type name is "Untitled" since we haven't yet saved the definition. That is expected.

Let's assign the name now. Clicking on the "Save" button brings up the "Save As" dialog where we can give our Type a name of "film_summary":

Using the film_summary Type In a Function

Now it's time to use the "film_summary" as the return type of a function. Like the Type creation, we'll use Navicat's GUI tool to do so. To access the Function Designer, click the "Function" button on the main toolbar followed by "New Function" on the "Objects" toolbar:

The editor will pre-populate most of the syntax for the CREATE FUNCTION for us; we just need to supply a few details like the function name, input parameters, return type, and function body. Here is the completed CREATE FUNCTION statement:

CREATE FUNCTION get_film_summary (f_id INT4)
  RETURNS film_summary
AS $BODY$
  SELECT 
    film_id,
    title,
    release_year
  FROM
    film
  WHERE
    film_id = f_id;
$BODY$
  LANGUAGE SQL VOLATILE;

Also be sure to set the language to "SQL".

Once we click the "Save" button, our function is ready to be used. The quickest and easiest way to try a function is to click the "Execute" button. That will bring up a prompt for use to supply a value for the "f_id" parameter:

The results should then appear in a new Result tab:

Conclusion

In today's blog, we created a Composite Type using Navicat Premium 17's Type tool and designed a function that returns our Type. Part 3 will continue with Enumeration Types.