PostgreSQL: Stored Procedures, Functions, and Triggers

Writing and Using Stored Procedures#

Stored procedures are like that one responsible friend who ensures everything happens in a structured manner. They encapsulate a sequence of SQL statements into a single callable unit.

What Are Stored Procedures?#

Stored procedures are precompiled SQL code that you can execute repeatedly. They are useful for complex operations that need to be executed multiple times with different parameters.

Advantages of Using Stored Procedures#

• Performance Boost: Reduces the overhead of multiple query parsing and execution.
• Security: Restricts direct table access, enforcing logic through procedures.
• Code Reusability: No need to rewrite the same SQL logic multiple times.

Creating Stored Procedures Using CREATE PROCEDURE#

CREATE PROCEDURE add_user(IN username TEXT, IN email TEXT)
LANGUAGE SQL
AS $$
    INSERT INTO users (name, email) VALUES (username, email);
$$;

Executing Stored Procedures with CALL#

CALL add_user('John Doe', 'john.doe@example.com');

Handling Input and Output Parameters#

Procedures can also return output values using OUT parameters:

CREATE PROCEDURE get_user(IN user_id INT, OUT user_email TEXT)
LANGUAGE SQL
AS $$
    SELECT email INTO user_email FROM users WHERE id = user_id;
$$;

Creating and Managing Functions#

Unlike stored procedures, functions return a value and can be used in SQL queries.

Difference Between Functions and Stored Procedures#

Creating Functions Using CREATE FUNCTION#

CREATE FUNCTION get_user_email(user_id INT) RETURNS TEXT
LANGUAGE SQL
AS $$
    SELECT email FROM users WHERE id = user_id;
$$;

Return Types: Scalar, Table, Composite Types#

Functions can return different types:

CREATE FUNCTION get_users_by_role(role TEXT) RETURNS TABLE(id INT, name TEXT)
LANGUAGE SQL
AS $$
    SELECT id, name FROM users WHERE user_role = role;
$$;

PL/pgSQL Basics#

PL/pgSQL is PostgreSQL’s procedural language, allowing for more complex logic inside functions.

Writing PL/pgSQL Functions#

CREATE FUNCTION increment_counter() RETURNS VOID AS $$
BEGIN
    UPDATE counters SET value = value + 1 WHERE id = 1;
END;
$$ LANGUAGE plpgsql;

Variables, Control Structures (IF, LOOP, WHILE)#

CREATE FUNCTION check_balance(account_id INT) RETURNS TEXT AS $$
DECLARE balance INT;
BEGIN
    SELECT amount INTO balance FROM accounts WHERE id = account_id;
    IF balance < 0 THEN
        RETURN 'Overdrawn';
    ELSE
        RETURN 'Sufficient funds';
    END IF;
END;
$$ LANGUAGE plpgsql;

Triggers and Event-Driven Programming#

Triggers are like the database’s automatic reaction to events.

What Are Triggers?#

A trigger is a function that executes automatically before or after specific database events (INSERT, UPDATE, DELETE).

Creating Triggers Using CREATE TRIGGER#

CREATE FUNCTION log_user_activity() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_logs (user_id, action, timestamp)
    VALUES (NEW.id, 'User Created', now());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_created_trigger
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_activity();

Using Procedural Languages (PL/Python, PL/Perl)#

For advanced scripting, PostgreSQL supports PL/Python and PL/Perl.

Enabling PL/Python and PL/Perl#

CREATE EXTENSION plpython3u;

Writing Functions in PL/Python#

CREATE FUNCTION reverse_string(text) RETURNS TEXT AS $$
    return text[::-1]
$$ LANGUAGE plpython3u;

When to Use PL/Python or PL/Perl Over PL/pgSQL#

• Use PL/Python for machine learning, data analysis, or complex string manipulation.
• Use PL/Perl when handling complex text processing tasks.

Hands-On Exercise#

1. Create a stored procedure that inserts data into a table and handles errors.
2. Develop a function that returns a computed value based on table data.
3. Implement PL/pgSQL control structures in a function.
4. Create and test triggers for automatic updates on related tables.
5. Write a PL/Python function to process JSON data within PostgreSQL.

By now, you should be able to craft stored procedures, write efficient functions, and set up event-driven triggers like a PostgreSQL wizard. Happy coding!