Easy Steps to Understanding SQL

Key Takeaways

• Stored procedures package operations behind an explicit call; triggers run implicitly when their configured database events occur.
• Functions make reusable logic available inside queries, but their return types and permission to change data depend on the database engine.
• Ordinary views simplify queries without persisting their results. Materialized and indexed views can store results, with different maintenance rules.
• None of these objects automatically improves security or performance. Permissions, implementation, query plans, and workload determine the outcome.

Stored procedures, triggers, functions, and views extend basic SQL by giving reusable database operations a name and a defined interface. Used deliberately, they organize data access and reduce duplication; used indiscriminately, they add hidden behavior and deployment complexity.

The easiest way to understand them is to ask two questions: What causes this object to run, and how does its caller use the result?

SQL Database Objects at a Glance

Set Up the PostgreSQL Example

Run the examples in order in a disposable database, using an account allowed to create a schema and its objects. For simplicity, the optional permissions example below assumes you run it as a superuser; superuser access is sufficient, not strictly necessary.

CREATE SCHEMA sql_demo;

CREATE TABLE sql_demo.products (
    id integer PRIMARY KEY,
    name text NOT NULL,
    price numeric(10, 2) NOT NULL CHECK (price >= 0),
    private_cost numeric(10, 2) NOT NULL,
    updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO sql_demo.products (id, name, price, private_cost)
VALUES (1, 'Keyboard', 20.00, 12.00);

Stored Procedures: Explicit, Reusable Operations

A stored procedure packages database commands into a named operation. Applications call it explicitly rather than repeating its implementation wherever the operation is needed. In PostgreSQL, you invoke a procedure with CALL, not a function expression.

CREATE PROCEDURE sql_demo.set_price(p_id integer, p_price numeric)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE sql_demo.products
    SET price = p_price
    WHERE id = p_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Unknown product: %', p_id;
    END IF;
END;
$$;

CALL sql_demo.set_price(1, 25.00);

SELECT id, price FROM sql_demo.products;

This procedure changes the price and rejects an unknown product ID. The table's constraints reject a null or negative price. Its default PostgreSQL execution context uses the caller's privileges, so creating it does not automatically let callers bypass table permissions.

Performance and Security Are Design Outcomes

Putting several operations behind one call can reduce application-to-database round trips. It does not make equivalent SQL inherently faster: PostgreSQL's PL/pgSQL planning and caching behavior depends on execution and parameter values, not merely on storing code in the database. Measure representative inputs and concurrency, including the underlying query plans.

Likewise, procedures do not automatically prevent SQL injection. Bind application inputs as parameters, avoid concatenating untrusted values into dynamic SQL, and grant only required privileges. OWASP's SQL injection guidance explains why safe construction matters for both procedures and application queries.

The example leaves transaction boundaries to its caller. PostgreSQL procedures can commit or roll back only in allowed calling contexts; a CALL inside an explicit transaction block cannot perform that transaction control. Procedure result handling also varies by engine; PostgreSQL supports output parameters.

Triggers: Automatic Behavior With Hidden Costs

A trigger runs implicitly when a configured event occurs, such as an insert, update, or delete. Unlike a procedure, it doesn't require an explicit application call. That makes triggers useful for consistent database-side behavior, but harder to discover during maintenance and debugging.

In PostgreSQL, a trigger definition connects an event to a trigger function. This example updates a timestamp before each affected row is stored:

CREATE FUNCTION sql_demo.touch_product()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := statement_timestamp();
    RETURN NEW;
END;
$$;

CREATE TRIGGER products_touch
BEFORE UPDATE ON sql_demo.products
FOR EACH ROW
EXECUTE FUNCTION sql_demo.touch_product();

CALL sql_demo.set_price(1, 27.00);

SELECT id, price, updated_at FROM sql_demo.products;

The procedure's update now fires the trigger without mentioning it. NEW represents the proposed row, and statement_timestamp() supplies the current statement's start time.

Understand Timing, Scope, and Transactions

PostgreSQL's trigger definition rules distinguish these dimensions:

• Timing: BEFORE runs before the operation; AFTER runs afterward. INSTEAD OF triggers replace an operation on a view.
• Scope: A row-level trigger runs for each affected row. A statement-level trigger runs once for the statement, even when it affects no rows.
• Ordering: PostgreSQL runs triggers of the same kind for the same event alphabetically by name. That is not a portable SQL guarantee.

PostgreSQL triggers execute within the triggering transaction. An unhandled trigger error rolls back the triggering statement's changes and the trigger's database changes. An AFTER trigger is therefore not an after-commit background job; see the trigger behavior documentation.

Keep triggers small, document their dependencies, and avoid correctness that depends on a chain of trigger names. For downstream change distribution, evaluate change data capture rather than hiding an integration workflow inside triggers.

Views: Reusable Queries and Controlled Interfaces

An ordinary view gives a query a name. It can hide repetitive joins and calculations behind a simpler interface, helping application queries remain stable while you reorganize the underlying schema.

CREATE VIEW sql_demo.catalog AS
SELECT id, name, price
FROM sql_demo.products;

SELECT id, name, price
FROM sql_demo.catalog
WHERE price < 30.00;

This view exposes catalog fields without exposing private_cost. PostgreSQL's ordinary views store the query definition, not its results. Creating a view does not automatically make the query faster.

Ordinary, Updatable, and Persisted Views

• Ordinary view: Evaluates its defining query when referenced.
• Updatable view: Allows supported writes through the view. PostgreSQL automatically supports certain simple views; complex definitions have additional restrictions.
• Materialized view: PostgreSQL persists query results; REFRESH MATERIALIZED VIEW regenerates them. Results can become stale between refreshes.
• Indexed view: SQL Server can persist a view's results through indexing and maintain them as base data changes. This adds write work and has specific definition and configuration requirements.

Updatability and persistence are separate properties, not interchangeable labels. For persisted query results, decide how much staleness you can accept and who owns maintenance. The distinction between OLTP and OLAP workloads helps frame that decision.

Make the View an Actual Permission Boundary

A view only restricts access if the user's effective privileges support that restriction. PostgreSQL normally checks underlying table privileges using the view owner; a view configured with security_invoker = true instead requires the invoking user's underlying privileges. Ownership, row-security policies, and the definition all matter; review view security behavior before treating a filtered view as a security boundary.

For an isolated demonstration, run this optional block as a superuser after creating the table and view:

CREATE ROLE catalog_reader NOLOGIN;

REVOKE ALL ON sql_demo.products FROM PUBLIC;
GRANT USAGE ON SCHEMA sql_demo TO catalog_reader;
GRANT SELECT ON sql_demo.catalog TO catalog_reader;

SET ROLE catalog_reader;
SELECT id, name, price FROM sql_demo.catalog;
RESET ROLE;

With no additional grants or memberships, this new role can read the view but not the base table. In an existing system, audit inherited and column-level privileges too; removing a table grant alone is not a complete access review. PostgreSQL's privilege documentation explains the permission model.

Functions: Logic You Can Compose in Queries

A scalar function returns one value per invocation. Table-returning functions can instead expose rows and columns as a query source. Those return shapes make functions useful for composing reusable logic with filtering, joins, and other SQL expressions.

CREATE FUNCTION sql_demo.price_with_tax(p_price numeric, p_rate numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
RETURNS NULL ON NULL INPUT
AS $$
    SELECT round(p_price * (1 + p_rate), 2);
$$;

SELECT sql_demo.price_with_tax(25.00, 0.20) AS total;

SELECT name, sql_demo.price_with_tax(price, 0.20) AS total
FROM sql_demo.catalog;

The first call returns 30.00. Here, IMMUTABLE declares that the result depends only on the arguments and the function doesn't modify the database. RETURNS NULL ON NULL INPUT returns null without executing the body when either argument is null. These are documented PostgreSQL function attributes, not universal SQL syntax.

Stored Procedure vs Function: Avoid Universal Rules

Prefer a procedure for an explicit command and a function for query-composable logic. That is a useful design convention, not a portable prohibition on function side effects.

PostgreSQL SQL functions can execute data-changing statements when appropriately declared and permitted; functions with side effects must be classified VOLATILE. By contrast, SQL Server Transact-SQL user-defined functions cannot modify database state.

PostgreSQL functions cannot commit or roll back a transaction. Don't infer transaction control, output parameters, or side-effect rules from the word “function” or “procedure” alone.

Choosing Where Database Logic Belongs

Use database objects when they make an operation easier to reuse, govern, or keep consistent across database clients. Prefer declarative constraints for straightforward integrity rules, views for shared query interfaces, functions for reusable query logic, and procedures for explicit database commands.

Keep service orchestration and external integrations in application code unless you have a compelling reason otherwise. A small, documented database interface is usually easier to maintain than business logic scattered across application services and implicit trigger chains.

Production Checklist

• Version definitions: Store database objects and grants in reviewed migrations alongside application changes. Apply the same Git workflow discipline you use for application code.
• Test behavior: Cover missing records, null inputs, constraint failures, multirow changes, transaction rollback, and access denied under the actual application role.
• Review privileges: Separate object ownership from application access. Audit execution grants and any owner-privileged routines.
• Measure complete operations: Include trigger work, locks, query plans, and persisted-view maintenance rather than timing only the application's visible SQL.
• Make failures observable: Record useful operation context without sensitive values, document trigger dependencies, and plan deployment compatibility before changing interfaces.

FAQ

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

A procedure is usually an explicitly invoked command, while a function provides a result that can participate in a query. Functions can return scalar values or, where supported, rows and columns. Output handling, transaction control, and side-effect restrictions depend on the database engine.

Can a stored procedure be called from a SELECT statement?

In PostgreSQL, procedures are invoked with CALL and cannot be used as SELECT expressions. Use a function when you need a query-composable result. Other engines have their own procedure invocation and result-handling rules.

Do SQL views store data?

Ordinary views store a query definition rather than its results. PostgreSQL materialized views persist results that can become stale until refreshed, while SQL Server indexed views maintain stored results as underlying data changes.

Can SQL functions modify data?

It depends on the engine and function type. PostgreSQL permits data-changing SQL and PL/pgSQL functions with appropriate declarations and privileges, while SQL Server Transact-SQL user-defined functions cannot modify database state.

In what order do multiple triggers run?

Trigger ordering is database-specific, not a universal schema-defined sequence. PostgreSQL runs triggers of the same kind for the same event alphabetically by name. Prefer designs whose correctness does not depend on trigger order.

Further Reading

• PostgreSQL user-defined procedures — invocation, output parameters, and transaction differences.
• PostgreSQL trigger behavior — timing, transactions, and interacting triggers.
• PostgreSQL CREATE VIEW — ordinary views, updatability, and execution context.
• PostgreSQL materialized views — persisted results and refresh tradeoffs.
• PostgreSQL CREATE FUNCTION — return types, volatility, and security attributes.
• PostgreSQL SQL functions — scalar, table-returning, and data-changing functions.
• PostgreSQL privileges — ownership, grants, and default access.
• OWASP SQL Injection Prevention Cheat Sheet — parameterization and safe stored procedures.