Stored Procedures and Triggers in PostgreSQL

1. Introduction

Stored Procedures and Triggers are essential components of PostgreSQL that allow developers to encapsulate logic and automate actions in response to database events.

2. Stored Procedures

What are Stored Procedures?

Stored procedures are a set of SQL statements that can be stored in the database and executed as a single unit. They help in encapsulating business logic and can be reused across applications.

Creating a Stored Procedure

To create a stored procedure in PostgreSQL, use the CREATE PROCEDURE statement. Below is a simple example:

CREATE PROCEDURE add_employee(IN name VARCHAR, IN age INT)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees (name, age) VALUES (name, age);
END;
$$;

Calling a Stored Procedure

To call a stored procedure, use the CALL statement:

CALL add_employee('John Doe', 30);

3. Triggers

What are Triggers?

Triggers are special routines that are automatically executed in response to certain events on a particular table or view, such as INSERT, UPDATE, or DELETE.

Creating a Trigger

To create a trigger, you first need to define a function that will be executed when the trigger is fired:

CREATE FUNCTION log_employee_change()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO employee_audit (employee_id, change_time)
    VALUES (NEW.id, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Next, create the trigger itself:

CREATE TRIGGER employee_update
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_employee_change();

4. Best Practices

Use meaningful names for procedures and triggers to indicate their purpose.
Keep stored procedures focused on a single task to enhance readability and maintainability.
Document your procedures and triggers for future reference.
Test procedures and triggers in a development environment before deploying them to production.

5. FAQ

What is the difference between a stored procedure and a function?

A stored procedure does not return a value, while a function does. Functions can be used in SQL statements, whereas procedures cannot.

Can triggers call stored procedures?

Yes, triggers can call stored procedures as part of their execution, allowing for complex logic to be encapsulated and reused.

How are triggers executed?

Triggers are executed automatically in response to specific events on a table, such as INSERT, UPDATE, or DELETE operations.