Trying to start work following function, with SELECT in LOOP statement for other table records:
CREATE OR REPLACE FUNCTION parts."clearExpiredReserves"()
RETURNS void AS
$BODY$DECLARE
reserved_parts CURSOR FOR SELECT id, part_id, quantity FROM parts.operations_reg WHERE operation_type = 300
AND operation_date < CURRENT_TIMESTAMP - interval '72 hours';
spareparts_qty INT;
BEGIN
FOR reserve_record IN reserved_parts LOOP
SELECT p.quantity FROM parts.spareparts p WHERE p.id = reserve_record.part_id INTO spareparts_qty;
IF spareparts_qty = reserved_parts.quantity THEN
UPDATE parts.spareparts SET is_reserved = FALSE;
END IF;
DELETE FROM parts.operations_reg WHERE id = reserved_parts.id;
END LOOP;
END;$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
And I have the error:
ERROR: missing FROM-clause entry for table "reserved_parts"
LINE 1: SELECT spareparts_qty = reserved_parts.quantity
^
QUERY: SELECT spareparts_qty = reserved_parts.quantity
CONTEXT: PL/pgSQL function "clearExpiredReserves" line 10 at IF
Where I was wrong?
Thanks.
spareparts_qty = reserved_parts.quantity
-->reserved_parts
is the cursor name, you must use a record namereserve_record.quantity
, not the cursor. – kordirko yesterday