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

September 4th, 2023
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.

Prerequisites

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

Run your PostgreSQL deployments in a high-performing and cost-effective open cloud infrastructure. Cherry Servers' bare metal cloud and virtual servers offer automatic scaling, pay-as-you-go pricing, and free 24/7 technical support.

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.

psql -U postgres

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
);

CREATE TABLE

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:

CREATE OR REPLACE PROCEDURE create_invoice

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;
$$;

CREATE OR REPLACE PROCEDURE mark_invoice_paid

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

\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');

CALL create_invoice

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;

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);

call mark_invoice_paid

1 here refers to the invoice ID.

Discover how MKC Limited leveraged Cherry Servers' secure cloud infrastructure to enhance data security, reduce cost, and increase system efficiency.

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.

select * FROM invoices

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.

Mantas is a hands-on growth marketer with expertise in Linux, Ansible, Python, Git, Docker, dbt, PostgreSQL, Power BI, analytics engineering, and technical writing. With more than seven years of experience in a fast-paced Cloud Computing market, Mantas is responsible for creating and implementing data-driven growth marketing strategies concerning PPC, SEO, email, and affiliate marketing initiatives in the company. In addition to business expertise, Mantas also has hands-on experience working with cloud-native and analytics engineering technologies. He is also an expert in authoring topics like Ubuntu, Ansible, Docker, GPU computing, and other DevOps-related technologies. Mantas received his B.Sc. in Psychology from Vilnius University and resides in Siauliai, Lithuania.

Start Building Now

Deploy your new Cloud VPS server in 3 minutes starting from $5.83 / month.

We use cookies to ensure seamless user experience for our website. Required cookies - technical, functional and analytical - are set automatically. Please accept the use of targeted cookies to ensure the best marketing experience for your user journey. You may revoke your consent at any time through our Cookie Policy.
build: 0cab4b92.684