Only 3.9% of viewers are subscribing to my channel 😓.
I request you to please give click on Subscribe button.
It really helps me grow 😢.
In this video, we will see how to schedule a stored procedure
to call in pgAgent Jobs
In the last video, we have seen how to schedule a job.
In this video, we will call a stored procedure.
So, let's start.
How To Call A Stored Procedure From A Schedule Job Using pgAgent Jobs In PostgreSQL Database pgAdmin || PostgreSQL pgAgent
-- Creating a table
CREATE TABLE public.emp
(
id numeric,
name character(30),
salary numeric,
insert_date timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);
-- Now we will create a stored procedure
CREATE OR REPLACE PROCEDURE public.testing_procedure()
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
BEGIN
Insert into emp(id,name,salary) values(1,'Akram Sohail',100);
Insert into emp(id,name,salary) values(2,'Knowledge 360',200);
END;
$BODY$;
CALL public.testing_procedure();
-- When this procedure will be called, two entries will be there in the Emp table.
-- Let's do it through a scheduled job using pgAgent Jobs
-- We have scheduled the procedure to be called every minute, every hour, every day, and all.
-- the First scheduler will be executed at 11:30 PM IST.
-- So, let's forward the video and wait.
Select * from emp;
-- There is no record as of now because the scheduler is not executed by now.
-- waiting...
-- We get two records at 11:30 PM as we can see from a timestamp.
-- Now, again new entries will come at 11:31 PM
-- So, our schedule job is working.
-- All the source codes and notes are in the description.
-- Please subscribe to my channel...Thank you :)
DO $$
DECLARE
jid integer;
scid integer;
BEGIN
-- Creating a new job
INSERT INTO pgagent.pga_job(
jobjclid, jobname, jobdesc, jobhostagent, jobenabled
) VALUES (
1::integer, 'New_Job'::text, 'This job will be used to call a stored procedure.'::text, ''::text, true
) RETURNING jobid INTO jid;
-- Steps
-- Inserting a step (jobid: NULL)
INSERT INTO pgagent.pga_jobstep (
jstjobid, jstname, jstenabled, jstkind,
jstconnstr, jstdbname, jstonerror,
jstcode, jstdesc
) VALUES (
jid, 'Step1'::text, true, 's'::character(1),
''::text, 'postgres'::name, 'f'::character(1),
'CALL public.testing_procedure(); -- Calling the stored procedure'::text, 'This is Step1'::text
) ;
-- Schedules
-- Inserting a schedule
INSERT INTO pgagent.pga_schedule(
jscjobid, jscname, jscdesc, jscenabled,
jscstart, jscend, jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths
) VALUES (
jid, 'Scheduler1'::text, 'The Scheduler'::text, true,
'2022-07-17 23:30:00+05:30'::timestamp with time zone, '2022-07-31 23:31:00+05:30'::timestamp with time zone,
-- Minutes
ARRAY[true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true]::boolean[],
-- Hours
ARRAY[true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true]::boolean[],
-- Week days
ARRAY[true,true,true,true,true,true,true]::boolean[],
-- Month days
ARRAY[true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true]::boolean[],
-- Months
ARRAY[true,true,true,true,true,true,true,true,true,true,true,true]::boolean[]
) RETURNING jscid INTO scid;
END
$$;
How To Call A Stored Procedure From A Schedule Job Using pgAgent Jobs,
How To Call A Stored Procedure From A Schedule Job Using pgAgent Jobs In PostgreSQL Database pgAdmin,
PostgreSQL Database pgAdmin,
PostgreSQL,
pgAgent,
Call A Stored Procedure,
Call A Stored Procedure From A Schedule Job,
Stored Procedure From A Schedule Job,
Schedule Job Using pgAgent Jobs,
Call A Stored Procedure,
Call A Stored Procedure From A pgAgent Jobs,
How To Call A Stored Procedure,
PostgreSQL Database,
Knowledge 360,
Akram Sohail,
Postgres,
database,
stored procedure,
procedure
