PostgreSQL Database management system is one of the most popular object-relational DB systems as it is open-source and free. It allows the use of functions/methods to perform specific tasks defined in the function according to the requirement to manage tables. PostgreSQL Functions are called Procedures and are used to perform a task consisting of multiple steps which would normally take several queries.

This guide will explain how to use a CREATE PROCEDURE statement in Postgres along with suitable examples.

CREATE PROCEDURE Statement in PostgreSQL

Functions can be of two types; Built-in functions and user-defined functions and the user is allowed to use these functions. Built-in functions in PostgreSQL are not designed to perform transactional operations so to solve this problem a procedure can be created. The Procedure can be called multiple times and it performs the same set of operations on the PostgreSQL table every time.

Syntax

The following is the syntax to create a procedure in PostgreSQL:

create [or replace] procedure procedure_name(parameter_list)
--Body of the Procedure with language SQL
 language plpgsql
 as $$
 declare
 -- variable declaration
 begin
 -- stored procedure body
 end; $$

In the above code snippet:

- The create [or replace] procedure keyword is used with a procedure name to create a new or modify/replace an existing procedure.
- The procedure/function utilizes the small parenthesis to accept the list of parameters in it. The arguments in the parameter list can be zero to many for one procedure.
- After that, specify the procedural language for the PostgreSQL stored procedure such as SQL, C, etc.
- The procedure’s body must be wrapped between the begin and end keywords.

Create a Stored Procedure

To create a procedure and use it on the PostgreSQL table, first, create a table using this query:

CREATE TABLE accounts (
  id int generated by default as identity,
  name varchar(50),
  balance dec(30,4)
   );

The above query creates a table named “accounts” that has id, name, and balance as its fields. The value in the id field is generated automatically and it is set as the primary key of the table:

Use the following query to insert values/data in the accounts table:

INSERT INTO accounts(name,balance)
 VALUES('Ross',10000), ('Taylor',10000);

The above query only inserts data in the name and balance field as the id is automatically generated:

Use the following query to get the data from the accounts table:

SELECT * FROM accounts;

The above command fetches the data stored in the accounts table:

Use the following code to create a stored procedure named transfer to complete the transactions in the accounts table:

CREATE PROCEDURE transfer(
  account1 int,
  account2 int, 
  amount dec
   )
 language plpgsql
 as $$
 begin
update accounts 
  set balance = balance - amount 
  where id = account1;
 update accounts 
  set balance = balance + amount 
  where id = account2;
 commit;
 end;$$

The above code creates a transfer stored procedure with three arguments which are account1, account1, and amount to be transferred. It uses SQL procedural language and starts the body of the procedure using dollar signs. The body of the procedure contains multiple steps as it starts with subtracting the amount from the account1 account. After that, it adds the said amount to the account2 account and that’s how a transaction completes:

Calling a Procedure

Type the following command to call the procedure by simply typing the name of the procedure with its arguments:

call transfer(1,2,1000);

The PROCEDURE has been called successfully as displayed in the screenshot below:

That’s all about creating the procedure statements in PostgreSQL databases.

Conclusion

The procedure is a user-defined function in PostgreSQL that performs transactional operations on the database tables. The create [or replace] procedure statement is used with a valid procedure name to create a new procedure or modify/replace an existing procedure. The procedure may contain a list of arguments that can be used while calling it. This guide has explained the Create Procedure statement in the PostgreSQL database.