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

[@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: kzbin.info/aero/PLRU_t-SgTrYgEyj0rCpDX30J0ombkf1kO

[@escm-9db]

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