PostgreSQL — Procedures

Basic Procedure

CREATE PROCEDURE update_status(user_id INTEGER, new_status VARCHAR)
AS $$
BEGIN
    UPDATE users SET status = new_status WHERE id = user_id;
END;
$$ LANGUAGE plpgsql;

-- Call
CALL update_status(1, 'active');

Procedure with Transaction

CREATE PROCEDURE transfer_funds(from_id INTEGER, to_id INTEGER, amount DECIMAL)
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$ LANGUAGE plpgsql;

-- Call
CALL transfer_funds(1, 2, 100.00);

Procedure with Parameters

CREATE PROCEDURE add_user(
    p_name VARCHAR,
    p_email VARCHAR,
    OUT p_id INTEGER
)
AS $$
BEGIN
    INSERT INTO users (name, email) VALUES (p_name, p_email) RETURNING id INTO p_id;
END;
$$ LANGUAGE plpgsql;

Drop Procedure

DROP PROCEDURE update_status(INTEGER, VARCHAR);

Procedure vs Function

Feature	Procedure	Function
Return	Via OUT params	RETURN statement
Call	CALL	SELECT
Transactions	Yes	Limited
Use cases	Operations	Calculations

Mini Practice

1. Create a procedure
2. Use transactions
3. Add OUT parameters
4. Call procedures

Up Next

Continue with Triggers — trigger functions.