Stored Procedures

Code

-- Stored procedure
CREATE OR REPLACE PROCEDURE transfer_money(
    from_id INT, to_id INT, amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    INSERT INTO transactions (from_id, to_id, amount)
    VALUES (from_id, to_id, amount);
    COMMIT;
END;
$$;

-- Call
CALL transfer_money(1, 2, 500.00);

-- function
CREATE OR REPLACE FUNCTION get_user_order_count(user_id INT)
RETURNS INT
LANGUAGE SQL
AS $$
    SELECT COUNT(*) FROM orders WHERE user_id = $1;
$$;

-- Usage
SELECT name, get_user_order_count(id) FROM users;

Explanation

Stored procedures encapsulate business logic; functions can be embedded in SQL queries.

More SQL Snippets