How to Create a PostgreSQL CREATE FUNCTION Statement

Introduction

This tutorial will explain how to create a PostgreSQL CREATE FUNCTION using the CREATE TRIGGER statement. The CREATE TRIGGER statement will initiate a new trigger that will be connected to a specific table to execute a specified command when specific events occur. The trigger can be created to fire before the operation is initiated or after the operation is executed. The trigger can skip the operation for a current table row or change a row being inserted if it occurs before the event. If the trigger occurs after the event, then the trigger will become tied to the changes.

Prerequisites for Creating a PostgreSQL CREATE FUNCTION using the PostgreSQL CREATE FUNCTION Statement.

• A basic understanding of what a PostgreSQL trigger is and how it is used.

• The PostgreSQL server must be properly installed, configured and running in the background.

Execute the following command to start the PostgreSQL server using a LINUX machine:

• Verify the service is running by executing the following command:

The results should resemble the following:

Execute the following instructions to start, stop and/or restart the PostgreSQL server with a Windows machine:

1. Open Control Panel
2. Open Administrative Tools
3. Open Services
4. Find the PostgreSQL Server service
5. Start, Stop or Restart the service

The results should resemble the following:

How to Create a Sample Table

Execute the following command to create a sample table, named clients, to use later in the tutorial :

Now execute the following script to add records to the table clients:

An Explanation of the “CREATE” and “REPLACE” Functions

The CREATE feature will define a new function. The REPLACE function will replace an existing definition that will define a characteristic. Note that the user must have utilization privileges on the language being used.

Note: Functions may be created in a variety of languages, such as sq., PL/pgSQL, C, Python, etc.

Execute the following script to create a basic user-defined function using the CREATE FUNCTION statement:

The following is a breakdown of the above CREATE FUNCTION statement:

1. The CREATE FUNCTION is specified followed by the desired name of the function.
2. Parameters are specificed inside parenthesis; separate multiple parameters with a comma.
3. Specify a return type of the function after the RETURN keyword. The return_datatype can be any of the following:
   • composite type
   • domain type
   • table type reference
4. The executable command is inserted between the BEGIN and END blocks.
5. Finally, the desired language of the function is specified. In this case plpgsql was used. SQL, C or internal may also be used.

A PostgreSQL CREATE FUNCTION Example

With a better understanding of the PostgreSQL CREATE FUNCTION, execute the following script to create the first simple function:

The above function will return the total number of records within the PostgreSQL table named clients.

When the above query is executed the results will display CREATE FUNCTION.

Execute this function with the following command:

The result should resemble the following:

Conclusion

This tutorial explained how to create a PostgreSQL CREATE FUNCTION using the CREATE TRIGGER statement. The article specifically covered how to create a sample table and provided an explanation of the CREATE and REPLACE functions. The tutorial also provided an actual PostgreSQL CREATE FUNCTION example and a step-by-step breakdown of the CREATE FUNCTION statement script. Remember that the CREATE feature will define a new function and the REPLACE function will replace an existing definition.