@@FETCH_STATUS Variable - SQL Server to PostgreSQL Migration

In SQL Server, the @@FETCH_STATUS variable holds the status of the last FETCH statement. In PostgreSQL, you can use the FOUND boolean variable.

Consider a sample table:

  -- 1 row table
  CREATE TABLE colors (name VARCHAR(30));
  INSERT INTO colors VALUES ('Green');

SQL Server:

  -- Declare a cursor for query
  DECLARE cur CURSOR FOR SELECT name FROM colors
  DECLARE @name VARCHAR(30)
 
  -- Open cursor and fetch the first row 
  OPEN cur
  FETCH cur INTO @name
 
  PRINT @@FETCH_STATUS
  -- Output: 0
 
  -- Try to fetch one more time
  FETCH cur INTO @name
  PRINT @@FETCH_STATUS
  -- Output: -1
 
  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 the first row
    OPEN cur;
    FETCH cur INTO v_name;
 
    RAISE NOTICE '%', FOUND;
    -- Output: t
 
    -- Try to fetch one more time
    FETCH cur INTO v_name;
    RAISE NOTICE '%', NOT FOUND;
    -- Output: t
 
    CLOSE cur;
  END$$;

For more information, see SQL Server to PostgreSQL Migration.