#Views, functions and procedures - MySQL and PostgreSQL implementation comparison

#Introduction

When your application needs to process complex data logic, if you write a bunch of code directlyJOIN、GROUP BYand conditional judgment, the code will become more and more bloated and difficult to maintain and reuse. In order to cope with this situation, mainstream relational-databases provide a series of "database programming" tools - views, stored functions, stored procedures and triggers. They can encapsulate common queries, calculations, and business logic directly inside the database, making the application layer lighter and the data more secure.

This article will use actual cases to explain step by step the creation and use of these objects, and compare the key differences in implementation between MySQL and PostgreSQL to help you make the appropriate choice based on the actual scenario.

#1. Views - save common queries as a "virtual table"

#1.1 What is a view?

A view can be understood as a saved query statement. It looks like a table and you can doSELECTQuery and even modify the data in some cases, but it does not actually store the data. Each time it is accessed, the database dynamically executes the SQL statement that defines the view and returns the latest results.

Advantages of using views:

• Simplify complex queries: Encapsulate multi-table associations, aggregate statistics, etc., and use them directly laterSELECT * FROM 视图名That’s it.
• Improve security: You can only expose certain columns to users and hide sensitive fields (such as ID number, salary, etc.).
• Logical layering: The application code only relies on views. When the underlying table structure is adjusted, as long as the view is modified, the upper layer calls do not need to change.

#1.2 Create views in MySQL

MySQL's view syntax is very intuitive. Here are a few common uses.

-- 1. 简单视图：只显示在职员工，隐藏了一些字段
CREATE VIEW active_employees AS
SELECT employee_id, first_name, last_name, email, salary
FROM employees
WHERE status = 'active';

-- 2. 复杂视图：部门信息汇总（多表关联 + 聚合）
CREATE VIEW department_summary AS
SELECT 
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) AS employee_count,
    AVG(e.salary) AS avg_salary,
    MIN(e.hire_date) AS earliest_hire,
    MAX(e.salary) AS max_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- 3. 带检查选项的视图：确保通过视图插入/更新的数据符合视图的 WHERE 条件
CREATE VIEW high_earners AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 75000
WITH CHECK OPTION;  -- 如果通过这个视图修改数据，数据库会校验是否满足薪资 > 75000

Version Difference Note: MySQL 5.7 and previous versions cannot include subqueries when creating views.LIMIT, 8.0 has relaxed restrictions.

#1.3 Create views in PostgreSQL

The basic view syntax of PostgreSQL is almost the same as that of MySQL, and the learning cost is very low.

-- 基础视图：与 MySQL 写法相同
CREATE VIEW active_employees AS
SELECT employee_id, first_name, last_name, email, salary
FROM employees
WHERE status = 'active';

-- 带检查选项的视图（同样支持）
CREATE VIEW high_earners AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 75000
WITH CHECK OPTION;

Features of PostgreSQL: Materialized Views Normal views re-execute the definition SQL every time they are queried, and when the view involves complex aggregations of large amounts of data, performance may not be ideal. PostgreSQL provides materialized views, which will store the results to disk and read them directly during queries, which is very fast.

-- 创建物化视图：按月汇总销售额
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT 
    DATE_TRUNC('month', sale_date) AS month,
    SUM(amount) AS total_sales,
    COUNT(*) AS transaction_count
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);

-- 当基表数据有变化时，需要手动刷新物化视图
REFRESH MATERIALIZED VIEW monthly_sales_summary;

-- 高并发场景下，可使用 CONCURRENTLY 在刷新时不锁定视图
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

MySQL currently does not support materialized views. If MySQL users have similar needs, they can usually only create an ordinary table and then use it regularly with a scheduled task (Event Scheduler).INSERT ... SELECTto update.

#1.4 Best Practice Examples of Views

-- 利用视图做安全控制：只暴露员工公开信息
CREATE VIEW employee_public_view AS
SELECT 
    employee_id,
    first_name,
    last_name,
    department_id,
    hire_date
FROM employees
WHERE status = 'active';       -- 只显示在职人员，且不包含薪资等敏感列

-- 利用视图汇总客户消费情况
CREATE VIEW customer_order_summary AS
SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_amount) AS total_spent,
    MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

With these views, business code only needs toSELECT * FROM customer_order_summary WHERE customer_id = 123, no need to write complex association and aggregation statements multiple times.

#2. Stored functions (Functions) - reusable computing building blocks

#2.1 What is a stored function?

A stored function is calculation logic encapsulated in the database. It receives input parameters and performs a series of operations before returning a clear value** (which can be a number, a string, or even a table). You can call functions directly in SQL statements, just like using built-in functionsUPPER()、COUNT()Same.

Functions are very suitable for encapsulating frequently occurring calculation logic, such as calculating bonuses based on sales, formatting mobile phone numbers, or returning a certain business object.

#2.2 Stored functions in MySQL

MySQL creation function needs to change the statement delimiter (DELIMITER), then useBEGIN...ENDBlock writing logic.

DELIMITER //

CREATE FUNCTION calculate_bonus(salary DECIMAL(10,2), bonus_rate DECIMAL(3,2))
RETURNS DECIMAL(10,2)
READS SQL DATA            -- 声明函数特性：只读取数据，不修改
DETERMINISTIC             -- 声明相同输入永远返回相同输出（有助于优化）
BEGIN
    DECLARE bonus_amount DECIMAL(10,2);
    SET bonus_amount = salary * bonus_rate;
    RETURN bonus_amount;
END //

DELIMITER ;

-- 像内置函数一样在查询里使用
SELECT employee_id, first_name, last_name, 
       salary, 
       calculate_bonus(salary, 0.1) AS bonus
