CODEWITHSUNDEEP
postgresqlplpgsql
ThP
ThP

Reputation: 185

ERROR: missing FROM-clause entry for table when running function

I want to create a trigger and a function that do the following: Each time a new row is inserted in table SalesOrderDetail the function finds the corresponding CustomerID from table SalesOrderHeader and then it adds +1 to the corresponding number_of_sales in the Customer table.

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   |
+---------+--------------------+

Code is as follows:

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();

When I insert something into the SalesOrderDetail table I get the following error:

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

What I am doing wrong? Sorry for the poor explanation English is not my native language.

Upvotes: 2

Views: 3426

Answers (1)

Varshney P.
Varshney P.

Reputation: 248

Firstly you need to take care about the case-sensitive pgsql. If you do not explicitly use "", it converts all to lower case.

Next, your procedure should be somewhat like this:

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

Upvotes: 0

Related Questions