Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-create-procedure/ below:

PostgreSQL - CREATE PROCEDURE - GeeksforGeeks

Last Updated : 15 Jul, 2025

PostgreSQL CREATE PROCEDURE allows developers to define stored procedures that apply complex business logic, transaction handling and multiple SQL statements in a reusable manner. Unlike functions, PostgreSQL stored procedures can manage transactions with commands like COMMIT and ROLLBACK.

Understanding the PostgreSQL procedure syntax and how to efficiently call stored procedures is crucial for simplifying repetitive tasks and improving database performance.

In PostgreSQL, a stored procedure is a set of SQL statements stored on the server that can be invoked to perform operations.

Procedures are designed to implement logic and make database operations more efficient especially when repetitive tasks or transaction handling is involved.

The CREATE PROCEDURE statement allows developers to define these procedures which can execute without returning values, unlike functions that must return values.

Syntax

The following illustrates the basic syntax of the CREATE PROCEDURE statement:

CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)
LANGUAGE plpgsql
AS $$
DECLARE
    -- Variable declarations (optional)
BEGIN
    -- Procedure body (SQL statements)
END;
$$;

• procedure_name: The name of the stored procedure.
  
• parameter_list: The parameters for the stored procedure, which can have IN and INOUT modes. OUT mode is not supported.
  
• LANGUAGE plpgsql: Specifies the procedural language. Other languages like SQL and C can also be used.
  
• $$: Dollar-quoted string constant syntax to define the body of the stored procedure.

Let's start with a basic example where we create a procedure that inserts a record into an employees table.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT
);
CREATE PROCEDURE add_employee(emp_name VARCHAR, emp_age INT)

LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees (name, age) VALUES (emp_name, emp_age);
END;
$$;

Explanation:

• The add_employee procedure inserts a new employee into the employees table by taking the employee's name and age as parameters.

CALL add_employee('John Doe', 25);

This will insert a new employee record with the name "John Doe" and age 25.

Stored procedures in PostgreSQL can accept parameters that influence the execution of the procedure. These parameters can be of type IN (input) or INOUT (input-output). Here's an example of a procedure with parameters:

CREATE PROCEDURE update_employee_age(emp_id INT, new_age INT)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees SET age = new_age WHERE id = emp_id;
END;
$$;

CALL update_employee_age(1, 30);

This procedure updates the age of the employee with ID 1 to 30.

One of the key advantages of using procedures is the ability to manage transactions. We can include BEGIN, COMMIT, and ROLLBACK statements inside procedures to handle transaction logic.

Consider a scenario where we need to transfer money between two accounts. The transaction must either fully complete or roll back if an error occurs.

CREATE PROCEDURE transfer_money(sender INT, receiver INT, amount DECIMAL)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Subtract the amount from the sender's account
    UPDATE accounts SET balance = balance - amount WHERE id = sender;
    -- Add the amount to the receiver's account

    UPDATE accounts SET balance = balance + amount WHERE id = receiver;
    -- Commit the transaction

    COMMIT;
END;
$$;

CALL transfer_money(1, 2, 1000);

This will transfer $1,000 from account 1 to account 2. The COMMIT ensures that the transaction is completed successfully.

To execute a stored procedure in PostgreSQL, the CALL statement is used. The syntax for calling a procedure is straightforward:

CALL procedure_name(argument_list);

CALL add_employee('Alice', 30);

This will execute the add_employee procedure and insert a new record into the employees table.

• The CREATE PROCEDURE statement is used to define new stored procedures in PostgreSQL.
  
• Unlike user-defined functions, stored procedures in PostgreSQL can manage transaction.
  
• Stored procedures are executed using the CALL statement, followed by the procedure name and any necessary arguments.
  
• Stored procedures in PostgreSQL can accept parameters with IN and INOUT modes. IN parameters are used to pass values to the procedure, while INOUT parameters can pass values to and return values from the procedure.

Overall, PostgreSQL stored procedures into your database management allows for efficient execution of complex operations, reducing server load and improving reusability. By mastering the PostgreSQL procedure syntax and the process of calling stored procedures, developers can optimize transaction handling and streamline their database logic. This functionality not only enhances performance but also offers a powerful tool for maintaining data integrity in critical operations.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4