Create a stored procedure in PostgreSQL

Stored procedures are essential for efficiently managing database operations. In this comprehensive article, I’ll walk you through everything you need to know about how to create a stored procedure in PostgreSQL.

Create a stored procedure in PostgreSQL

We can extend the functionality of the PostgreSQL database using various procedural languages, often referred to as stored procedures.

With the help of stored procedures, we can create our own functions that we can call or reuse whenever needed.

Syntax

Before PostgreSQL version 11, stored procedures were created using the CREATE FUNCTION statement, but with PostgreSQL version 11, they are made using the CREATE PROCEDURE statement.

Case 1: Version before 11:

CREATE FUNCTION function_name(argument1 type,argument2 type)
 RETURNS type AS
BEGIN
  staments;
END;
LANGUAGE 'language_name';

Case 2: Version 11 or next

CREATE PROCEDURE procedure_name(argument type)
 RETURNS type AS
BEGIN
  staments;
END;
LANGUAGE 'language_name';

• function_name or procedure_name: It is the name of the function or procedure that we want to create.
• Argument type: It is used to provide a list of arguments with their datatype, like boolean, integer, etc, in parentheses of a function or procedure.
• Returns: It is used to specify the return type of the function or procedure.
• BEGIN and END: Blocks of code are written between BEGIN and END.
• Language: It is used to specify the procedural language of a function or procedure. For PostgreSQL, it is the plpgsql.

To call a created function or procedure, we can use:

SELECT * FROM function_name(val_1); -- for function

CALL procedure_name(val_1); -- for procedure

To call a created function or procedure that returns a table, we can use this one:

SELECT function_name(val_1); -- for function

CALL procedure_name(val_1); -- for procedure

To call a created function or procedure that takes one of the variables from a column in a table, we can use this one:

SELECT function_name(val_1); -- for function

CALL procedure_name(val_1); -- for procedure

Also, read, PostgreSQL DATE Format + Examples

Prerequisites for Creating Stored Procedures

Before creating stored procedures, ensure you have:

1. PostgreSQL version 11 or higher installed
2. Appropriate database permissions

Now, assuming you are ready with all the Prerequisites, let’s discuss the possible approaches to create the stored procedure.

Approach 1: Simple Procedure with No Parameters

Below is a simple example of a procedure that doesn’t take any parameters

CREATE OR REPLACE PROCEDURE update_last_login()
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE users SET last_login = CURRENT_TIMESTAMP;
  COMMIT;
END;
$$;

After executing the above query, I got the expected output as shown in the screenshot below.

Now, we can execute the following query to call the same stored procedure.

CALL update_last_login();

Approach 2: Procedure with Input Parameters

Let’s create a more efficient stored procedure that accepts parameters

CREATE OR REPLACE PROCEDURE create_new_customers(
  customer_firstname VARCHAR,
  customer_lastname VARCHAR,
  customer_email VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO customer (first_name, last_name, email)
  VALUES (customer_firstname, customer_lastname, customer_email);
  COMMIT;
END;
$$;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

Now, we can execute the following query to call the same stored procedure.

CALL create_new_customers('John', 'Doe', 'john@example.com');

The query executed successfully as shown below.

Now, we can execute the query below to verify that the record has been inserted successfully, as shown in the screenshot below.

Select * from customer

Approach 3: Procedure with Transaction Control

Below is an example where we can run transactions directly inside procedural code.

CREATE OR REPLACE PROCEDURE transfer_funds(
  senderid INT,
  receiverid INT,
  amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
  -- Start a transaction
  BEGIN
    -- Deduct from sender
    UPDATE accounts SET balance = balance - amount 
    WHERE id = senderid;
    
    -- Add to receiver
    UPDATE accounts SET balance = balance + amount 
    WHERE id = receiverid;
    
    -- Check if sender has sufficient funds
    IF (SELECT balance FROM accounts WHERE id = senderid) < 0 THEN
      RAISE EXCEPTION 'Insufficient funds';
      ROLLBACK;
    ELSE
      COMMIT;
    END IF;
  END;
END;
$$;

After executing the above query, the stored procedure has been created successfully as shown in the screenshot below.

Approach 4: Using Conditional Logic

We can execute the below query with certain conditional logic.

CREATE OR REPLACE PROCEDURE process_orders(order_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
  order_total DECIMAL;
  customer_status VARCHAR;
BEGIN
  SELECT total INTO order_total FROM orders WHERE id = order_id;
  SELECT status INTO customer_status FROM customer 
  WHERE id = (SELECT customer_id FROM orders WHERE id = order_id);
  
  IF customer_status = 'Premium' THEN
    UPDATE orders SET total = order_total * 0.9 WHERE id = order_id;
    COMMIT;
  ELSIF order_total > 2000 THEN
    UPDATE orders SET total = order_total * 0.95 WHERE id = order_id;
    COMMIT;
  ELSE
    -- No discount applies
    COMMIT;
  END IF;
END;
$$;

After executing the above query, I got the expected output as per the below screenshot.

Best Practices for PostgreSQL Stored Procedures

Below are the quick lists of best practices those you must follow.

• Use meaningful names: Choose clear, descriptive names for your procedures
• Document your code: Add comments explaining what the procedure does
• Error handling: Always include robust error handling
• Avoid excessive logic: Keep procedures focused on database operations
• Security: Use parameter validation to prevent SQL injection
• Testing: Test procedures thoroughly with various input scenarios
• Version control: Keep track of procedure changes in your source control system

Conclusion

Stored procedures in PostgreSQL provide a powerful way to use the database logic, improve performance, and enhance security. Since PostgreSQL 11, the dedicated CREATE PROCEDURE syntax has made procedures even more useful by enabling transaction management within procedural code.

By following the examples and best practices in this article, you’ll be well-equipped to create, execute, and manage stored procedures in your PostgreSQL databases.

You may like the following PostgreSQL tutorials:

• Postgresql date between two dates
• How to create a view in PostgreSQL
• PostgreSQL Min With Examples
• Postgresql create user with password
• How to set user password in PostgreSQL
• Postgresql ilike case insensitive