#PostgreSQL_cursor_setting_multiple_variables.md

#PostgreSQL cursor setting multiple variables

Overview

• The following document demonstrates how to use a cursor within a PostgreSQL function to set multiple variable in a single fetch statement.
• Test code was executed using the online platform DB Fiddle with a PostgreSQL 14 instance.
  • Live demo

Test File / Usage

/* FILE: PostgreSQL_cursor_setting_multiple_variables.sql
		
* Creates table, loads data, defines function then calls function.
    
* Function creates cursor, assigns values to variables using cursor,
    uses variables to set all studentId values to NULL then returns
    number of changes made.
*/


CREATE TABLE Students(
	name VARCHAR(50),
  	studentId INT
);

INSERT INTO Students(name, studentId) VALUES
	('Xue Ying', 1),
    ('Qian', 2),
    ('An Shang', 3),
    ('Yuan', 4);
    

CREATE OR REPLACE FUNCTION
change_studentId_to_null()
RETURNS INTEGER AS ' 
	
    DECLARE
    	number_of_updates INT;
        currentName VARCHAR(50);
        currentId INT;
    
    DECLARE studentInformationCursor CURSOR FOR
    	    SELECT s.name, s.studentId
            FROM Students s
            WHERE TRUE;

    BEGIN
    
      number_of_updates := 0;

      OPEN studentInformationCursor;
      
      LOOP
      	FETCH studentInformationCursor INTO currentName, currentId;
        EXIT WHEN NOT FOUND;
        
        UPDATE Students s
        SET studentId = NULL
        WHERE s.studentId = currentId
        	AND s.name = currentName;
        
        number_of_updates := number_of_updates +1;
        
      END LOOP;
      CLOSE studentInformationCursor;

      RETURN number_of_updates;

    END

' LANGUAGE plpgsql;

SELECT VERSION();

SELECT * FROM Students;

SELECT change_studentId_to_null();

SELECT * FROM Students;

Output

version
PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-15), 64-bit

name	studentid
Xue Ying	1
Qian	2
An Shang	3
Yuan	4

change_studentid_to_null
4

name	studentid
Xue Ying
Qian
An Shang
Yuan