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

How To Create And Call A Stored Procedure With Multiple/Many OUT Paramet...

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 PROCEDURE public.two_out_param_proc(

IN in_customer_name character varying,

IN in_address_id numeric,

OUT out_payment_amount numeric,

OUT out_max_payment_date date

)

LANGUAGE 'plpgsql'

AS $BODY$

 

 Declare

 Begin

  Select sum(amount), max(payment_date) 

into out_payment_amount, out_max_payment_date

From payment

where customer_id = (select customer_id from customer

where first_name = in_customer_name

and address_id = in_address_id);

exception

when others then

raise notice 'Something went wrong';

end;

$BODY$;

ALTER PROCEDURE public.two_out_param_proc(character varying, numeric, numeric, date)

    OWNER TO postgres;

select sum(amount) from payment where customer_id = 341;

select * from customer where customer_id = 341;

do $$

declare

v_payment_amount numeric(10,2);

v_max_payment_date date;

begin

call two_out_param_proc('Peter',346,v_payment_amount,v_max_payment_date);

raise notice 'The total payment amount is % and the latest payment date is %',v_payment_amount,v_max_payment_date;

end $$;

How To Create And Call A Stored Procedure With OUT Parameters In Postgre...

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 procedure one_out_param_proc

(in_customer_name IN character varying,

 in_address_id IN numeric,

 out_payment_amount OUT numeric)

 Language 'plpgsql'

 AS $BODY$

 

 Declare

 Begin

  Select sum(amount) into out_payment_amount

From payment

where customer_id = (select customer_id from customer

where first_name = in_customer_name

and address_id = in_address_id);

exception

when others then

raise notice 'Something went wrong';

end;

$BODY$

select sum(amount) from payment where customer_id = 341;

select * from customer where customer_id = 341;

do $$

declare

v_payment_amount numeric(10,2);

begin

call one_out_param_proc ('Peter', 346, v_payment_amount);

raise notice 'Total Payment Amount is %',v_payment_amount;

end $$;

How To Create Audit Triggers In PostgreSQL || Trigger Functions 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 😢.

-- SEQUENCE: public.students_logs_seq -- DROP SEQUENCE IF EXISTS public.students_logs_seq; CREATE SEQUENCE IF NOT EXISTS public.students_logs_seq INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 999999999 CACHE 1; ALTER SEQUENCE public.students_logs_seq OWNER TO postgres;

-- Table: public.students -- DROP TABLE IF EXISTS public.students; CREATE TABLE IF NOT EXISTS public.students ( roll numeric(10,0), name character varying(30) COLLATE pg_catalog."default", course character varying(30) COLLATE pg_catalog."default" ) TABLESPACE pg_default; ALTER TABLE IF EXISTS public.students OWNER to postgres; -- Trigger: student_trg -- DROP TRIGGER IF EXISTS student_trg ON public.students; CREATE TRIGGER student_trg AFTER INSERT OR DELETE OR UPDATE ON public.students FOR EACH ROW EXECUTE FUNCTION public.student_logs_trg_func();

-- Table: public.students_logs -- DROP TABLE IF EXISTS public.students_logs; CREATE TABLE IF NOT EXISTS public.students_logs ( logs_id numeric(10,0) NOT NULL DEFAULT nextval('students_logs_seq'::regclass), roll_old numeric(10,0), name_old character varying(30) COLLATE pg_catalog."default", course_old character varying(30) COLLATE pg_catalog."default", roll_new numeric(10,0), name_new character varying(30) COLLATE pg_catalog."default", course_new character varying(30) COLLATE pg_catalog."default", actions character varying(50) COLLATE pg_catalog."default", log_date timestamp without time zone DEFAULT CURRENT_TIMESTAMP, CONSTRAINT students_logs_pkey PRIMARY KEY (logs_id) ) TABLESPACE pg_default; ALTER TABLE IF EXISTS public.students_logs OWNER to postgres;

-- FUNCTION: public.student_logs_trg_func() -- DROP FUNCTION IF EXISTS public.student_logs_trg_func(); CREATE OR REPLACE FUNCTION public.student_logs_trg_func() RETURNS trigger LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF AS $BODY$ declare begin if old.roll <> new.roll then insert into students_logs (roll_old,name_old,course_old, roll_new,name_new,course_new,actions) values(old.roll,old.name,old.course,new.roll,new.name,new.course, 'Roll Value Updated'); end if; if old.name <> new.name then insert into students_logs (roll_old,name_old,course_old, roll_new,name_new,course_new,actions) values(old.roll,old.name,old.course,new.roll,new.name,new.course,'Name Value Updated'); end if; if old.course <> new.course then insert into students_logs (roll_old,name_old,course_old, roll_new,name_new,course_new,actions) values(old.roll,old.name,old.course,new.roll,new.name,new.course, 'Course Value Updated'); end if; -- For Insert if old.roll is null then insert into students_logs (roll_old,name_old,course_old, roll_new,name_new,course_new,actions) values(old.roll,old.name,old.course,new.roll,new.name,new.course, 'New Record Inserted'); end if; -- For Delete if new.roll is null then insert into students_logs (roll_old,name_old,course_old, roll_new,name_new,course_new,actions) values(old.roll,old.name,old.course,new.roll,new.name,new.course, 'Existing Record Deleted'); end if; return new; end; $BODY$; ALTER FUNCTION public.student_logs_trg_func() OWNER TO postgres;

