结合你遇到的PostgresException: 42703: column "AcceptanceActID" of relation "AcceptanceActLines" does not exist错误，以及提供的存储过程代码，我梳理出几个核心问题和对应的解决方案：

1. 列名大小写匹配问题 #

PostgreSQL的大小写规则很明确：用双引号包裹的标识符（表名、列名）严格区分大小写，未包裹的标识符会自动转为小写。

你在插入billing."AcceptanceActLines"时写了：

INSERT INTO billing."AcceptanceActLines"("PackageId","Amount","AcceptanceActID")

这里的"AcceptanceActID"是双引号包裹的，你需要先确认billing."AcceptanceActLines"表中确实存在完全大小写一致的列名。可以执行以下查询验证：

SELECT column_name 
FROM information_schema.columns 
WHERE table_schema = 'billing' 
  AND table_name = 'AcceptanceActLines';

如果查询结果里的列名是小写的acceptanceactid（比如创建表时没加双引号），那么你需要修改INSERT语句的列名：要么去掉双引号写acceptanceactid，要么用双引号包裹小写列名"acceptanceactid"。

2. 参数引用错误（混淆参数与列名） #

你的存储过程参数是month integer, year integer，但在SQL语句中你用双引号包裹了这些参数（比如"month"、"year"），这会让PostgreSQL把它们当成列名而非传入的参数，这不仅会导致逻辑错误，还可能触发列不存在的报错。

比如这段代码：

INSERT INTO billing."AcceptanceActs"("AccountID", "Month", "Year","Amount") 
SELECT DISTINCT th."AccountID", "month", "year", th."TotalAmount" 
FROM billing."TransactionHeaders" AS th 
WHERE th."AcceptanceActID" IS NULL;

这里的"month"和"year"会被解析为列名，而非你传入的参数，正确的写法应该是去掉双引号：

INSERT INTO billing."AcceptanceActs"("AccountID", "Month", "Year","Amount") 
SELECT DISTINCT th."AccountID", month, year, th."TotalAmount" 
FROM billing."TransactionHeaders" AS th 
WHERE th."AcceptanceActID" IS NULL;

3. 错误的等于运算符 #

PostgreSQL中判断相等用单个=，而非==，你在这段代码里用了错误的运算符：

WHERE aa."Month"=="month" AND aa."Year"=="year"

需要修改为：

WHERE aa."Month" = month AND aa."Year" = year

修正后的完整存储过程 #

结合以上所有修正，最终的存储过程代码如下：

create or replace function "ClosePeriod"(month integer, year integer) 
returns void language plpgsql as $$ 
DECLARE 
BEGIN 
    -- 插入AcceptanceActs记录（修正参数引用）
    INSERT INTO billing."AcceptanceActs"("AccountID", "Month", "Year","Amount") 
    SELECT DISTINCT th."AccountID", month, year, th."TotalAmount" 
    FROM billing."TransactionHeaders" AS th 
    WHERE th."AcceptanceActID" IS NULL;

    -- 更新TransactionHeaders的AcceptanceActID（修正参数引用）
    UPDATE billing."TransactionHeaders" 
    set "AcceptanceActID" = acc."ID" 
    from billing."AcceptanceActs" acc 
    where acc."Year" = year 
      AND acc."Month" = month 
      AND acc."AccountID" = billing."TransactionHeaders"."AccountID" 
      AND billing."TransactionHeaders"."AcceptanceActID" is null;

    -- 插入AcceptanceActLines记录（修正运算符+参数引用，同时确认列名大小写）
    INSERT INTO billing."AcceptanceActLines"("PackageId","Amount","AcceptanceActID") 
    SELECT tl."PackageID", SUM(tl."Amount"), aa."ID" 
    FROM billing."TransactionHeaders" AS th 
    INNER JOIN billing."TransactionLines" AS tl ON th."Id" = tl."TransactionHeaderID" 
    INNER JOIN billing."AcceptanceActs" as aa ON th."AcceptanceActID" = aa."ID" 
    WHERE aa."Month" = month 
      AND aa."Year" = year 
    GROUP BY tl."PackageID", aa."ID";
END; 
$$; 

alter function "ClosePeriod"(integer, integer) owner to postgres;

执行这段修正后的代码后，再调用存储过程应该就能解决你遇到的问题了。

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