FROM employees;

Note: The MySQL function declaration must clearly indicate its characteristics (such asDETERMINISTIC、READS SQL DATAetc.), otherwise an error may be reported under certain circumstances.

#2.3 Stored functions in PostgreSQL

PostgreSQL's functions are more flexible, support multiple languages ​​(default PL/pgSQL), and functions that directly return table types are particularly useful in data analysis.

-- 1. 简单计算函数（和 MySQL 非常像）
CREATE OR REPLACE FUNCTION calculate_bonus(salary NUMERIC, bonus_rate NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN salary * bonus_rate;
END;
$$ LANGUAGE plpgsql;

-- 查询中使用
SELECT employee_id, first_name, last_name, 
       salary, 
       calculate_bonus(salary, 0.1) AS bonus
FROM employees;

-- 2. 返回“表”的函数：直接把一段查询结果当表返回
CREATE OR REPLACE FUNCTION get_high_earners(min_salary NUMERIC)
RETURNS TABLE (
    employee_id INTEGER,
    first_name TEXT,
    last_name TEXT,
    salary NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT e.employee_id, e.first_name, e.last_name, e.salary
    FROM employees e
    WHERE e.salary >= min_salary
    ORDER BY e.salary DESC;
END;
$$ LANGUAGE plpgsql;

-- 像查询普通表一样调用
SELECT * FROM get_high_earners(80000);

This function that returns a table type is very suitable for replacing complex views or implementing dynamic reports with parameters.

#3. Stored Procedures - "scripts" for processing complete business transactions

#3.1 What is a stored procedure?

Stored procedures are very similar to functions, but have several important differences:

• A procedure does not necessarily have a return value, but a function must have a return value;
• Processes can Exercise Transaction Control (COMMIT / ROLLBACK), functions usually do not work;
• Process passedCALLStatement execution cannot be done directly inSELECTcall inside.

Stored procedures are suitable for encapsulating a business operation that contains multiple steps, such as placing an order (deducting inventory + creating an order + writing a log), or updating certain data in batches.

#3.2 MySQL stored procedure

DELIMITER //

CREATE PROCEDURE GetEmployeeById(IN emp_id INT)
BEGIN
    SELECT employee_id, first_name, last_name, email, salary
    FROM employees
    WHERE employee_id = emp_id;
END //

DELIMITER ;

-- 调用存储过程
CALL GetEmployeeById(123);

#3.3 PostgreSQL stored procedures

PostgreSQL only natively supports stored procedures starting from version 11 (previously it could only be simulated through functions), which can perform transaction control.

CREATE OR REPLACE PROCEDURE update_employee_salary(
    emp_id INTEGER,
    new_salary NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    current_salary NUMERIC;
BEGIN
    -- 查询当前薪资
    SELECT salary INTO current_salary
    FROM employees
    WHERE employee_id = emp_id;
    
    IF current_salary IS NULL THEN
        RAISE NOTICE '员工编号 % 不存在', emp_id;
        RETURN;   -- 直接退出过程
    END IF;
    
    -- 更新薪资并记录修改时间
    UPDATE employees
    SET salary = new_salary,
        updated_at = NOW()
    WHERE employee_id = emp_id;
    
    RAISE NOTICE '薪资已从 % 更新为 %', current_salary, new_salary;
END;
$$;

-- 调用存储过程（和 MySQL 一样用 CALL）
CALL update_employee_salary(123, 75000);

#4. Triggers - "data guardians" that respond automatically

#4.1 MySQL triggers

MySQL triggers are directly bound to execution logic, and the syntax is relatively simple.

DELIMITER //

CREATE TRIGGER employee_audit_trigger
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (
        employee_id,
        old_salary,
        new_salary,
        change_date,
        changed_by
    ) VALUES (
        NEW.employee_id,
        OLD.salary,
        NEW.salary,
        NOW(),
        USER()               -- 当前数据库用户
    );
END //

DELIMITER ;

#4.2 PostgreSQL triggers

PostgreSQL separates trigger functions and triggers. You need to first define a returnTRIGGERtype function, and then create a trigger to call it. Although there is one more step, the advantage of this is that the same function can be reused by multiple triggers.

-- 先创建触发器函数
CREATE OR REPLACE FUNCTION audit_employee_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN   -- TG_OP 表示操作类型：INSERT, UPDATE, DELETE
        INSERT INTO employee_audit (
            employee_id,
            old_salary,
            new_salary,
            change_date,
            changed_by
        ) VALUES (
            NEW.employee_id,
            OLD.salary,
            NEW.salary,
            CURRENT_TIMESTAMP,
            CURRENT_USER
        );
        RETURN NEW;           -- 触发器函数必须返回 NEW 或 OLD
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 再创建触发器，绑定到对应表上
CREATE TRIGGER employee_audit_trigger
    AFTER UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION audit_employee_changes();

#5. Summary of implementation differences between MySQL and PostgreSQL

#Related tutorials

• 索引优化策略
• Python接入MySQL与PostgreSQL数据库
• SQL详解之DCL

#Summarize

Views, functions, stored procedures and triggers provide strong support for database-side programming:

• View: Provides data abstraction and security layer to make complex queries simple;
• Stored function: encapsulates calculation logic and can be embedded in SQL statements for repeated use;
• Stored Procedure: realizes complete business processes and supports transaction control;
• Trigger: Automatically complete auditing, verification and other operations to ensure data consistency.

In MySQL and PostgreSQL, these functions have mature implementations, but the design ideas and expansion capabilities are different. After understanding the comparison in this article, you can choose the most appropriate database object and database system according to the actual situation of the project, so that the development work can be done with half the effort.