PostgreSQL Procedures

Benefits of Using Stored Procedures:

• Transaction control allowing us to COMMIT and ROLLBACK inside procedures.
• Very helpful for Oracle to PostgreSQL migration, the new procedure functionality can be a significant time saver.
• As you can see there are a couple of similarities between CREATE FUNCTION and CREATE PROCEDURE so things should be really easy for most end users.

In this syntax:

• First, specify the name of the stored procedure after the CREATE PROCEDURE clause.
• Next, define a parameter list which is similar to the parameter list of user-defined functions.
• Then, specify the programming language for the stored procedure such as PLpgSQL and SQL.
• After that, place the code in the body of the stored procedure after that AS keyword.
• Finally, use double dollar ($$) to end the stored procedure.

CREATE TABLE accounts (
    id INT GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR(100) NOT NULL,
    balance DEC(15,2) NOT NULL,
    PRIMARY KEY(id)
);

INSERT INTO accounts(name,balance)
VALUES('Bob',10000);

INSERT INTO accounts(name,balance)
VALUES('Alice',10000);

Procedure:

 

CREATE OR REPLACE PROCEDURE transfer(INT, INT, DEC)
LANGUAGE plpgsql   
AS $$
BEGIN
    UPDATE accounts
    SET balance = balance - $3
    WHERE id = $1;
    UPDATE accounts
    SET balance = balance + $3
    WHERE id = $2;
     COMMIT;
END;
$$;

Output:

 

 CALL transfer(1,2,1000);