Examples of PostgreSQL Stored Procedures: The Ultimate Guide

On a broader level, PostgreSQL supports several procedural languages that help sustain data models that repeat previous values. PostgreSQL Stored Procedures support procedural operations, which are helpful when building powerful database apps — they increase performance, productivity, and scalability. Like UDFs, stored procedures are a sequence of SQL statements that are available to applications that access an RDBMS.

In short, developing custom functions becomes easier using PostgreSQL Stored Procedures. Moreover, once you have created them, you can deploy stored procedures in any app based on your requirements. So, in this blog, we help you learn about Stored Procedures in PostgreSQL in detail. Additionally, through some workflow examples, we will provide you with the skillset to apply these methods in real-life situations.

How Do Postgres Stored Procedures Affect Your Workflow (Advantages vs Disadvantages)?

Advantages	Disadvantages
As stored Procedures can be reintroduced as often as possible, the number of visits between application and database servers is dramatically reduced.	Using PostgreSQL Stored Procedures can slow down the software development process because stored procedure programming necessitates specific abilities that many developers lack. (This is a human resources challenge)
To retrieve the results and remove the waiting time to get a response, now the app simply has to issue a function call instead of sending SQL statements.	It is difficult to manage workflow as many versions exist, and difficulty when debugging can not be undermined either.
The application’s performance is increased. The user-defined functions and stored procedures are now pre-combined in the PostgreSQL database server.	A portability issue exists. Other DBMS systems like MySQL and MS SQL Server are not supported.

How to CREATE OR REPLACE PostgreSQL Stored Procedures?

In PostgreSQL, the “CREATE OR REPLACE PROCEDURE” command allows you to create a new procedure or replace an existing one with a new definition. To CREATE OR REPLACE PROCEDURE” for a user in PostgreSQL, having “USAGE” privilege on the language is a prerequisite.

The syntax to “CREATE or REPLACE” PostgreSQL Stored Procedures is given below:

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
  }

1. A procedure in the schema is generated if the user has provided a schema name
2. The “CREATE or REPLACE PROCEDURE” statement can be used to modify or replace the existing definition of an existing procedure. The ownership and permissions will not change.
3. To build a procedure, you must have “USAGE” privilege on the argument types.
4. The user who creates the procedure becomes the owner of the procedure.

The syntax given above is more generic to stored procedures. The one below is for PostgreSQL. 

create [or replace] procedure procedure_name(parameter_list)
language plpgsql
as $$
declare
-- variable declaration
begin
-- stored procedure body
end; $$
Code language: SQL (Structured Query Language) (sql)

Let’s break down the components in this syntax:

• Write the name of the stored procedure after the create procedure keywords.
• Set the parameters for the stored procedure. If not, a stored procedure can accept zero as well. If not, a stored procedure can also accept zero
• Mention plpgsql as the procedural language for the stored procedure.
• Utilize the dollar-quoted string constant syntax to write the body of the stored procedure.

Note that parameters in stored procedures can’t have the out mode, but only the in and inout modes. As a stored procedure can’t return a value, you won’t be able to use the return statement with a value inside a stored procedure like this:

return expression;
Code language: JavaScript (javascript)

But to stop the stored procedure immediately, you can utilize the return statement without the expression as given:

return;
Code language: SQL (Structured Query Language) (sql)

Examples of Postgres Stored Procedure

1. Create_invoice: This stored procedure adds information to the invoices database and computes totals using order details to create a new invoice.

Schema setup

CREATE TABLE orders (
  order_id INT64,
  customer_id INT64,
  total_amount NUMERIC
);
CREATE TABLE invoices (
  invoice_id INT64,
  customer_id INT64,
  invoice_date DATE,
  total_amount NUMERIC
);

Procedure

CREATE PROCEDURE create_invoice(p_order_id INT64)
BEGIN
  DECLARE v_customer_id INT64;
  DECLARE v_total_amount NUMERIC;
  -- Fetch order details
  SELECT customer_id, total_amount
  INTO v_customer_id, v_total_amount
  FROM orders
  WHERE order_id = p_order_id;
  -- Insert into invoices table
  INSERT INTO invoices (invoice_id, customer_id, invoice_date, total_amount)
  VALUES (
    (SELECT IFNULL(MAX(invoice_id), 0) + 1 FROM invoices), -- Auto-increment invoice_id
    v_customer_id,
    CURRENT_DATE(),
    v_total_amount
  );
END;

Usage: 

CALL create_invoice(101);

2. Is_paid: This procedure checks if an invoice has been paid based on a payments table.

Schema Setup:

CREATE TABLE payments (
  payment_id INT64,
  invoice_id INT64,
  payment_date DATE,
  amount_paid NUMERIC
);

Procedure:

CREATE PROCEDURE is_paid(p_invoice_id INT64)
BEGIN
  DECLARE v_total_amount NUMERIC;
  DECLARE v_amount_paid NUMERIC;
  -- Fetch the total amount for the invoice
  SELECT total_amount
  INTO v_total_amount
  FROM invoices
  WHERE invoice_id = p_invoice_id;
  -- Calculate the total payments made for the invoice
  SELECT IFNULL(SUM(amount_paid), 0)
  INTO v_amount_paid
  FROM payments
  WHERE invoice_id = p_invoice_id;
  -- Check if fully paid
  IF v_amount_paid >= v_total_amount THEN
    SELECT 'Paid' AS status;
  ELSE
    SELECT 'Unpaid' AS status;
  END IF;
