postgresql - 错误 : missing FROM-clause entry for table when running function

Question

我想创建一个触发器和一个函数来执行以下操作:每次在表 SalesOrderDetail 中插入一个新行时，函数都会从表 SalesOrderHeader 中找到相应的 CustomerID，然后将 +1 添加到 Customer 表中相应的 number_of_sales .

SalesOrderDetail
+---------+-------------------------+
| SalesOrderID  | SalesOrderDetailID |
+---------+-------------------------+
|     value1    |       value4       |
|     value1    |       value5       |
|     value2    |       value6       |
|     value3    |       value7       |
|     value3    |       value8       |
|     value4    |       value9       |
+---------+-------------------------+


SalesOrderHeader
+---------+-----------------+
| SalesOrderID | CustomerID |
+---------+-----------------+
|   value1    |   value10   |
|   value2    |   value11   |
|   value3    |   value12   |
|   value4    |   value13   |
+---------+-----------------+



Customer
+---------+--------------------+
| CustomerID | Number_of_sales |
+---------+--------------------+
|   value10   |      value14   |
|   value11   |      value15   |
|   value12   |      value16   |
|   value13   |      value17   |
+---------+--------------------+

代码如下:

CREATE OR REPLACE FUNCTION new_order_detail()
RETURNS trigger AS
$BODY$
BEGIN
    DROP TABLE IF EXISTS CustomerInfo;
    CREATE TEMP TABLE CustomerInfo AS
        SELECT* FROM(SELECT CustomerID FROM(
    SELECT * from SalesOrderHeader
    WHERE  SalesOrderHeader.SalesOrderID = (SELECT SalesOrderID FROM SalesOrderDetail ORDER BY SalesOrderID DESC limit 1))AS Last_Entry) AS Common_Element;




    IF CustomerInfo.CustomerID = Customer.CustomerID THEN
    UPDATE Customer
    SET number_of_items = number_of_items + 1;
    END IF;



END;
$BODY$ LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS new_order ON SalesOrderDetail;

CREATE TRIGGER new_order
    AFTER INSERT OR UPDATE ON SalesOrderDetail
    FOR EACH ROW EXECUTE PROCEDURE new_order_detail();

当我向 SalesOrderDetail 表中插入内容时，出现以下错误:

PL/pgSQL function new_order_detail() line 3 at SQL statement ERROR: missing FROM-clause entry for table "customerinfo" LINE 1: SELECT CustomerInfo.CustomerID = Customer.CustomerID ^ QUERY: SELECT CustomerInfo.CustomerID = Customer.CustomerID CONTEXT: PL/pgSQL function new_order_detail() line 12 at IF ********** Error **********

ERROR: missing FROM-clause entry for table "customerinfo" SQL state: 42P01 Context: PL/pgSQL function new_order_detail() line 12 at IF

我做错了什么？抱歉解释不当英语不是我的母语。

Answer 1

首先你需要注意区分大小写的pgsql。如果您没有明确使用“”，它会将所有内容转换为小写。

接下来，您的过程应该有点像这样:

select CustomerID from SalesOrderHeader where SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID into custID;
UPDATE Customer SET number_of_items = number_of_items + 1 其中 CustomerID = custID ;