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