PostgreSQL存储过程中如何为CREATE VIEW传递参数？

环境信息 #

PostgreSQL版本：PostgreSQL 14.8 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r10) 12.2.1 20220924, 64-bit

问题描述 #

尝试创建接收两个参数的SQL语言存储过程，用于生成带参数过滤条件的临时视图。参考官方文档示例，INSERT语句可直接使用存储过程参数，但自身编写的CREATE VIEW语句无法识别参数，执行时抛出"column 'a' does not exist"错误。

问题代码 #

存储过程及调用逻辑：

CREATE OR REPLACE PROCEDURE get_total_and_count_for_car_for_service (a int, b int)
LANGUAGE SQL
AS $$
    CREATE OR REPLACE TEMPORARY VIEW total_and_count_for_car_for_service_result AS (
        SELECT
            SUM(
                CASE WHEN cars.is_foreign THEN
                    services.cost_foreign
                ELSE
                    services.cost_our
                END) AS total,
            COUNT(works.id)
        FROM
            works
        LEFT JOIN cars ON cars.id = works.car_id
        LEFT JOIN services ON services.id = works.service_id
    WHERE
        works.service_id = a
        AND works.car_id = b
    GROUP BY
        works.car_id)
$$;

CALL get_total_and_count_for_car_for_service (2, 2);
SELECT * FROM total_and_count_for_car_for_service_result;

报错信息 #

car_service=# CALL get_total_and_count_for_car_for_service ( 2, 2);
ERROR:  column "a" does not exist
LINE 1: ...es.id = works.service_id WHERE works.service_id = a AND work...
                                                             ^
QUERY:   CREATE OR REPLACE TEMPORARY VIEW total_and_count_for_car_for_service_result AS (SELECT SUM(CASE WHEN cars.is_foreign THEN services.cost_foreign ELSE services.cost_our END) AS total, COUNT(works.id) FROM works LEFT JOIN cars ON cars.id = works.car_id LEFT JOIN services ON services.id = works.service_id WHERE works.service_id = a AND works.car_id = b GROUP BY works.car_id) 
CONTEXT:  SQL function "get_total_and_count_for_car_for_service" statement 1

问题根因 #

在SQL语言的存储过程中，CREATE VIEW属于DDL操作，PostgreSQL会将视图的定义文本原样存储，不会在创建阶段解析并替换存储过程的参数。而INSERT这类DML语句在执行时会直接绑定存储过程的参数，因此可以正常工作。当后续查询视图时，PostgreSQL会尝试在当前上下文中查找名为a、b的列，自然无法找到，从而报错。

解决方案 #

方案1：改用PL/pgSQL语言编写存储过程 #

PL/pgSQL支持动态SQL，通过EXECUTE结合format函数可以安全地拼接参数，动态生成视图：

CREATE OR REPLACE PROCEDURE get_total_and_count_for_car_for_service (a int, b int)
LANGUAGE plpgsql
AS $$
BEGIN
    EXECUTE format('
        CREATE OR REPLACE TEMPORARY VIEW total_and_count_for_car_for_service_result AS (
            SELECT
                SUM(
                    CASE WHEN cars.is_foreign THEN
                        services.cost_foreign
                    ELSE
                        services.cost_our
                    END) AS total,
                COUNT(works.id)
            FROM
                works
            LEFT JOIN cars ON cars.id = works.car_id
            LEFT JOIN services ON services.id = works.service_id
            WHERE
                works.service_id = %L
                AND works.car_id = %L
            GROUP BY
                works.car_id
        )', a, b);
END;
$$;

format函数的%L占位符会自动对参数进行转义，避免SQL注入风险。

方案2：调整逻辑（非目标需求适配，仅作思路参考） #

如果坚持使用SQL语言存储过程，可以先创建基础视图，再通过存储过程过滤参数：

-- 创建不带筛选条件的基础临时视图
CREATE OR REPLACE TEMPORARY VIEW total_and_count_for_car_for_service_base AS (
    SELECT
        works.service_id,
        works.car_id,
        SUM(
            CASE WHEN cars.is_foreign THEN
                services.cost_foreign
            ELSE
                services.cost_our
            END) AS total,
        COUNT(works.id) AS count
    FROM
        works
    LEFT JOIN cars ON cars.id = works.car_id
    LEFT JOIN services ON services.id = works.service_id
    GROUP BY
        works.service_id, works.car_id
);

-- 存储过程直接查询过滤
CREATE OR REPLACE PROCEDURE get_total_and_count_for_car_for_service (a int, b int)
LANGUAGE SQL
AS $$
    SELECT total, count FROM total_and_count_for_car_for_service_base WHERE service_id = a AND car_id = b;
$$;

此方案未实现“通过存储过程创建带参数视图”的需求，仅为替代思路。

补充说明 #

尽管函数更适合此类查询场景，但如果必须通过存储过程创建带参数过滤的临时视图，PL/pgSQL动态SQL是最优解。

内容的提问来源于stack exchange，提问作者Quakumei