PostgreSQL Cursors Lecture Notes

Introduction

• Host: Akram
• Topic: Cursors in PostgreSQL
• Focus of the video: Simple Cursor
• Future videos will cover other cursor types:
  • Parameterized Cursor
  • For Loop Cursor
  • Ref Cursor

Database Setup

• Requirement: A database set up before running examples.
• Suggested Database: DVD Rental Database
  • Use a sample database: Link in video description.
• Example table used: Actor Table

Accessing Data

• Query to view data: SELECT * FROM actor;

Simple Cursor Implementation

• Stored Procedure Creation:
  • Name: simple_cursor
  • Language: PL/pgSQL
  • Delimiter and declarations:
    • Variable: lv_string of type character varying (size 200)
    • Variable: rs_one of type record
    • Cursor: cursor1
    • SQL Query for cursor: SELECT actor_id, first_name, last_name FROM actor ORDER BY actor_id;

Procedure Structure

1. Begin Block:

   • Open the cursor: OPEN cursor1;

2. Loop through records:

   • Fetch records into rs_one: FETCH cursor1 INTO rs_one;

3. Exit condition:

   • Exit when no more records are found: EXIT WHEN NOT FOUND;

4. Accessing Values:

   • Store values in lv_string: lv_string := rs_one.actor_id || ' ' || rs_one.first_name || ' ' || rs_one.last_name;
   • Alternatively, using aliases: lv_string := rs_one.actor_id || ' ' || rs_one.first_name AS first_name_alias || ' ' || rs_one.last_name AS last_name_alias;

5. Output results:

   • Use RAISE NOTICE to show outputs: RAISE NOTICE '%', lv_string;

6. Close Cursor:

   • Close the cursor at the end of execution: CLOSE cursor1;

Exception Handling

• Implemented using: EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'Something went wrong';

Calling the Procedure

• Procedure Call Syntax: CALL simple_cursor();
• Output verification against actor table.

Conclusion

• Next video: Other types of cursors in PostgreSQL.
• Reminder to subscribe for updates.
• Offer to answer questions in the comments.
• Closing remarks: Take care!