How To Export Table Data Backup Using pgAgent Jobs Scheduler In PostgreS...

DO $$

DECLARE

    jid integer;

    scid integer;

BEGIN

-- Creating a new job

INSERT INTO pgagent.pga_job(

    jobjclid, jobname, jobdesc, jobhostagent, jobenabled

) VALUES (

    3::integer, 'Scheduler1'::text, ''::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),

    E'copy (select * from public.jobs) to ''D:\\DataExports\\jobs_export.csv'' DELIMITER '','' HEADER;'::text, ''::text

) ;

-- Schedules

-- Inserting a schedule

INSERT INTO pgagent.pga_schedule(

    jscjobid, jscname, jscdesc, jscenabled,

    jscstart,     jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths

) VALUES (

    jid, 'Scheduler1'::text, ''::text, true,

    '2024-03-25 22:41: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

    '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'::bool[]::boolean[],

    -- Week days

    '{f,f,f,f,f,f,f}'::bool[]::boolean[],

    -- Month days

    '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'::bool[]::boolean[],

    -- Months

    '{f,f,f,f,f,f,f,f,f,f,f,f}'::bool[]::boolean[]

) RETURNING jscid INTO scid;

END

$$;

How To Call A PostgreSQL Stored Procedure From pgAgent Schedule In pgAge...

Select * from jobs order by 1 desc;

delete from jobs;

CREATE OR REPLACE PROCEDURE public.job_proc()

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

BEGIN

Insert into jobs (entry_job) values (now());

END;

$BODY$;

Why pgAgent Jobs Not Working || How To Fix pgAgent Jobs || pgAgent For p...

How To Create A Simple pgAgent Job Using pgAdmin 4 In PostgreSQL Databas...

create table jobs (entry_job timestamp default now());

select * from jobs;

DO $$

DECLARE

    jid integer;

    scid integer;

BEGIN

-- Creating a new job

INSERT INTO pgagent.pga_job(

    jobjclid, jobname, jobdesc, jobhostagent, jobenabled

) VALUES (

    1::integer, 'Job1'::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),

    'insert into jobs (entry_job) values (now());'::text, 'Step1 Comment'::text

) ;

-- Schedules

-- Inserting a schedule

INSERT INTO pgagent.pga_schedule(

    jscjobid, jscname, jscdesc, jscenabled,

    jscstart,     jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths

) VALUES (

    jid, 'Scheduler1'::text, 'Scheduler1 comment'::text, true,

    '2024-03-16 00: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

    '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'::bool[]::boolean[],

    -- Week days

    '{f,f,f,f,f,f,f}'::bool[]::boolean[],

    -- Month days

    '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'::bool[]::boolean[],

    -- Months

    '{f,f,f,f,f,f,f,f,f,f,f,f}'::bool[]::boolean[]

) RETURNING jscid INTO scid;

END

$$;

How To Backup PostgreSQL Database With pg_dump And pg_dumpall From Serve...

pg_dump -h localhost -d dvdrental -U postgres -p 5432 -F tar >D:\DB_CMD_BKP\dvdrental.tar

pg_dump -h localhost -d dvdrental -U postgres -p 5432 -F custom >D:\DB_CMD_BKP\dvdrental.bak

pg_dump -h localhost -d dvdrental -U postgres -p 5432 -F plain >D:\DB_CMD_BKP\dvdrental.sql

pg_dump -h localhost -d dvdrental -U postgres -p 5432 -F directory -f D:\DB_CMD_BKP\Directory_BKP

pg_dumpall -h localhost -U postgres -p 5432 >D:\DB_CMD_BKP\Dump_All\DBs.sql

How To Load/Restore PostgreSQL Database Using psql Command || PostgreSQL...

psql -h localhost -d dvdrental -U postgres -p 5432 <D:\DB_CMD\dvdrental.sql

How To Restore PostgreSQL Database Using pg_restore Command In Command P...

pg_restore -h localhost -d dvdrental -U postgres -p 5432 D:\DB_CMD\dvdrental.tar

pg_restore -h localhost -d dvdrental -U postgres -p 5432 D:\DB_CMD\dvdrental.bak

How To Resolve Or Fix Error "Restoring backup on the server PostgreSQL 1...

