"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
Last activity: Jan 5, 2025, 05:04 PM