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 😢.
Please Like, Comment, and Subscribe to my channel. ❤
create table test_job (id numeric, time_stamp timestamp default current_timestamp);
SELECT * FROM test_job;
delete FROM test_job;
call public.testing_job_proc();
CREATE OR REPLACE PROCEDURE public.testing_job_proc()
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
lv_id numeric;
BEGIN
begin
select coalesce(max(id),0)+1 into lv_id from test_job;
exception when others then
lv_id := 0;
end;
Insert into test_job(id) values(lv_id);
END;
$BODY$;
DO $$
DECLARE
jid integer;
scid integer;
BEGIN
-- Creating a new job
INSERT INTO pgagent.pga_job(
jobjclid, jobname, jobdesc, jobhostagent, jobenabled
) VALUES (
1::integer, 'Test_Job'::text, 'Test Job'::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_job_proc();'::text, 'Step1 Test Job Proc'::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, ''::text, true,
'2023-07-23 20:00:00+05:30'::timestamp with time zone, '2023-07-31 20:00:00+05:30'::timestamp with time zone,
-- Minutes
'{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}'::bool[]::boolean[],
-- Hours
'{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}'::bool[]::boolean[],
-- Week days
'{t,t,t,t,t,t,t}'::bool[]::boolean[],
-- Month days
'{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}'::bool[]::boolean[],
-- Months
'{t,t,t,t,t,t,t,t,t,t,t,t}'::bool[]::boolean[]
) RETURNING jscid INTO scid;
END
$$;
