Performing operations on cursors in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a cursor in PostgreSQL?

A cursor is a database object used to retrieve a few rows from a query result at a time, allowing row-by-row processing instead of loading all rows at once.

beginner

How do you declare a cursor in PostgreSQL?

You declare a cursor using the DECLARE statement inside a PL/pgSQL block, for example: <br>DECLARE my_cursor CURSOR FOR SELECT * FROM employees;

beginner

What does the FETCH command do with a cursor?

FETCH retrieves the next set of rows from the cursor's result set. For example, FETCH NEXT FROM my_cursor; gets the next row.

intermediate

Why should you CLOSE a cursor after use?

Closing a cursor releases the resources associated with it. If you don't close it, resources remain allocated, which can cause performance issues.

intermediate

How do you move a cursor to a specific position?

You use the MOVE command to reposition the cursor without fetching data. For example, MOVE ABSOLUTE 5 IN my_cursor; moves the cursor to the 5th row.

Which command is used to start reading rows from a declared cursor in PostgreSQL?

A FETCH

B DECLARE

C OPEN

D MOVE

FETCH reads rows from the cursor. DECLARE defines it, OPEN activates it, and MOVE repositions it.

What happens if you try to FETCH from a cursor that is not OPEN?

A It closes the cursor

B It returns an empty result

C An error occurs

D It automatically opens the cursor

You must OPEN a cursor before FETCHing; otherwise, PostgreSQL raises an error.

Which statement correctly closes a cursor named 'cur1'?

A FINISH cur1;

B END cur1;

C STOP cur1;

D CLOSE cur1;

The CLOSE statement releases the cursor resources.

What does the MOVE command do in cursor operations?

A Repositions the cursor without returning rows

B Fetches the next row

C Closes the cursor

D Declares a new cursor

MOVE changes the cursor position but does not return any data.

Which of the following is NOT a valid cursor operation in PostgreSQL?

A DECLARE

B INSERT

C FETCH

D CLOSE

INSERT is a data manipulation command, not a cursor operation.

Explain the lifecycle of a cursor in PostgreSQL and the main operations you perform on it.

Think about how you start, use, reposition, and finish with a cursor.

Describe why and when you would use a cursor instead of a simple SELECT query.

Consider situations where you don't want to load all data at once.