How to Create a Stored Procedure in PostgreSQL | Step-by-Step

PostgreSQL is a free and open-source object-relational database management system (ORDBMS). It is trusted by many large-scale projects for its reliability, robustness, performance, and extensibility. One of the methods that can extend PostgreSQL is stored procedures.

In this tutorial, you will learn what Postgres stored procedures are and how to create and use them.

#What are stored procedures?

Postgres stored procedures are routines that contain one or more SQL statements designed to execute a task or set of tasks on a database server. For example, they can be used to extract an employee's information or to calculate salaries. They consolidate and centralize logic and execute on the database itself. On PostgreSQL, we can write stored procedures in other programming languages besides SQL, such as PL/Python or PL/Perl.

Stored procedures also come with other benefits, such as:

• Optimization: Stored procedures are often compiled after their creation, improving the database performance during execution.
• Reusability: Stored procedures encourage reusability and help create modular database design.
• Reduced network traffic: As the stored procedure is executed on the database server, only the information needed to call the procedure is sent to the network.

In PostgreSQL, the user who creates the procedure becomes the owner of the procedure. PostgreSQL stored procedures showcase SQL's versatility in business applications. For those looking to grow their expertise can explore comprehensive SQL courses that can help you learn database functionalities, including but not limited to creating and optimizing such procedures.

#Prerequisites

To follow this how-to guide, you will need the latest PostgreSQL server installed.

#1. Create a table

#Step 1: Connect to PostgreSQL server

To connect to the server, we need to use the psql tool. The command is as follows:

psql -U postgres

-U specifies the postgres user with which the connection will be established.

We have now established the connection, and the psql prompt is displayed, waiting for commands.

#Step 2: Create a table

Before creating our stored procedures, we need to create a table. We will use a basic invoice system across the how-to. We create the invoicing model as follows:

CREATE TABLE invoices (
  invoice_id SERIAL PRIMARY KEY,
  customer_name TEXT NOT NULL,
  total_amount NUMERIC NOT NULL,
  invoice_date DATE NOT NULL,
  is_paid BOOLEAN DEFAULT FALSE
);

We have created the table, but it does not contain any data yet. We will populate it with data using stored procedure calls.

#2. Create stored procedure in PostgreSQL

The syntax for creating a procedure is as follows:

CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
    | sql_body
  } ...

#Stored procedure 1: create_invoice():

To populate the table invoices we created above, we will use the create_invoice procedure. To create the procedure, we enter the below code in psql:

CREATE OR REPLACE PROCEDURE create_invoice(
  IN customer_name TEXT,
  IN total_amount NUMERIC,
  IN invoice_date DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO invoices (customer_name, total_amount, invoice_date)
  VALUES (customer_name, total_amount, invoice_date);
END;
$$;

Let's break down the components of this stored procedure:

1. CREATE OR REPLACE PROCEDURE defines the creation of a new stored procedure. Any stored procedure with the same name gets replaced by this one.
2. The IN specifies that the parameters are used for input purposes. Values are passed to the stored procedures using these parameters.
3. LANGUAGE plpgsql specifies the language that we are using in the stored procedure. plpgsql is a procedural language used in PostgreSQL.
4. AS $$ marks the beginning of the procedure's body, and END; marks its end.

To create the procedure, we enter the above code in the psql prompt:

The create_invoice procedure creates a record in the invoices table every time it is called.

#Stored procedure 2: is_paid():

We now create the is_paid procedure using the following commands in psql:

CREATE OR REPLACE PROCEDURE mark_invoice_paid(
  IN invoice_id INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE invoices
  SET is_paid = TRUE
  WHERE invoices.invoice_id = mark_invoice_paid.invoice_id;
END;
$$;

This procedure will update a record every time it is called.

#3. List stored procedure in PostgreSQL

Now that we have created the stored procedures, we can list them using \df in psql.

\df

The two stored procedures are listed along with their argument data types.

#4. Call stored procedure in PostgreSQL

We can now call the stored procedures using the arguments listed by the command \df.

#Step 1: Calling create_invoice

Let’s create an invoice. To do so, we call the create_invoice procedure with the following command:

CALL create_invoice('Customer 1', 100, '2023-07-20');

This creates a record in the invoices table.

#Step 2: Verify the record insertion

Verifying the records in the invoices table shows that the stored procedure has inserted data in the table.

select * FROM invoices;

#Step 3: Calling mark_invoice_paid

Now that we have created an invoice, we can mark it as paid using the mark_invoice_paid procedure. We call the procedure as follows:

call mark_invoice_paid(1);

1 here refers to the invoice ID.

#Step 4: Verify the record update

To verify the record update, we use the following command:

select * FROM invoices;

Listing the record of table invoices shows that the invoice with ID 1 has been updated. The field is_paid changed from f to t as expected.

#Conclusion

In this how-to guide, we explained what a Postgres stored procedure is and highlighted its benefits. We also covered the creation and execution of the stored procedures in PostgreSQL, demonstrating how data can be inserted and updated using business logic stored directly in the database. You can find more information about PostgreSQL stored procedures in the official PostgreSQL documentation.