PostgreSQL中如何将函数/表作为存储过程参数并实现数据插入

1. 在PostgreSQL中将函数作为存储过程的参数使用 #

你原有的存储过程写法不符合语法规范，不能直接将SELECT * FROM RetrieveRebates(customerID)作为参数传入。正确的做法是让存储过程接收函数所需的基础输入参数（比如customerID INT），再在存储过程内部调用目标函数。

示例1：SQL语言存储过程（直接批量插入） #

CREATE PROCEDURE InsertData(customerID INT)
LANGUAGE SQL
BEGIN ATOMIC
    -- 直接调用函数并将结果批量插入目标表
    INSERT INTO table3 (customer, reb, prod)
    SELECT customer, reb, prod FROM RetrieveRebates(customerID);
END;

示例2：PL/pgSQL存储过程（逐行处理场景） #

如果需要对函数返回的每一行做额外逻辑处理，可以用PL/pgSQL实现：

CREATE PROCEDURE InsertData(customerID INT)
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT * FROM RetrieveRebates(customerID) LOOP
        -- 此处可添加单行数据的自定义处理逻辑
        INSERT INTO table3 VALUES (rec.customer, rec.reb, rec.prod);
    END LOOP;
END;
$$;

2. 将表作为存储过程的参数并插入到另一个表 #

PostgreSQL支持通过自定义复合类型或直接声明TABLE类型参数来传递表结构数据，以下是两种实现方式：

方式1：基于自定义复合类型 #

步骤1：定义与目标表结构匹配的复合类型

CREATE TYPE rebate_record AS (
    customer INT,
    reb NUMERIC,
    prod INT
);

步骤2：创建接收TABLE类型参数的存储过程

CREATE PROCEDURE InsertFromTableParam(IN input_table TABLE(rebate_record))
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO table3 (customer, reb, prod)
    SELECT customer, reb, prod FROM input_table;
END;
$$;

调用示例

直接传入函数返回结果作为参数：

CALL InsertFromTableParam(RetrieveRebates(123));

方式2：直接声明TABLE参数结构 #

无需预先定义复合类型，直接在存储过程参数中指定表结构：

CREATE PROCEDURE InsertFromTableParam(IN input_table TABLE(customer INT, reb NUMERIC, prod INT))
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO table3 SELECT * FROM input_table;
END;
$$;

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