END;

Usage:

CALL is_paid(201);

Workflow Example

Let’s use a “stored_Procedure_Prac” table to understand the PostgreSQL stored procedure better. We will use an online editor, OneCompiler, to write and run PostgreSQL queries for demo purposes.

Step 1: Create a table with the following inputs, as shown in the code block below.

create table stored_Procedure_Prac (
    id int generated by default as identity,
    name varchar(100) not null,
    balance dec(15, 2) not null,
    primary key(id)
);

insert into stored_Procedure_Prac(name, balance)
values('Don', 400000);

insert into stored_Procedure_Prac(name, balance)
values('Bob', 400000);

Step 2: Now, run an SQL query to show the table.

select * from stored_Procedure_Prac;

The following shall be the result:

Step 3: A stored procedure named “prac_transfer” is created by the query below, and it moves a certain sum of money between accounts.

create or replace procedure prac_transfer(
   sender int,
   receiver int, 
   amount dec
)
language plpgsql    
as $$
begin
    -- subtracting the amount from the sender's account 
    update accounts 
    set balance = balance - amount 
    where id = sender;

    -- adding the amount to the receiver's account
    update accounts 
    set balance = balance + amount 
    where id = receiver;

    commit;
end;$$;

Step 4: Now call a stored procedure. Use the below-given query; a CALL statement is used.

call prac_transfer(1, 2, 1000);

The result would look like this:

Step 5: Now, verify the results by running the following query.

SELECT * FROM stored_Procedure_Prac;

PostgreSQL Stored Procedure vs Function

A stored procedure doesn’t return a result, but a function does. The explanation is straightforward: a stored procedure’s only function is to carry out an operation in order to finish it. Following that, the caller regains control.

Using one of the PostgreSQL Stored Procedures examples query, let’s distinguish:

1. PostgreSQL Stored Procedures’ Query 

CREATE PROCEDURE deactivate_unpaid_accounts()
LANGUAGE SQL
AS $$
  UPDATE accounts SET active = false WHERE balance < 0;
$$;

For calling purposes, post PostgreSQL 11, we use the CALL statement as shown below:

CALL deactivate_unpaid_accounts();

2. PostgreSQL Function Query

CREATE FUNCTION deactivate_unpaid_accounts() RETURNS void
LANGUAGE SQL
AS $$
  UPDATE accounts SET active = false WHERE balance < 0;
$$;

For calling purposes, we used the SELECT statement, not the “CALL” statement, as shown below:

SELECT deactivate_unpaid_accounts();

Transaction Control Capabilities in Postgres Stored Procedures

Transaction control capabilities in stored procedures are vital for ensuring data consistency and integrity for numerous related operations. These capabilities allow developers to group various SQL statements into a single unit of work, ensuring that either all operations succeed or none are applied (i.e., “atomicity”). Here’s an overview of transaction control in stored procedures:

Transaction Control Commands

1. BEGIN TRANSACTION
   • Marks the start of a transaction. In some databases, this is implicit and does not need to be written explicitly.
2. COMMIT
   • Finalizes the transaction, making all changes permanent. Once a COMMIT is issued, the changes made in the transaction are saved to the database.
3. ROLLBACK
   • Reverts all changes made during the transaction. If something goes wrong (e.g., an error occurs in one of the SQL statements), the ROLLBACK command restores the database to its state before the transaction began.
4. SAVEPOINT
   • Creates a point within the transaction to which you can later roll back without affecting the entire transaction. This allows for partial rollback.
5. RELEASE SAVEPOINT
   • Removes a previously defined savepoint.

Benefits of Stored Procedures in PostgreSQL

Stored procedures offer significant benefits in terms of reusability and modularity:

• Reusability: Stored procedures centralize business logic, reducing code duplication and ensuring consistency across multiple applications. Updates to the logic are made in one place, automatically benefiting all users.
• Modularity: Complex tasks are broken down into smaller, manageable units. This enables the separation of concerns, making code easier to maintain and test. Stored procedures can encapsulate logic, improving organization and reducing the likelihood of errors.
• Performance: Being precompiled, stored procedures often execute faster than dynamic SQL queries, reducing parsing overhead. They also minimize client-server communication.
• Security: Access control is simplified by granting permissions to stored procedures rather than directly to data, ensuring that sensitive information remains protected.
• Maintainability: Changes are easier to implement and version-controlled, enhancing long-term maintainability.

Conclusion

In this guide, we’ve taken a closer look at PostgreSQL Stored Procedures and how they can help you streamline your database operations. You’ve seen how to use the “CREATE OR REPLACE PROCEDURE” command to configure reusable SQL blocks that are capable of handling more complex logic within your database. We also covered the differences between stored procedures and functions. While functions are great for returning values and performing calculations, stored procedures are better suited for handling tasks such as transactions and multi-step operations.

If you want to continue your learning of PostgreSQL Commands and operations, go through either of the two articles provided below:

1. The Ultimate Guide to PostgreSQL Subquery for 2022
2. PostgreSQL MAX() Function: Syntax, Working & Examples

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

Want to give Hevo a try? Sign up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. You may also look at the price, which will assist you in selecting the best plan for your requirements.

FAQ on PostgreSQL Stored Procedures