How to write a Cursor for PostgreSQL. Learn how to Open, Move, Fetch, Close., and Update/Delete DB.

How to write a Cursor that loops over a result set, and allow the program to evaluate each row.

table of contents:
00:00 begin
00:12 - build a table and insert data
00:44 - build a basic cursor
06:42 - learn how to fetch
09:42 - learn how to loop
11:54 - learn how to loop in reverse
13:47 - learn how to update base table (and how to use random() function)
17:44 - learn how to delete from base table
21:25 - closing message from software nuggets

Access a cursor in PL/pgSQL goes through cursor variables, which are always of the special data type refcursor.
How to open and close a cursor
How to use the cursor move method to reposition the current pointer
How to the the curosr fetch method using the directions: first, last, prior, next and relative
How to use the special variable "found" to exit a loop

Use the built-in RANDOM function and LIMIT the number of Records to be used in a Cursor.

How to use raise notice to print a response to the Messages tab.

#softwareNuggets, #postgresql

[SoftwareNuggets]

SOURCE CODE FOR VIDEO
written by : Software Nuggets

drop table public.department
create table public.department
(
deptId int not null,
deptName varchar(50) not null,
groupName varchar(50) not null,
modDate timestamp not null,
onSite bool not null default 'Y'
);


insert into public.department(deptId, deptName, groupName, modDate)
values
(1, 'Executive', 'Admin', '2022-09-03 16:40'),
(2, 'Finance', 'Admin', '2022-09-03 16:40'),
(3, 'Human Resources', 'Admin', '2022-09-03 16:40'),
(4, 'Programming', 'MIS', '2022-09-03 16:40'),
(5, 'Networking', 'MIS', '2022-09-03 16:40'),
(6, 'Marketing', 'Admin', '2022-09-03 16:40'),
(7, 'Quality', 'MIS', '2022-09-03 16:40'),
(8, 'Documentation', 'MIS', '2022-09-03 16:40'),
(9, 'Research', 'MIS', '2022-09-03 16:40'),
(10, 'Help Desk', 'MIS', '2022-09-03 16:40')



create or replace function ShowReverse()
returns void
as
$$
declare
c1 refcursor;
trow department%rowtype;
begin

raise notice 'start';

open c1 scroll for select * from department;

fetch last from c1 into trow;

loop
raise notice 'found=%', found;

exit when not found;
raise notice 'prev=% % %', trow.deptId, trow.deptName, trow.groupName;
fetch prior from c1 into trow;

end loop;

close c1;
raise notice 'finished';

end;
$$
language 'plpgsql';

select * from ShowReverse();



create or replace function ShowAllDepartments()
returns void
as
$$
declare
c1 refcursor;
trow department%rowtype;
work_onsite bool;
begin

raise notice 'start';
open c1 for
select deptId, deptName, onsite
from department
order by random()
fetch first 3 rows only;

loop
fetch next from c1 into trow;
exit when not found;

if trow.deptId % 2 = 1 then
raise notice 'found=% %', trow.deptId, trow.deptName;
update department
set onsite = false,
modDate = now()
where deptId = trow.deptId;

end if;

end loop;

close c1;
raise notice 'finished';

end;
$$
language 'plpgsql';
select * from ShowAllDepartments();


create or replace function DeleteWhenFalse()
returns void
as
$$
declare
c1 refcursor;
trow department%rowtype;
begin
open c1 for
select * from department;

loop
fetch next from c1 into trow;
exit when not found;


if trow.deptId % 2 = 1 then
if trow.OnSite = 'f' then
delete from department
where deptId = trow.deptId;

raise notice 'has been deleted: % %', trow.deptId, trow.onSite;
end if;
end if;
end loop;

end;
$$
language 'plpgsql';
select * from DeleteWhenFalse();

[MrGschecho]

Thank you! this explanation really help me out!

[hoperight]

Again, thank you for the effort; it's priceless. Is there any chance that you could make videos of store procedures and triggers as well?

[j_esc]

11:07 trivial but curious why we're printing 'prev=% % %' in the looping example. Is this supposed to stand for Previous row? Shouldn't it maybe be Current as it's going to print whichever row we're currently on?