Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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.

share|improve this question
 
Here: spareparts_qty = reserved_parts.quantity --> reserved_parts is the cursor name, you must use a record name reserve_record.quantity, not the cursor. –  kordirko yesterday
add comment

1 Answer

up vote 0 down vote accepted

When referring to the current record in the FOR loop you need to use the variable reserve_record instead of the bound cursor var reserved_parts.

Eg.instead of

IF spareparts_qty = reserved_parts.quantity THEN

Use

IF spareparts_qty = reserved_record.quantity THEN
share|improve this answer
 
You're right. I Should be more careful and do not work at night :) –  Andrey Sapunov 17 hours ago
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.