Cursor declaration and usage 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. It allows you to process query results row by row instead of all at once.

beginner

How do you declare a cursor in PostgreSQL?

You declare a cursor using the syntax: <br>DECLARE cursor_name CURSOR FOR SELECT_statement;<br>This prepares the cursor to fetch rows from the SELECT query.

beginner

What command is used to fetch rows from a cursor?

The FETCH command is used to retrieve rows from a cursor. For example: FETCH NEXT FROM cursor_name; gets the next row.

intermediate

Why should you close a cursor after use?

Closing a cursor releases the memory and resources it uses. Use CLOSE cursor_name; to close it when done.

intermediate

Explain the typical steps to use a cursor in PostgreSQL.

1. Declare the cursor with a SELECT query.<br>2. Open the cursor (optional in PostgreSQL, DECLARE opens it).<br>3. Fetch rows one by one or in blocks.<br>4. Close the cursor to free resources.

Which SQL command declares a cursor in PostgreSQL?

A DECLARE cursor_name CURSOR FOR SELECT * FROM table;

B OPEN cursor_name;

C FETCH cursor_name;

D CLOSE cursor_name;

The DECLARE command is used to create a cursor with a query.

What does the FETCH command do?

A Retrieves rows from the cursor

B Closes the cursor

C Declares a new cursor

D Opens the cursor

FETCH retrieves rows from the cursor one at a time or in groups.

Why is it important to CLOSE a cursor?

A To start fetching rows

B To declare the cursor

C To release resources used by the cursor

D To open the cursor

Closing a cursor frees memory and resources it uses.

Which of these is NOT a typical step when using a cursor?

A Declare the cursor

B Insert rows into the cursor

C Fetch rows

D Close the cursor

You cannot insert rows into a cursor; it only reads query results.

In PostgreSQL, when is a cursor opened?

A When you fetch from it

B When you commit a transaction

C When you close it

D When you declare it

In PostgreSQL, DECLARE opens the cursor immediately.

Describe how to declare, fetch from, and close a cursor in PostgreSQL.

Think about the three main commands: DECLARE, FETCH, CLOSE.

Explain why cursors are useful when working with large query results.

Consider memory and performance when handling big data.