DECLARE CURSOR Statement - SQL Server to PostgreSQL Migration

In SQL Server, the DECLARE CURSOR statement declares a cursor variable and assigns a query to it. In PostgreSQL, you can also use a CURSOR variable defined in the declaration block.

Consider a sample table:

  CREATE TABLE colors (name VARCHAR(30));
 
  INSERT INTO colors VALUES ('Green');
  INSERT INTO colors VALUES ('Black');
  INSERT INTO colors VALUES ('Red');

SQL Server:

  -- Declare a cursor for query
  DECLARE cur CURSOR FOR SELECT name FROM colors
  DECLARE @name VARCHAR(30)
 
  -- Open cursor and fetch rows 
  OPEN cur
  FETCH cur INTO @name
  WHILE @@FETCH_STATUS = 0
  BEGIN
     PRINT @name
     FETCH cur INTO @name
  END
 
  CLOSE cur
  DEALLOCATE cur

PostgreSQL:

  DO $$
  DECLARE
    -- Declare a cursor for query 
    cur CURSOR FOR SELECT name FROM colors;
    v_name VARCHAR(30);
 
 BEGIN
    -- Open cursor and fetch rows 
    OPEN cur;
    FETCH cur INTO v_name;
    WHILE FOUND
    LOOP
       RAISE NOTICE '%', v_name;
       FETCH cur INTO v_name;
    END LOOP;
 
  CLOSE cur;
  END$$;

For more information, see SQL Server to PostgreSQL Migration.