How To Create Audit Triggers In PostgreSQL || Trigger Functions In PostgreSQL || Part 2 Video

In this video, we will try to create an audit trigger function for PostgreSQL. This is also an extended video called Part 2 of the audit triggers series. Please watch the video completely to understand the concept of trigger functions in PostgreSQL.

In PostgreSQL, an audit trigger is a mechanism that allows you to monitor and record changes to database tables. It helps in maintaining data integrity, tracking modifications, and ensuring compliance with regulatory requirements. When certain events or actions occur, such as INSERT, UPDATE, or DELETE operations on specific tables, the audit trigger is triggered, and it performs predefined actions to capture relevant information. Here's how audit triggers work in PostgreSQL: Defining Audit Triggers: To implement audit triggers, you need to define them on the tables you want to monitor. An audit trigger is a database object associated with a specific table and set of events. It consists of trigger functions and rules that define the desired behavior when the associated events occur. Trigger Functions: A trigger function is a user-defined function that gets executed when the associated event is triggered. In the context of audit triggers, the trigger function typically captures the necessary information about the event and inserts it into an audit table or log. Audit Tables or Logs: An audit table or log is a separate table or set of tables where the audit trail is stored. This is where the trigger function inserts the relevant information about the event, such as the user who performed the action, the timestamp, the old and new values (in case of UPDATE operations), and any other desired metadata. Event Types: You can configure audit triggers to fire on specific events, such as INSERT, UPDATE, or DELETE operations. This allows you to customize the level of detail captured in the audit trail based on your requirements. For example, you might choose to audit only certain tables or specific columns within those tables. Trigger Rules: Trigger rules define the conditions under which the audit trigger should be fired. For example, you can specify that the trigger should only be activated when a specific column is modified or when a certain condition is met. Enabling and Disabling Audit Triggers: Once you have defined the audit triggers, you can enable or disable them as needed. This gives you flexibility in controlling when the triggers are active, such as during specific maintenance or auditing periods. Analyzing Audit Data: The captured audit trail can be analyzed to gain insights into the database activity, identify potential issues or anomalies, and meet compliance requirements. By reviewing the audit logs, you can track changes, detect unauthorized actions, and investigate any suspicious activities. It's important to note that implementing audit triggers requires careful consideration of performance and storage implications. Storing detailed audit logs can generate a significant amount of data, so it's essential to strike a balance between capturing sufficient information and managing resource usage effectively.

In summary, audit triggers in PostgreSQL provide a powerful mechanism to monitor and record changes in database tables. By capturing relevant information about specific events, they enhance data integrity, assist in compliance efforts, and enable effective analysis of database activity.

How To Create Audit Triggers In PostgreSQL || Trigger Functions 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 😢.

insert into students values (1, 'Akram Sohail', 'MCA');

select * from students;

select * from students_logs;

update students set course = 'MCA 2018-2019';

update students set name = 'A. Sohail';

update students set roll = 2;

delete from students_logs;

delete from students;

-- Table: public.students

-- DROP TABLE IF EXISTS public.students;

CREATE TABLE IF NOT EXISTS public.students

