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:

sudo service postgresql start
  • Verify the service is running by executing the following command:
service postgresql status

The results should resemble the following:

‚óè postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
Active: active (exited) since Thu 2019-08-01 14:51:20 PST; 36min ago
Process: 1230 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 1230 (code=exited, status=0/SUCCESS)

Aug 01 14:51:20 user-UX330UAK systemd[1]: Starting PostgreSQL RDBMS...
Aug 01 14:51:20 user-UX330UAK systemd[1]: Started PostgreSQL RDBMS.
lines 1-8/8 (END)

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:

Restarting PostgreSQL server in a Windows Machine

How to Create a Sample Table

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

CREATE TABLE clients(
id SERIAL PRIMARY KEY,
first_name VARCHAR(40) NOT NULL,
last_name VARCHAR(40) NOT NULL
);

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

INSERT INTO clients (first_name, last_name)
VALUES ('yeshua', 'galisanao');

INSERT INTO clients (first_name, last_name)
VALUES ('abi', 'galisanao');

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:

CREATE FUNCTION function_name(p1 TYPE, p2 TYPE)
RETURNS return_datatype AS $variable_key$
DECLARE
declaration;
[...]
BEGIN
-- logic
< function_body >
[...]
RETURN { variable_name | VALUE}
END;
LANGUAGE plpgsql;

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:

CREATE OR REPLACE FUNCTION allRecords ()
RETURNS INTEGER AS $allRec$
DECLARE
allRec INTEGER;
BEGIN
SELECT COUNT(*) INTO allRec FROM clients;
RETURN allRec;
END;
$allRec$ LANGUAGE plpgsql;

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:

testdatabae# SELECT allRecords();

The result should resemble the following:

allrecords
------------
2
(1 ROW)

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.