# The CREATE PROCEDURE Statement in PostgreSQL

> The CREATE PROCEDURE Statement contains the name of the procedure with its arguments. The body of the procedure contains steps to be performed.

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.

---
[View this page online](https://www.commandprompt.com/education/the-create-procedure-statement-in-postgresql/)