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 declare a rowset object using the database.tablename%ROWTYPE
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();

[@JorgeEscobarMX]

Thank you. I love your playlist

[@SoftwareNuggets]

Thanks Jorge. Appreciate the comment.

[@MrGschecho]

Thank you! this explanation really help me out!

[@SoftwareNuggets]

Glad to hear this video was useful. Thanks for leaving a comment.

[@hoperight5280]

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?

[@SoftwareNuggets]

I have a video already about triggers which is pretty good, please watch. Tell me what you want the stored procedure to perform. If you already have a database table, could make a video that is relevant. Thanks for the comment and trust for me to make a video for you.

[@hoperight5280]

@@SoftwareNuggets I recall that I looked it up but I guess it fell off my radar. Thanks.

[@SoftwareNuggets]

@@hoperight5280 have you viewed any of my Postgresql Shorts?
many about Arrays and JSON.
here is a link, if you are interested:
ua-cam.com/play/PLRU_t-SgTrYgEyj0rCpDX30J0ombkf1kO.html

[@j_esc9]

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?

[@SoftwareNuggets]

Hey @j_esc9161, You are very correct. raise notice 'prev=% % %" should be "current row % % %". thanks for pointing this out. -- scott