Sample Header Ad - 728x90

"ERROR: Cursor does not exist" in PL/pgSQL procedure

0 votes
1 answer
1083 views
I am trying to transfer a specific amount of film with an specific quantity from one store_id to another. For this I have tried doing a procedure: ~~~pgsql CREATE OR REPLACE PROCEDURE transfer_inventory( p_film_id INTEGER, p_from_store_id INTEGER, p_to_store_id INTEGER, p_quantity INTEGER ) LANGUAGE plpgsql AS $$ DECLARE v_inventory_id INTEGER; v_current_quantity INTEGER; v_transferred_quantity INTEGER := 0; v_remaining_quantity INTEGER; v_total_available_quantity INTEGER := 0; v_cursor CURSOR FOR SELECT inventory_id, COUNT(*) AS film_count FROM inventory WHERE film_id = p_film_id AND store_id = p_from_store_id GROUP BY inventory_id; BEGIN SELECT COALESCE(SUM(film_count), 0) INTO v_total_available_quantity FROM ( SELECT COUNT(*) AS film_count FROM inventory WHERE film_id = p_film_id AND store_id = p_from_store_id GROUP BY inventory_id ) AS temp; RAISE NOTICE 'Se desean transferir % unidades de la película %.', p_quantity, p_film_id; RAISE NOTICE 'Hay % unidades disponibles para transferir.', v_total_available_quantity; IF v_total_available_quantity = v_remaining_quantity THEN RAISE NOTICE 'Transferencia de % unidades de la película % a la tienda %.', v_remaining_quantity, p_film_id, p_to_store_id; EXIT WHEN v_transferred_quantity = p_quantity; ELSE -- No enough units to transfer RAISE NOTICE 'No hay suficientes películas para completar la transferencia.'; ROLLBACK; EXIT; END IF; END LOOP; -- Cursor closure CLOSE v_cursor; -- Verify if all units where transfered IF v_transferred_quantity SQL Error : > ERROR: Cursor "" does not exist > Where: PL/pgSQL function transfer_inventory(integer,integer,integer,integer) on line 72 in CLOSE Apparently, it's a closure issue of my cursor, but it is being closed after de loop ends. I am new to plpgsql so my solutions are limited.
Asked by matuco1998 (1 rep)
May 6, 2024, 04:33 PM
Last activity: Jan 5, 2025, 05:04 PM