从MSSQL迁移PostgreSQL：创建含临时表的存储过程失败

错误原因 #

使用PL/pgSQL编写存储过程时，所有SQL执行语句必须包裹在BEGIN...END代码块内，直接在$BODY$下写CREATE TABLE这类SQL会触发语法错误，这是PL/pgSQL的语法规范要求。

修正后的存储过程代码 #

CREATE OR REPLACE PROCEDURE public.temp()
LANGUAGE plpgsql
AS $BODY$
BEGIN
    -- 创建临时表，添加ON COMMIT DROP可让临时表在事务提交后自动删除
    CREATE TEMPORARY TABLE temp_table
    (
        temp_id integer
    ) ON COMMIT DROP;

    WITH rows AS (
        INSERT INTO table_a (some_name_a)
        SELECT some_name_b FROM table_b
        RETURNING id
    )
    INSERT INTO temp_table(temp_id)
    SELECT id FROM rows;
END;
$BODY$;

额外优化建议 #

如果后续操作在同一个存储过程内完成，无需单独创建临时表，可直接用数组变量存储插入的ID，减少资源开销：

CREATE OR REPLACE PROCEDURE public.temp()
LANGUAGE plpgsql
AS $BODY$
DECLARE
    inserted_ids integer[];
BEGIN
    WITH rows AS (
        INSERT INTO table_a (some_name_a)
        SELECT some_name_b FROM table_b
        RETURNING id
    )
    SELECT array_agg(id) INTO inserted_ids FROM rows;

    -- 示例：用存储的ID执行后续操作
    -- INSERT INTO other_table (related_id) SELECT unnest(inserted_ids);
END;
$BODY$;

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