How to fix cursor already exists in PostgreSQL

-- Look for DECLARE statements in your functions:
DECLARE cursor_name CURSOR FOR SELECT ...;

-- Or in application code:
BEGIN;
DECLARE my_cursor CURSOR FOR SELECT * FROM table_name;

-- Proper cursor lifecycle in PL/pgSQL:
CREATE FUNCTION process_rows() RETURNS void AS $$
DECLARE
  my_cursor CURSOR FOR SELECT * FROM my_table;
  row RECORD;
BEGIN
  OPEN my_cursor;
  LOOP
    FETCH NEXT FROM my_cursor INTO row;
    EXIT WHEN NOT FOUND;
    -- Process row...
  END LOOP;
  CLOSE my_cursor;  -- MUST close before function returns
END;
$$ LANGUAGE plpgsql;

-- Complete cursor lifecycle:
BEGIN;
DECLARE my_cursor CURSOR FOR SELECT * FROM my_table;
FETCH FORWARD 10 FROM my_cursor;  -- Get rows
CLOSE my_cursor;                   -- Release cursor resources
DEALLOCATE my_cursor;              -- Remove cursor definition
COMMIT;

-- In application code with try-finally pattern:
BEGIN;
  DECLARE my_cursor CURSOR FOR SELECT * FROM my_table;
  -- Process cursor...
FINALLY
  CLOSE my_cursor;
  DEALLOCATE my_cursor;
END;

-- Instead of manual cursor management:
CREATE FUNCTION process_rows_bad() RETURNS void AS $$
DECLARE
  my_cursor CURSOR FOR SELECT * FROM my_table;
  row RECORD;
BEGIN
  OPEN my_cursor;
  LOOP
    FETCH NEXT FROM my_cursor INTO row;
    EXIT WHEN NOT FOUND;
    -- Process row...
  END LOOP;
  CLOSE my_cursor;
END;
$$ LANGUAGE plpgsql;

-- Use a FOR loop instead (much cleaner):
CREATE FUNCTION process_rows_good() RETURNS void AS $$
DECLARE
  row RECORD;
BEGIN
  FOR row IN SELECT * FROM my_table LOOP
    -- Process row...
  END LOOP;
  -- Cursor is automatically managed, no CLOSE or DEALLOCATE needed
END;
$$ LANGUAGE plpgsql;

-- Instead of hardcoded names:
DECLARE my_cursor CURSOR FOR SELECT * FROM my_table;  -- Bad - name collision

-- Use dynamically generated names:
CREATE FUNCTION process_rows_safe(p_table_id INT) RETURNS void AS $$
DECLARE
  cursor_name TEXT := 'cursor_' || p_table_id || '_' || EXTRACT(EPOCH FROM now());
BEGIN
  -- Cursor names must be hardcoded in DECLARE, not dynamic
  -- So instead, use different cursor names for different paths:
  IF p_table_id = 1 THEN
    DECLARE cursor_1 CURSOR FOR SELECT * FROM table_1;
    -- Process and close cursor_1
  ELSE
    DECLARE cursor_2 CURSOR FOR SELECT * FROM table_2;
    -- Process and close cursor_2
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Ensure explicit transaction boundaries:
BEGIN;  -- Start transaction
-- Your cursor operations here
CLOSE my_cursor;
DEALLOCATE my_cursor;
COMMIT;  -- Commit transaction, cursor is gone

-- New operation can now reuse the cursor name:
BEGIN;
DECLARE my_cursor CURSOR FOR SELECT ...;  -- No conflict now
-- ...
COMMIT;

-- Test single invocation (will likely work):
SELECT process_rows();

-- Test multiple invocations in same transaction (this is the real test):
BEGIN;
SELECT process_rows();
SELECT process_rows();  -- This would fail if cursors not closed
SELECT process_rows();  -- And this too
COMMIT;

-- In your test suite:
-- Run the same function multiple times in a single test case
-- Verify no cursor conflicts occur