如何调试PostgreSQL触发错误"query has no destination for result data"？

Question

我有三个表，如下所示：

data_buku表

+----+----------+----------------+
| kode_buku  |  *  | *  |  stock |
+----+----------+----------------+
| 111        | *   | *  |  50    |
| 222        | *   | *  |  50    |
| 333        | *   | *  |  50    |
| 444        | *   | *  |  50    |
| 555        | *   | *  |  50    |
| 666        | *   | *  |  50    |
+----+-------+-----+----+--------+

数据堵塞表

+---------------+----------------------------+
| no_transaksi  |  kode_buku  | *  |  jumlah |
+---------------+-------------+----+---------+
| 1             | 111         | *  |   3     |
| 1             | 222         | *  |   2     |
| 1             | 333         | *  |   4     |
+---------------+-------------+----+---------+

数据_肯巴利表

+---------------+-----+----+---------+
| no_transaksi  |  *  | *  |  status |
+---------------+-----+----+---------+
| 1             | *   | *  |   back  |
+---------------+-----+----+---------+

在我的Tables中，我在表data_kembali上创建了一个函数和触发器。在将查询插入到表data_kembali时，函数将执行对表data_pinjam的jumlah求和的操作，其中表data_kembali中的no_transaksi与data_pinjam相同，并将更新表data_buku中的库存。每行具有相同的kode_buku值。库存+Jumlah。

我有创建功能

CREATE OR REPLACE FUNCTION kembali()
RETURNS TRIGGER AS
$BODY$
DECLARE
CURRENT_STOK INT4;
r data_pinjam%ROWTYPE;
BEGIN
FOR r IN
SELECT *
FROM   data_pinjam p
WHERE  p.no_transaksi = new.no_transaksi
LOOP
select CURRENT_STOK stock from data_buku where kode_buku = r.kode_buku;
CURRENT_STOK = CURRENT_STOK + r.jumlah;
UPDATE data_buku SET STOCK = CURRENT_STOK WHERE kode_buku = r.kode_buku;   
END LOOP;
UPDATE data_pinjam SET status = 'kembali' WHERE no_transaksi = new.no_transaksi;
update data_transaksi set status = 'kembali' where no_transaksi = new.no_transaksi;
RETURN NEW;
END;
$BODY$
LANGUAGE PLPGSQL VOLATILE
COST 100;

但在运行时，获取输出

ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function kembali() line 13 at SQL statement

有人能告诉我关于这个触发器和循环更新功能吗？

Answer 1

光标循环中的SELECT语句需要所选值的目的地：

SELECT stock
INTO CURRENT_STOK
FROM data_buku 
WHERE kode_buku = r.kode_buku;

还需要声明变量CURRENT_STOK(在BEGIN之前)。

但也许循环光标的意图是这样的？

UPDATE data_buku
SET STOCK = STOCK + r.jumlah
WHERE kode_buku = r.kode_buku;