Why copy-paste SQL code when you can automate like a genius?
Introduction: Why Stored Procedures & Functions are a Lifesaver
Let’s face it—writing the same SQL query over and over again sucks.
You tweak one little thing and suddenly have to update 50 places in your code. Painful!
What if I told you PostgreSQL has superpowers that can:
Automate repetitive queries (no more copy-pasting like a robot)
Improve performance by pre-compiling SQL logic
Make your database smarter and your life easier
Enter: Stored Procedures & Functions!
Let’s break them down.
What’s the Difference Between a Function and a Stored Procedure?
| Feature | Functions (CREATE FUNCTION) |
Stored Procedures (CREATE PROCEDURE) |
|---|---|---|
| Returns a value? | Yes (returns a result) | No (just executes SQL statements) |
| Can modify data? | Limited (mostly read-only) | Yes (perfect for transactions) |
| Can use Transactions? | No | Yes (BEGIN, COMMIT, ROLLBACK) |
| Use Case | Data calculations, transformations | Complex operations like batch updates |
Functions return something (like a calculated value).
Stored Procedures just do stuff (like inserting, updating, or deleting).
Creating Functions: Because Smart Databases Do the Math for You
Example 1: A Simple Function to Calculate Total Price
CREATE FUNCTION total_price(unit_price NUMERIC, quantity INT)
RETURNS NUMERIC AS $$
BEGIN
RETURN unit_price * quantity;
END;
$$ LANGUAGE plpgsql;
What it does: Multiplies unit price by quantity.
Why it’s useful: No more repeating price * qty everywhere in your queries!
Calling the Function
SELECT total_price(50, 3);
-- Output: 150
Boom! PostgreSQL does the math for you. No Excel needed!
Example 2: Get a User’s Full Name from the Database
CREATE FUNCTION get_full_name(user_id INT)
RETURNS TEXT AS $$
DECLARE full_name TEXT;
BEGIN
SELECT CONCAT(first_name, ' ', last_name) INTO full_name
FROM users WHERE id = user_id;
RETURN full_name;
END;
$$ LANGUAGE plpgsql;
Using the Function
SELECT get_full_name(1);
-- Output: "John Doe"
Instead of writing SELECT first_name || ' ' || last_name FROM users everywhere, just call get_full_name(1). Much cleaner!
Creating Stored Procedures: Automate Like a Boss!
Scenario:
Your boss says: "We need a procedure that deletes old user accounts. Also, if something goes wrong, don’t delete everything."
Example: Delete Old Users Safely
CREATE PROCEDURE delete_old_users(age_limit INT)
LANGUAGE plpgsql
AS $$
BEGIN
DELETE FROM users WHERE age > age_limit;
END;
$$;
Executing the Procedure
CALL delete_old_users(80);
No SELECT needed, just CALL delete_old_users(80);
All users older than 80 get removed. RIP.
Transactions in Stored Procedures: Because One Wrong Query Can Ruin Everything
Let’s say you’re transferring money between accounts.
If something fails halfway, you DON’T want half the money gone!
Example: Transfer Money with a Transaction
CREATE PROCEDURE transfer_money(from_acc INT, to_acc INT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
BEGIN;
UPDATE accounts SET balance = balance - amount WHERE id = from_acc;
UPDATE accounts SET balance = balance + amount WHERE id = to_acc;
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
END;
$$;
Executing the Money Transfer
CALL transfer_money(1, 2, 500);
If something fails, the whole thing rolls back (no missing money!).
If everything is fine, the transaction commits.
Dropping Functions & Procedures: Oops, I Don’t Need This Anymore!
Delete a Function
DROP FUNCTION total_price(NUMERIC, INT);
Delete a Stored Procedure
DROP PROCEDURE delete_old_users(INT);
Be careful! Once dropped, poof—it’s gone!
Stored Procedures & Functions = SQL Superpowers!
Functions → Great for calculations & quick queries.
Stored Procedures → Automate complex logic, transactions, & updates.
Transactions → Prevent disasters when modifying data.
Clean Code → No more repeating SQL queries 1000 times!
Make PostgreSQL work for you like a true database wizard!
0 Comments