(

    roll numeric(10,0),

    name character varying(30) COLLATE pg_catalog."default",

    course character varying(30) COLLATE pg_catalog."default"

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.students

    OWNER to postgres;

-- Trigger: student_trg

-- DROP TRIGGER IF EXISTS student_trg ON public.students;

CREATE TRIGGER student_trg

    AFTER INSERT OR DELETE OR UPDATE 

    ON public.students

    FOR EACH ROW

    EXECUTE FUNCTION public.student_logs_trg_func();

-- Table: public.students_logs

-- DROP TABLE IF EXISTS public.students_logs;

CREATE TABLE IF NOT EXISTS public.students_logs

(

    roll_old numeric(10,0),

    name_old character varying(30) COLLATE pg_catalog."default",

    course_old character varying(30) COLLATE pg_catalog."default",

    actions character varying(50) COLLATE pg_catalog."default"

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.students_logs

    OWNER to postgres;

-- FUNCTION: public.student_logs_trg_func()

 -- DROP FUNCTION IF EXISTS public.student_logs_trg_func();

CREATE OR REPLACE FUNCTION PUBLIC.STUDENT_LOGS_TRG_FUNC() RETURNS TRIGGER LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF AS $BODY$

declare

begin

if old.roll <> new.roll then

insert into students_logs

(roll_old,name_old,course_old, actions)

values(old.roll,old.name,old.course, 'Roll Value Updated');

end if;

if old.name <> new.name then

insert into students_logs

(roll_old,name_old,course_old, actions)

values(old.roll,old.name,old.course, 'Name Value Updated');

end if;

if old.course <> new.course then

insert into students_logs

(roll_old,name_old,course_old, actions)

values(old.roll,old.name,old.course, 'Course Value Updated');

end if;

return new;

end;

$BODY$;

ALTER FUNCTION PUBLIC.STUDENT_LOGS_TRG_FUNC() OWNER TO POSTGRES;

 

In PostgreSQL, an audit trigger is a mechanism that allows you to monitor and record changes to database tables. It helps in maintaining data integrity, tracking modifications, and ensuring compliance with regulatory requirements. When certain events or actions occur, such as INSERT, UPDATE, or DELETE operations on specific tables, the audit trigger is triggered, and it performs predefined actions to capture relevant information.

Here's how audit triggers work in PostgreSQL:

Defining Audit Triggers: To implement audit triggers, you need to define them on the tables you want to monitor. An audit trigger is a database object associated with a specific table and set of events. It consists of trigger functions and rules that define the desired behavior when the associated events occur.

Trigger Functions: A trigger function is a user-defined function that gets executed when the associated event is triggered. In the context of audit triggers, the trigger function typically captures the necessary information about the event and inserts it into an audit table or log.

Audit Tables or Logs: An audit table or log is a separate table or set of tables where the audit trail is stored. This is where the trigger function inserts the relevant information about the event, such as the user who performed the action, the timestamp, the old and new values (in case of UPDATE operations), and any other desired metadata.

Event Types: You can configure audit triggers to fire on specific events, such as INSERT, UPDATE, or DELETE operations. This allows you to customize the level of detail captured in the audit trail based on your requirements. For example, you might choose to audit only certain tables or specific columns within those tables.

Trigger Rules: Trigger rules define the conditions under which the audit trigger should be fired. For example, you can specify that the trigger should only be activated when a specific column is modified or when a certain condition is met.

Enabling and Disabling Audit Triggers: Once you have defined the audit triggers, you can enable or disable them as needed. This gives you flexibility in controlling when the triggers are active, such as during specific maintenance or auditing periods.

Analyzing Audit Data: The captured audit trail can be analyzed to gain insights into the database activity, identify potential issues or anomalies, and meet compliance requirements. By reviewing the audit logs, you can track changes, detect unauthorized actions, and investigate any suspicious activities.

It's important to note that implementing audit triggers requires careful consideration of performance and storage implications. Storing detailed audit logs can generate a significant amount of data, so it's essential to strike a balance between capturing sufficient information and managing resource usage effectively.

In summary, audit triggers in PostgreSQL provide a powerful mechanism to monitor and record changes in database tables. By capturing relevant information about specific events, they enhance data integrity, assist in compliance efforts, and enable effective analysis of database activity.

How To Create Triggers In PostgreSQL || How To Create Trigger Function I...

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

-- Table: public.students

-- DROP TABLE IF EXISTS public.students;

CREATE TABLE IF NOT EXISTS public.students

(

    roll numeric(10,0),

    name character varying(30) COLLATE pg_catalog."default",

    course character varying(30) COLLATE pg_catalog."default"

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.students

    OWNER to postgres;

-- Table: public.students_logs

-- DROP TABLE IF EXISTS public.students_logs;

CREATE TABLE IF NOT EXISTS public.students_logs

(

    roll_old numeric(10,0),

    name_old character varying(30) COLLATE pg_catalog."default",

    course_old character varying(30) COLLATE pg_catalog."default"

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.students_logs

    OWNER to postgres;

-- FUNCTION: public.student_logs_trg_func()

-- DROP FUNCTION IF EXISTS public.student_logs_trg_func();

CREATE OR REPLACE FUNCTION public.student_logs_trg_func()

    RETURNS trigger

    LANGUAGE 'plpgsql'

    COST 100

    VOLATILE NOT LEAKPROOF

AS $BODY$

declare

begin

insert into students_logs

(roll_old,name_old,course_old)

values(old.roll,old.name,old.course);

return new;

end;

$BODY$;

ALTER FUNCTION public.student_logs_trg_func()

    OWNER TO postgres;

-- Trigger: student_trg

-- DROP TRIGGER IF EXISTS student_trg ON public.students;

CREATE TRIGGER student_trg

    BEFORE DELETE OR UPDATE 

    ON public.students

    FOR EACH ROW

    EXECUTE FUNCTION public.student_logs_trg_func();

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

select * from students;

select * from students_logs;

update students set name = 'Akram Sohail' where roll = 1;

update students set name = 'Akram Sohail (Modified)' where roll = 1;

delete from students where roll = 1;

----------------------------------------------------------------------------------------------

Triggers are special types of functions that are called/invoked/executed/performed automatically as per the trigger event declaration. A trigger is usually set before or after updating, deleting, insert DML statements on a table. That means, whenever a record is to be inserted, updated, or deleted, based on the time of execution which can be before or after, the trigger executes. Generally, the execution of the Trigger is called “Trigger Fired”.

In PostgreSQL, to create a trigger and make it usable, the sequence of object creation will be:

•   Create a Table on which a trigger to be implemented

•   Create another table to keep logs, so we can see the Trigger work

•   Create a Trigger Function that holds the trigger

•   Create the Trigger Function and Define the Trigger Logic

•   Perform Insert/Update/Delete Operations on the Table

PostgreSQL Refcursor || Cursors In PostgreSQL || Part 4 Refcursor Cursor...

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

select refcursor_cursor(1,'lv_refcursor');

fetch all in lv_refcursor;

create or replace function refcursor_cursor(in_actor_id in integer, lv_ref_cur refcursor)

returns refcursor

language plpgsql

as $$

begin

open lv_ref_cur

for select 'Title: '||f.title as Title

from film_actor fa, film f

where fa.film_id = f.film_id

and fa.actor_id = in_actor_id;

return lv_ref_cur;

exception when others then

raise notice 'Something Went Wrong';

end;

$$

PostgreSQL Cursors || Cursors Available In PostgreSQL || Part 3 For Loop...

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

call for_loop_cursor(1);

create or replace procedure for_loop_cursor(in_actor_id in integer)

language plpgsql

as $$

declare

lv_string character varying(200);

loop_rec record;

begin

for loop_rec in 

select f.title from film_actor fa,film f 

where fa.film_id = f.film_id 

and actor_id = in_actor_id

LOOP

lv_string := 'Title: '||loop_rec.title;

raise notice '%',lv_string;

end loop;

exception when others then

raise notice 'Something Went Wrong';

end;

$$

PostgreSQL Cursors || Cursors Available In PostgreSQL || Part 2 Paramete...

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

call parameterized_cursor(1);

create or replace procedure parameterized_cursor(in_actor_id in integer)

language plpgsql

as $$

declare

lv_string character varying(200);

loop_rec record;

rec1 record;

cur1 cursor(p_film_id integer) for 

select title from film where film_id = p_film_id order by film_id;

begin

for loop_rec in select film_id from film_actor where actor_id = in_actor_id

LOOP

open cur1(loop_rec.film_id);

loop 

fetch cur1 into rec1;

exit when not found;

lv_string := 'Title: '||rec1.title;

raise notice '%',lv_string;

end loop;

close cur1;

end loop;

exception when others then

raise notice 'Something Went Wrong';

end;

$$

Keywords

postgresql cursors parameterized cursor cursors available in postgresql,

parameterized cursor,

parameterised cursor,

cursors available in postgresql,

postgresql cursors,

postgresql,

cursors,

cursor,

postgres,

pg admin,

pg admin 4,

postgres pg admin,

how to create cursor in posgresql,

create cursor in posgresql,

create parameterized cursor,

create parameterised cursor,

fetch cursor,

loop cursor,

exit cursor,

declare cursor,

define cursor,

cursor in procedure,

cursor in function,

knowledge 360,

akram sohail

PostgreSQL Cursors || Cursors Available In PostgreSQL || Part 1 Simple C...

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

call simple_cursor();

create or replace procedure simple_cursor()

language plpgsql

as $$

declare

lv_string character varying(200);

rec1 record;

cur1 cursor for 

select actor_id, first_name, last_name as end_name from actor order by actor_id;

begin

open cur1;

loop 

fetch cur1 into rec1;

exit when not found;

lv_string := 'Actor ID: '||rec1.actor_id||', Actor First Name: '||rec1.first_name||', Actor Last Name: '||rec1.end_name;

raise notice '%',lv_string;

end loop;

close cur1;

exception when others then

raise notice 'Something Went Wrong';

end;

$$

Keywords:

postgresql cursors simple cursor cursors available in postgresql,simple cursor,cursors available in postgresql,postgresql cursors,postgresql,cursors,cursor,postgres,pg admin,pg admin 4,postgres pg admin,how to create cursor in posgresql,create cursor in posgresql,create simple cursor,fetch cursor,loop cursor,exit cursor,declare cursor,define cursor,cursor in procedure,cursor in function,knowledge 360,akram sohail,cursors in postgresql,postgresql cursor syntax