7. Database-Side Programming

Database-Side Programming in PostgreSQL

In many projects, business logic is implemented in an application server (for example in Python, Java, or TypeScript). But relational database systems can also execute logic directly inside the database. This is called database-side programming.

In PostgreSQL, the three most important building blocks are:

• Functions (CREATE FUNCTION)
• Stored Procedures (CREATE PROCEDURE)
• Triggers (CREATE TRIGGER + trigger function)

These features help to centralize rules, enforce data quality, and reduce duplicated logic across multiple applications.

Why Use Database-Side Logic?

Typical reasons:

• Consistency: Business rules are enforced at one central point.
• Security: Applications get restricted access and call approved routines instead of writing arbitrary SQL.
• Performance: Some operations are faster when they run close to the data.
• Reusability: The same logic can be used by web apps, batch jobs, and BI tools.

Potential downsides:

• More logic in SQL/PLpgSQL can increase complexity.
• Versioning and deployment must be managed carefully.
• Vendor-specific syntax may reduce portability.
• Limited debugging support: PostgreSQL lacks the mature debugging tools available in modern programming languages, making it harder to troubleshoot complex procedures and functions.

When does database-side programming really pay off?

Database-side programming is particularly valuable in these scenarios:

• Multi-user or multi-application access: When multiple apps or users access the same database, rules in the DB ensure consistency (e.g., banking, inventory).
• Financial or critical transactions: ACID transactions at the database level prevent race conditions and data corruption.
• Complex validation logic: Rules that span multiple tables belong in the database, not duplicated across applications.
• High-volume reporting: Materialized views and aggregate functions are often faster than application-side calculations.

For a single-application, single-user scenario (e.g., a small web app with no concurrency), keeping logic in the application is often simpler and no less safe.

Rule of thumb for most real-world systems: Move critical Rules, validation, and enforcement to the database; keep application-specific business logic and workflows in the app.

1. PostgreSQL Functions

A function returns a value (scalar, row, or table). Functions can be used in SQL expressions, SELECT lists, or WHERE clauses.

1.1 Basic Function Example

