Stored Procedures

About

Stored procedures do not return values with RETURN, so the use of RETURN is only to immediatly stop the procedure. (check Stored Procedures)

Unlike Functions, stored procedures support transactions.

Procedures tend to be more performant than functions, because they are compiled objects.

The code is compiled only when the procedure is created (or changed).

Creating

The syntax is very similar to Functions.

You can use pr prefix on procedure names when creating them.

CREATE OR REPLACE PROCEDURE procedure_name(p1 type, ...) AS
$$
    DECLARE
        -- variables..
    BEGIN
        -- statements..
    END;
$$ LANGUAGE plpgsql

With transactions

To make a procedure use a transaction, just add COMMIT at the of the block.

CREATE OR REPLACE PROCEDURE pr_money_transfer(
    sender int,
    receiver int,
    amount dec
) AS
$$
    BEGIN
        UPDATE accounts SET balance = balance - amount WHERE id = sender;
        UPDATE accounts SET balance = balance + amaount WHERE id = receiver;
        COMMIT;
    END;
$$ LANGUAGE plpgsql

Calling

You can't call procedures within SELECT like you could in functions.

CALL procedure_name();

Deleting

DROP PROCEDURE [IF EXISTS] procedure_name(argument_list) [CASCADE | RESTRICT]

Since procedures can be overloaded, Postgres need to know which procedure you want to remove by checking the argument_list.

Usage

Returning a value

You can use INOUT parameter mode to "return" a value.

At the end of the procedure the value in this parameter will be returned.

CREATE OR REPLACE PROCEDURE procedure_name(
    INOUT p1 int DEFAULT 0
) AS
$$
    BEGIN
        p1 := 5
    END;
$$ LANGUAGE plpgsql