How To Create A Database In PostgreSQL 16 Using pgAdmin 4 Or psql SQL Sh...

CREATE DATABASE "Akram_DB"

    WITH

    OWNER = postgres

    ENCODING = 'UTF8'

    LC_COLLATE = 'English_United States.1252'

    LC_CTYPE = 'English_United States.1252'

    LOCALE_PROVIDER = 'libc'

    TABLESPACE = pg_default

    CONNECTION LIMIT = -1

    IS_TEMPLATE = False;

COMMENT ON DATABASE "Akram_DB"

    IS 'This is a demo database';

Create table TestDemo(id numeric, name character varying(50) not null);

select version();

CREATE DATABASE testdemo2_db

    WITH

    OWNER = postgres

    ENCODING = 'UTF8'

    LC_COLLATE = 'English_United States.1252'

    LC_CTYPE = 'English_United States.1252'

    LOCALE_PROVIDER = 'libc'

    TABLESPACE = pg_default

    CONNECTION LIMIT = -1

    IS_TEMPLATE = False;

How To Create A Database In PostgreSQL 16,Using pgAdmin 4 Or psql SQL Shell,How To Create A Database,In PostgreSQL,Using pgAdmin 4,psql SQL Shell,Create A Database In PostgreSQL,Database In PostgreSQL Using pgAdmin 4,Database In PostgreSQL Using psql SQL Shell,How To,Create A Database In PostgreSQL 16,pgAdmin,psql,SQL Shell,Database,create,how to create database,create postgres database,database postgres,new database postgres,postgresql new database create,pgadmin create database,pgadmin database,psql database

How To Call A Stored Procedure/Function From A Trigger Function In Postg...

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. ❤

Working with triggers is fun and learning. There are endless possibilities that can be achieved through Triggers.

This time, I have explained the usage of Triggers to call other stored procedures or functions in PostgreSQL Trigger Functions.

#trigger #procedure #function #postgresql #database

CREATE TABLE students

(

    roll numeric(10,0),

    name character varying(30),

    course character varying(30)

);

CREATE OR REPLACE PROCEDURE 

trigger_proc(in_value1 IN numeric, in_value2 IN numeric, out_result OUT numeric)

LANGUAGE 'plpgsql'

AS $BODY$

Declare

lv_msg CHARACTER varying(100);

Begin

  out_result := in_value1 + in_value2;

exception

when others then

lv_msg := 'Error : '||sqlerrm;

raise notice '%',lv_msg;

end;

$BODY$;

CREATE OR REPLACE FUNCTION 

trigger_func(in_value1 IN numeric, in_value2 IN numeric) returns numeric

LANGUAGE 'plpgsql'

AS $BODY$

Declare

lv_msg CHARACTER varying(100);

out_result numeric (10);

Begin

  out_result := in_value1 + in_value2;

return out_result;

exception

when others then

lv_msg := 'Error :'||sqlerrm;

raise notice '%',lv_msg;

end;

$BODY$;

CREATE OR REPLACE FUNCTION student_logs_trg_func()

    RETURNS TRIGGER

    LANGUAGE 'plpgsql'

AS $BODY$

declare

lv_out_proc numeric(10);

lv_out_func numeric(10);

begin

call trigger_proc(50, 50, lv_out_proc);

lv_out_func := trigger_func(100, 100);

raise notice 'Proc Output: %',lv_out_proc;

raise notice 'Func Output: %',lv_out_func;

return new;

end;

$BODY$;

CREATE TRIGGER student_trg

    BEFORE INSERT OR DELETE OR UPDATE 

    ON students

    FOR EACH ROW

    EXECUTE FUNCTION student_logs_trg_func();

Insert into students values (1, 'Akram', 'MCA');

How To Call A Stored Procedure/Function From A Trigger Function In PostgreSQL

How To Call A Stored Procedure From A Trigger Function

Trigger Function In PostgreSQL

How To Call Stored Function From Trigger Function

Trigger In PostgreSQL

Call Procedure From Trigger

Call Function From Trigger

Procedure Call From Trigger In PostgreSQL

Function Call From Trigger In PostgreSQL

PostgreSQL Triggers

Triggers

