postgresql - 我试图执行查询并给了我一个错误，我不明白为什么？

Question

DROP FUNCTION IF EXISTS top_5(customers.customerid%TYPE, products.prod_id%TYPE, orderlines.quantity%TYPE) CASCADE;

CREATE OR REPLACE FUNCTION top_5(c_id customers.customerid%TYPE, p_id products.prod_id%TYPE, quant orderlines.quantity%TYPE)

RETURNS orders.orderid%TYPE AS $$
DECLARE

top_prod CURSOR IS 

SELECT inv.prod_id 
  FROM inventory AS inv, products AS prod 
 WHERE inv.prod_id=prod.prod_id
 ORDER BY inv.quan_in_stock desc, inv.sales
limit 5;

ord_id orders.orderid%TYPE;
ord_date orders.orderdate%TYPE:= current_date;
ordln_id orderlines.orderlineid%TYPE:=1;

BEGIN
  SELECT nova_orderid() INTO ord_id;

  INSERT INTO orders(orderid, orderdate,customerid,netamount,tax,totalamount) VALUES(ord_id,ord_date,c_id,0,0,0);

  PERFORM compra(c_id, p_id, 1::smallint, ord_id, ordln_id, ord_date);

  IF (p_id  = top_prod) THEN 
    UPDATE orders 
       SET totalamount = totalamount - (totalamount*0.2)
     WHERE ord_id = (SELECT MAX(ord_id) FROM orders);
  END IF;
END;
$$ LANGUAGE plpgsql;

我有以下代码，当我尝试执行它时

SELECT top_5(1,1,'2');

我有这个错误

错误:运算符不存在:整数 = refcursor第 1 行:选择 (p_id = top_prod)

Answer 1

您需要从游标“top_prod”获取“prod_id”值。你不能比较两种类型。试试这个，

DECLARE
top_prod_id top_prod%ROWTYPE;

    BEGIN
    OPEN top_prod;

    LOOP
        FETCH top_prod INTO top_prod_id;
        EXIT WHEN top_prod %NOTFOUND;

        IF (p_id  = top_prod_id) THEN 
          UPDATE orders 
          SET totalamount = totalamount - (totalamount*0.2)
          WHERE ord_id = (SELECT MAX(ord_id) FROM orders);
        END IF;
    END LOOP;

    CLOSE top_prod;
END;