CREATE OR REPLACE FUNCTION calculate_vat_net_to_gross(
  net_amount NUMERIC,
  vat_rate NUMERIC DEFAULT 0.20
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN ROUND(net_amount * (1 + vat_rate), 2);
END;
$$;

Usage:

SELECT calculate_vat_net_to_gross(100.00);       -- 120.00
SELECT calculate_vat_net_to_gross(100.00, 0.10); -- 110.00

Using a function in a real-world query:

Assume a products table with net prices:

SELECT
    product_id,
    name,
    net_price,
    calculate_vat_net_to_gross(net_price) AS gross_price,
    calculate_vat_net_to_gross(net_price, 0.07) AS reduced_vat_price
FROM products
WHERE net_price > 50.00
ORDER BY gross_price DESC;

This query calculates both standard (20%) and reduced (7%) VAT for all products over 50 EUR net price.

Why use a function here instead of inline calculation?

You could write the formula directly in the SELECT:

-- Without function (harder to maintain):
SELECT
    product_id,
    name,
    net_price,
    ROUND(net_price * 1.20, 2) AS gross_price,
    ROUND(net_price * 1.07, 2) AS reduced_vat_price
FROM products;

But using a function offers several advantages:

1. Single source of truth: If VAT law changes (e.g., from 20% to 21%), you update only the function, not dozens of queries across the codebase.
2. Consistency: All parts of the application use the same logic, preventing calculation errors.
3. Readability: calculate_vat_net_to_gross(price) is clearer than ROUND(price * 1.20, 2).
4. Reusability: The same function can be called from reports, APIs, batch jobs, and other routines.
5. Flexibility: You can add more complex logic (rounding rules, special rates, historical rates) inside the function without changing all callers.

1.2 SQL Function Example (No PL/pgSQL Needed)

If a function is just one query, LANGUAGE sql is often enough:

CREATE OR REPLACE FUNCTION full_name(first_name TEXT, last_name TEXT)
RETURNS TEXT
LANGUAGE sql
AS $$
  SELECT CONCAT(first_name, ' ', last_name);
$$;

1.3 Returning a Table

CREATE OR REPLACE FUNCTION active_students()
RETURNS TABLE(student_id INT, name TEXT)
LANGUAGE sql
AS $$
  SELECT id, full_name
  FROM students
  WHERE is_active = TRUE
  ORDER BY full_name;
$$;

Usage:

SELECT * FROM active_students();

Learn more about PL/pgSQL

For detailed documentation on PL/pgSQL syntax, control structures, error handling, and advanced features, see the official PostgreSQL documentation:

PL/pgSQL – SQL Procedural Language

2. Stored Procedures in PostgreSQL

Stored procedures were introduced in PostgreSQL 11 (CREATE PROCEDURE).

Main difference compared to functions:

• Functions are called with SELECT ... and must return a value.
• Procedures are called with CALL ... and do not have to return a value.

Also important:

• Procedures can perform transaction control (COMMIT, ROLLBACK) in valid call contexts.

2.1 Procedure Example: Transfer Funds

Assume this table structure:

CREATE TABLE accounts (
  account_id INT PRIMARY KEY,
  owner_name TEXT NOT NULL,
  balance NUMERIC(12,2) NOT NULL CHECK (balance >= 0)
);

CREATE TABLE transfer_log (
  transfer_id BIGSERIAL PRIMARY KEY,
  from_account INT NOT NULL,
  to_account INT NOT NULL,
  amount NUMERIC(12,2) NOT NULL CHECK (amount > 0),
  transferred_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Procedure:

CREATE OR REPLACE PROCEDURE transfer_funds(
  p_from_account INT,
  p_to_account INT,
  p_amount NUMERIC(12,2)
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_source_balance NUMERIC(12,2);
BEGIN
  IF p_amount <= 0 THEN
    RAISE EXCEPTION 'Amount must be greater than zero';
  END IF;

  IF p_from_account = p_to_account THEN
    RAISE EXCEPTION 'Source and target account must differ';
  END IF;

  SELECT balance
  INTO v_source_balance
  FROM accounts
  WHERE account_id = p_from_account
  FOR UPDATE;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Source account % does not exist', p_from_account;
  END IF;

  IF v_source_balance < p_amount THEN
    RAISE EXCEPTION 'Insufficient funds in account %', p_from_account;
  END IF;

  UPDATE accounts
  SET balance = balance - p_amount
  WHERE account_id = p_from_account;

  UPDATE accounts
  SET balance = balance + p_amount
  WHERE account_id = p_to_account;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Target account % does not exist', p_to_account;
  END IF;

  INSERT INTO transfer_log(from_account, to_account, amount)
  VALUES (p_from_account, p_to_account, p_amount);
END;
$$;

Call the procedure:

CALL transfer_funds(1, 2, 50.00);

Concurrency matters

Notice the FOR UPDATE lock in the example. Without row-level locks, concurrent transfers can lead to race conditions and inconsistent balances.

3. Triggers

A trigger is a mechanism that automatically executes logic when a table event occurs:

• INSERT
• UPDATE
• DELETE
• (and others such as TRUNCATE)

In PostgreSQL, a trigger executes a trigger function (a special function that returns TRIGGER).

3.1 Typical Trigger Use Cases

• Automatic updated_at timestamps
• Audit logging (who changed what and when)
• Validation of complex business constraints
• Synchronization with summary tables

Triggers for business rules from ER diagrams

Many business constraints appear in your ER diagrams only as text annotations or footnotes because they cannot be expressed graphically. These are prime candidates for trigger functions:

• “A student may not enroll in more than 5 courses per semester”
• “A booking is only valid if the booking date is before the event date”
• “An order’s total price must not exceed the customer’s credit limit”
• “A course can only be deleted if no students are currently enrolled”

When you design your database schema, look for these constraints and implement them as BEFORE INSERT/UPDATE triggers to enforce them at the database level, not just in your application code.

3.2 BEFORE UPDATE Trigger: Set updated_at

This example shows how to automatically update a timestamp column whenever a row is modified, without requiring manual application logic.

CREATE TABLE courses (
  course_id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Trigger function:

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$;

Trigger definition:

CREATE TRIGGER trg_courses_set_updated_at
BEFORE UPDATE ON courses
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

• BEFORE UPDATE: The trigger fires before the update is actually written to disk, so we can modify NEW.updated_at in time.
• FOR EACH ROW: The trigger fires once per row that is updated.
• NEW: Within a BEFORE trigger, NEW contains the new values that are about to be inserted/updated. We modify NEW.updated_at to the current time.
• RETURN NEW: We return the modified NEW row, so the update proceeds with the updated timestamp.

Result: Every time the courses table is updated, the updated_at column is automatically set to the current time, with no manual intervention required in the application.

3.3 Audit Trigger Example

This example demonstrates how to create a comprehensive audit trail by logging all INSERT, UPDATE, and DELETE operations on the students table, including the username, timestamp, and the old and new data values.

Audit table:

CREATE TABLE student_audit (
  audit_id BIGSERIAL PRIMARY KEY,
  action TEXT NOT NULL,
  changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  db_user TEXT NOT NULL,
  old_data JSONB,
  new_data JSONB
);

Trigger function:

CREATE OR REPLACE FUNCTION audit_students_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO student_audit(action, db_user, new_data)
    VALUES ('INSERT', CURRENT_USER, to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO student_audit(action, db_user, old_data, new_data)
    VALUES ('UPDATE', CURRENT_USER, to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO student_audit(action, db_user, old_data)
    VALUES ('DELETE', CURRENT_USER, to_jsonb(OLD));
    RETURN OLD;
  END IF;

  RETURN NULL;
END;
$$;

Attach trigger to table students:

CREATE TRIGGER trg_students_audit
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION audit_students_changes();

• AFTER INSERT OR UPDATE OR DELETE: The trigger fires after the change is committed to the table (not before), so we are recording the final state.
• TG_OP: A special PostgreSQL variable that tells us which operation triggered the function ('INSERT', 'UPDATE', or 'DELETE').
• OLD and NEW: In AFTER triggers (and UPDATE operations), OLD contains the previous row, NEW contains the updated row.
• to_jsonb(): Converts the entire row to JSON, which is flexible storage for audit purposes.
• CURRENT_USER: A PostgreSQL function that returns the username of the database user who made the change.
• RETURN NULL: For AFTER triggers, the return value is ignored for INSERT/UPDATE; returning NULL is convention.

Why this matters:

This audit table now contains a complete history of who changed what and when. Such records are often required for compliance (GDPR, SOX, financial regulations) or for debugging unexpected data changes. You can query the audit log to answer questions like “Who deleted customer #42 and when?” or “What was the grade before the correction?“

3.4 BEFORE INSERT Trigger: Enforce Business Constraint

This example shows how to enforce a business rule: A student may not enroll in more than 5 courses per semester.

Assume these tables:

CREATE TABLE semesters (
  semester_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,        -- e.g., "WS 2025/26", "SS 2026"
  start_date DATE NOT NULL,
  end_date DATE NOT NULL
);

CREATE TABLE students (
  student_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE courses (
  course_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  semester_id INT NOT NULL REFERENCES semesters(semester_id)
);

CREATE TABLE enrollments (
  enrollment_id SERIAL PRIMARY KEY,
  student_id INT NOT NULL REFERENCES students(student_id),
  course_id INT NOT NULL REFERENCES courses(course_id),
  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Trigger function to enforce the constraint:

CREATE OR REPLACE FUNCTION check_enrollment_limit()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_courses_count INT;
  v_semester_id INT;
BEGIN
  -- Get the semester of the course being enrolled in
  SELECT semester_id INTO v_semester_id
  FROM courses
  WHERE course_id = NEW.course_id;

  -- Count how many courses this student is already enrolled in this semester
  SELECT COUNT(*) INTO v_courses_count
  FROM enrollments e
  JOIN courses c ON e.course_id = c.course_id
  WHERE e.student_id = NEW.student_id
    AND c.semester_id = v_semester_id;

  -- Enforce the limit
  IF v_courses_count >= 5 THEN
    RAISE EXCEPTION 'Student % cannot enroll in more than 5 courses per semester', NEW.student_id;
  END IF;

  RETURN NEW;
END;
$$;

Explanation of check_enrollment_limit():

• DECLARE: Two local variables:

  • v_courses_count: Will hold the count of courses the student is already enrolled in
  • v_semester_id: Will hold the semester ID of the course being enrolled in

• NEW: A special PostgreSQL trigger variable that contains the row data about to be inserted. NEW.course_id is the course ID from the new enrollment record, and NEW.student_id is the student ID.

• SELECT … INTO: Queries the courses table to find which semester this course belongs to, storing the result in v_semester_id.

• JOIN and WHERE logic: Counts how many enrollments this student already has in this semester by joining enrollments and courses and filtering by student_id and semester_id.

• IF … RAISE EXCEPTION: If the count is 5 or more, raises an exception with the student ID. This exception cancels the INSERT operation before it reaches the database.

• RETURN NEW: If the constraint check passes (count < 5), return NEW unchanged, allowing the INSERT to proceed.

Trigger definition:

CREATE TRIGGER trg_check_enrollment_limit
BEFORE INSERT ON enrollments
FOR EACH ROW
EXECUTE FUNCTION check_enrollment_limit();

How it works:

1. When a student tries to enroll in a course, the trigger fires before the INSERT happens.
2. The trigger queries how many courses the student is already enrolled in for that semester.
3. If the count is 5 or more, the trigger raises an exception, preventing the enrollment.
4. If the count is less than 5, the enrollment is allowed to proceed.

Testing:

-- Student 1 enrolls in 5 courses of semester 1
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);  -- OK
INSERT INTO enrollments (student_id, course_id) VALUES (1, 2);  -- OK
INSERT INTO enrollments (student_id, course_id) VALUES (1, 3);  -- OK
INSERT INTO enrollments (student_id, course_id) VALUES (1, 4);  -- OK
INSERT INTO enrollments (student_id, course_id) VALUES (1, 5);  -- OK

-- Attempt to enroll in a 6th course
INSERT INTO enrollments (student_id, course_id) VALUES (1, 6);
-- ERROR: Student 1 cannot enroll in more than 5 courses per semester

This is a classic example of a business constraint from an ER diagram that cannot be expressed with simple foreign keys or CHECK constraints, but is easily enforced at the database level using a trigger.

3.5 AFTER INSERT Trigger: Keep Order Totals in Sync

This example demonstrates a typical AFTER trigger use case: after inserting an order item, the order header is updated with the new total amount.

Assume these tables:

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  total_amount NUMERIC(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE order_items (
  order_item_id SERIAL PRIMARY KEY,
  order_id INT NOT NULL REFERENCES orders(order_id),
  product_id INT NOT NULL,
  quantity INT NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(12,2) NOT NULL CHECK (unit_price >= 0)
);

Trigger function:

CREATE OR REPLACE FUNCTION update_order_total_after_item_insert()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE orders
  SET total_amount = total_amount + (NEW.quantity * NEW.unit_price)
  WHERE order_id = NEW.order_id;

  RETURN NULL;
END;
$$;

Trigger definition:

CREATE TRIGGER trg_order_items_after_insert
AFTER INSERT ON order_items
FOR EACH ROW
EXECUTE FUNCTION update_order_total_after_item_insert();

How it works:

1. A new line is inserted into order_items.
2. The AFTER trigger fires once for that row.
3. The function reads NEW.quantity, NEW.unit_price, and NEW.order_id.
4. It updates orders.total_amount by adding the item subtotal.

BEFORE vs AFTER: Rule of Thumb

• Use BEFORE triggers to validate or modify data before it is written.
• Use AFTER triggers to react to successful writes (e.g., logging, summaries, notifications).

4. Functions vs Procedures vs Triggers

Feature	Function	Procedure	Trigger
How to start	SELECT my_function(...)	CALL my_procedure(...)	Automatically on table event
Must return a value	Yes	No	Returns TRIGGER value (NEW/OLD)
Typical use	Calculations, reusable query logic	Multi-step operations, workflows	Automatic reactions to data changes
Directly callable by app	Yes	Yes	No (indirect via DML event)

5. Security and Maintenance Guidelines

1. Use least privilege: Grant EXECUTE on approved routines, not broad table rights.
2. Schema-qualify object names: Use public.students instead of students in production code.
3. Keep trigger logic short: Heavy trigger code can slow down writes.
4. Document side effects: Every trigger should be visible in project documentation.
5. Test with transactions and concurrency: Especially for financial or inventory operations.
6. Version routines in migrations: Never edit production objects manually without migration scripts.

Good architecture pattern

A common pattern is to allow applications to call carefully designed procedures/functions while direct table access is restricted. This creates a stable API at the database layer.

Summary

Database-side programming in PostgreSQL is a powerful way to keep business rules close to the data. Functions are ideal for reusable calculations and query logic, procedures are useful for controlled multi-step operations, and triggers automate reactions to data changes. Used carefully, these mechanisms improve consistency, security, and maintainability in real-world database systems.

Beyond these functions, stored procedures and triggers, there are additional database-side options you should know:

1. Views and Materialized Views (see chapter: Views detailed)
2. Constraints and Domains: They define and enforce valid data structures and business rules directly at the schema level.
3. Row Level Security (RLS) Policies: They control which rows a user is allowed to read or modify, based on role-specific conditions.
4. Event Triggers and Extensions: Event triggers react to DDL events, while extensions add reusable database features such as types, functions, or modules.