Trigger Example PostgreSQL

How To Triggers PostgreSQL

How To Use/Create Temporary/Temp Tables In PostgreSQL Procedure/Function...

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. ❤

-- Global Temporary Tables Usage In PostgreSQL Procedure/Function

-- How To Use Temporary Tables In PostgreSQL Procedure/Function

create or replace procedure temp_tables_proc()

    language plpgsql

    as $$

declare

v_count numeric(10);

begin

create temp table temp_tables(name character varying(100)) on commit preserve rows;

insert into temp_tables values ('Mumbai');

insert into temp_tables values ('Pune');

select count(*) into v_count from temp_tables;

raise notice 'Records : %', v_count;

drop table temp_tables;

exception

when others then

raise notice 'Error : %', substr (sqlerrm, 1, 100);

end;

$$;

call temp_tables_proc();

What Are Global Temporary Tables And Temp Tables In PostgreSQL Database ...

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. ❤

-- Global Temporary Tables In PostgreSQL Database

create temporary table temp_location

(

city varchar(50),

street varchar(40)

) on commit delete rows;

select * from temp_location;

create temp table temp_cities

(

name character varying(100)

) on commit delete rows;

select * from temp_cities;

drop table temp_cities;

begin transaction;

insert into temp_cities values ('Mumbai');

insert into temp_cities values ('Pune');

commit;

create temp table temp_cities

(

name character varying(100)

) on commit preserve rows;

begin transaction;

create temp table temp_cities

(

name character varying(100)

) on commit drop;

select * from temp_cities;

Why pgAgent Job Is Not Working || How To Check pgAgent Job Working Or No...

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

$$;

How To Resolve/Fix The pgAdmin 4 server could not be contacted || Postgr...

How To Resolve/Fix The pgAdmin 4 server could not be contacted || PostgreSQL Database || pgAdmin 4

How To Resolve/fix The Pgadmin 4 Server Connection Problem How To Fix The Pgadmin 4 Server Could Not Be Contacted || Postgresql Database || Pgadmin 4 How To Fix Pgadmin 4 Server Connection Problems How To Fix Pgadmin 4 Server Could Not Be Contacted Error Pgadmin 4: How To Fix The "Server Could Not Be Contacted" Error How To Fix When The Pgadmin 4 Server Can't Be Contacted Resolving The "pgAdmin 4 Server Could Not Be Contacted" Error For Postgresql Databases 1. Restart the Services 2. Restart The PC/Laptop 3. Configure the pgAdmin 4. Relaunch pgAdmin

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. ❤

How To Resolve pgAdmin 4 server could not be contacted, How To Fix pgAdmin 4 server could not be contacted, pgAdmin server could not be contacted, Fix pgAdmin 4 server could not be contacted, PostgreSQL, pgAdmin 4, pgAdmin, the application server could not be contacted pgAdmin 4, PostgreSQL tutorial, connection refused, Pgadmin 4 Server Connection Problems, Pgadmin 4 Server Could Not Be Contacted Error, Pgadmin 4 Server Connection Problem, connection refused error, fix pgAdmin 4

How To Create And Call A Stored Procedure/Function With Refcursor As OUT...

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 😢.

create or replace function refcur_function

(in_actor_id IN numeric, refcur_output refcursor)

returns refcursor

language plpgsql

as $$

begin

open refcur_output for

Select title from film, film_actor

where film.film_id = film_actor.film_id

and film_actor.actor_id = in_actor_id;

return refcur_output;

exception when others then

raise notice 'Something Went Wrong';

end; $$

select refcur_function(1, 'refcur_output');

fetch all in refcur_output;

select refcur_function(2, 'refcur_output');

fetch all in refcur_output;

How To Create And Call A Stored Procedure Function With Refcursor As OUT Parameter In PostgreSQL

,Function With Refcursor OUT Parameter

,Procedure With Refcursor OUT Parameter

,Create A Function With Refcursor OUT Parameter

,Create A Procedure With Refcursor OUT Parameter

,Call A Stored Function With Refcursor OUT Parameter

,PLpgSQL Language

,PostgreSQL PLpgSQL Language

,Refcursor OUT

,Function Refcursor OUT

,Procedure Refcursor OUT