How To Display Output Message Like DBMS_OUTPUT.PUT_LINE In PostgreSQL Us...

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

How To Display Output Message Like DBMS_OUTPUT.PUT_LINE In Oracle Using PostgreSQL || Learn PL/pgSQL
How To Display Output Message Like DBMS_OUTPUT.PUT_LINE In PostgreSQL Using PgAdmin | Learn PL/pgSQL









-- The Purpose is to Display Message like DBMS_OUTPUT.PUT_LINE

-- of Oracle in PostgreSQL





CREATE OR REPLACE PROCEDURE public.proc(p_num1 IN numeric,p_num2 IN numeric)

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

v_sum numeric;

v_mult numeric;

v_div numeric;

BEGIN

v_sum := p_num1 + p_num2;

v_mult := p_num1 * p_num2;

BEGIN

v_div := TRUNC(p_num1/p_num2);

EXCEPTION WHEN OTHERS THEN

v_div := 0;

END;

RAISE NOTICE 'The Sum is : %',v_sum;

RAISE NOTICE 'The Multiplication is : %',v_mult;

RAISE NOTICE 'The Division is : %',v_div;



END;

$BODY$;



CALL public.proc(10,2);

How To Create A Stored Procedure And Return Multiple Values From The Sto...

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

-- purpose is to return multiple values (more than one value) from a stored procedure

-- in PostgreSQL



How To Create A Stored Procedure And Return Multiple Values From The Stored Procedure In PostgreSQL





CREATE OR REPLACE PROCEDURE public.testing_procedure(p_num1 IN numeric,p_num2 IN numeric,p_sum INOUT numeric,p_mult INOUT numeric,p_div INOUT numeric)

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

BEGIN

p_sum := p_num1 + p_num2;

p_mult := p_num1 * p_num2;

BEGIN

p_div := p_num1/p_num2;

EXCEPTION WHEN OTHERS THEN

p_div := 0;

END;

END;

$BODY$;



CALL public.testing_procedure(10,0,null,null,0);







CREATE OR REPLACE PROCEDURE public.testing_procedure(p_num1 IN numeric,p_num2 IN numeric,p_sum INOUT numeric,p_mult INOUT numeric,p_div INOUT text)

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

BEGIN

p_sum := p_num1 + p_num2;

p_mult := p_num1 * p_num2;

BEGIN

p_div := p_num1/p_num2;

EXCEPTION WHEN OTHERS THEN

p_div := 'You cannot divide a value by zero';

END;

END;

$BODY$;



CALL public.testing_procedure(10,0,null,null,'0');

How To Create A Stored Procedure With Parameter Modes IN INOUT OUT Modes...

How To Create A Stored Procedure With Parameter Modes IN INOUT OUT Modes In Postgresql || PL/pgSQL

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

-- purpose is to create a stored procedure with parameter mode (IN, INOUT) and call it



-- IN Only Input and it is default



-- OUT Only Output, only OUT is not allowed in Stored Procedure in PostgreSQL



-- INOUT Input + Output



CREATE OR REPLACE PROCEDURE public.testing_procedure(p_num1 IN numeric,p_num2 IN numeric, p_sum INOUT numeric)

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

BEGIN

p_sum := p_num1 + p_num2;

END;

$BODY$;



CALL public.testing_procedure(13,10,-1);









CREATE OR REPLACE PROCEDURE public.testing_procedure(p_num1 IN numeric,p_num2 IN numeric, p_sum INOUT numeric,p_mult INOUT numeric)

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

BEGIN

p_sum := p_num1 + p_num2;

p_mult := p_num1 * p_num2;

END;

$BODY$;



CALL public.testing_procedure(3,0,-1,-1);

How To Create A Stored Procedure And Insert Data Into A Table By Calling...

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

How To Create A Stored Procedure And Insert Data Into A Table By Calling/Using The Stored Procedure



-- the purpose is to create a simple stored procedure in PostgreSQL
-- and Insert Data in the table by calling it using pgAdmin


CREATE TABLE public.testing_table (dummy_column text);

CREATE OR REPLACE PROCEDURE public.testing_procedure(p_msg text)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
BEGIN
INSERT INTO public.testing_table(dummy_column) VALUES('Before Inserting Parameter Value');
INSERT INTO public.testing_table(dummy_column) VALUES (p_msg);
INSERT INTO public.testing_table(dummy_column) VALUES('After Inserting Parameter Value');
END;
$BODY$;

SELECT * FROM public.testing_table;

DELETE from public.testing_table;

CALL public.testing_procedure('Hello Akram, How are you?');

Before Inserting
the message I pass as a parameter
After Inserting...

How To Resolve/Fix Issue Could Not Connect To Server Connection Refused ...

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

How To Resolve/Fix Issue Could Not Connect To Server Connection Refused In PostgreSQL pgAdmin 4
How To resolve Issue Application Server Could Not Be Contacted In PostgreSQL

Overloading Of A Stored Function In PostgreSQL || Stored Function Overlo...

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

Overloading Of A Stored Function In PostgreSQL || Stored Function Overloading || PL/pgSQL Tutorials



CREATE OR REPLACE FUNCTION public.testing_function(p_num1 numeric, p_num2 numeric)

RETURNS numeric

LANGUAGE 'plpgsql'

COST 100

VOLATILE

AS $BODY$

DECLARE

v_result numeric;

BEGIN

v_result := p_num1 + p_num2;



RETURN v_result;

END;

$BODY$;







CREATE OR REPLACE FUNCTION public.testing_function(p_num1 numeric, p_num2 numeric,p_num3 numeric)

RETURNS numeric

LANGUAGE 'plpgsql'

COST 100

VOLATILE

AS $BODY$

DECLARE

v_result numeric;

BEGIN

v_result := p_num1 + p_num2 + p_num3;



RETURN v_result;

END;

$BODY$;





CREATE OR REPLACE FUNCTION public.testing_function(p_num1 numeric, p_num2 numeric,p_num3 numeric,p_num4 numeric)

RETURNS numeric

LANGUAGE 'plpgsql'

COST 100

VOLATILE

AS $BODY$

DECLARE

v_result numeric;

BEGIN

v_result := p_num1 + p_num2 + p_num3 + p_num4;



RETURN v_result;

END;

$BODY$;





SELECT public.testing_function(14,5);



SELECT public.testing_function(14,10,50);



SELECT public.testing_function(10,20,30,50);

Overloading Of A Stored Procedure In PostgreSQL | Stored Procedure Overl...

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

Overloading Of A Stored Procedure In PostgreSQL | Stored Procedure Overloading | PL/pgSQL Tutorials



CREATE TABLE public.testing(demo_column text);



CREATE OR REPLACE PROCEDURE public.testing_procedure(p_msg text)

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

BEGIN

INSERT INTO public.testing(demo_column) VALUES (p_msg);

END;

$BODY$;







CREATE OR REPLACE PROCEDURE public.testing_procedure(p_num1 numeric, p_num2 numeric)

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

v_result numeric;

BEGIN

v_result := p_num1+p_num2;

INSERT INTO public.testing(demo_column) VALUES ('The Sum is '||v_result);

END;

$BODY$;





CREATE OR REPLACE PROCEDURE public.testing_procedure(p_num1 numeric, p_num2 numeric,p_num3 numeric)

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

v_result numeric;

BEGIN

v_result := p_num1+p_num2 +p_num3;

INSERT INTO public.testing(demo_column) VALUES ('The Sum is '||v_result);

END;

$BODY$;







CALL public.testing_procedure('Hello Akram');



CALL public.testing_procedure(3,5);



CALL public.testing_procedure(3,5,67);



SELECT * FROM public.testing;

How To Handle Exception In A Stored Procedure In PostgreSQL || PL/pgSQL ...

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

The Purpose Is To Handle Exception In A Stored Procedure In PostgreSQL || PL/pgSQL Tutorials Exception Handling



-- without exception block



CREATE TABLE public.testing (demo_column text);



CREATE OR REPLACE PROCEDURE public.testing_procedure(p_num1 numeric,p_num2 numeric)

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

v_result numeric;

BEGIN

v_result := p_num1/p_num2;

INSERT INTO public.testing(demo_column) VALUES(v_result);

END;

$BODY$;



CALL public.testing_procedure(100,0);



SELECT * from public.testing;









-- with exception block



CREATE TABLE public.testing (demo_column text);



CREATE OR REPLACE PROCEDURE public.testing_procedure(p_num1 numeric,p_num2 numeric)

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

v_result text;

BEGIN

BEGIN

v_result := p_num1/p_num2;

EXCEPTION WHEN OTHERS THEN

v_result := 'You cannot divide a number by zero.';

END;

INSERT INTO public.testing(demo_column) VALUES(v_result);



END;

$BODY$;



CALL public.testing_procedure(100,20);



SELECT * from public.testing;

How To Create A Simple Parameterized Stored Procedure In PostgreSQL And ...

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

-- purpose is to create a simple parameterized stored procedure in postgreSQL

-- and call it using pgAdmin



CREATE TABLE public.testing (demo_column text);



CREATE OR REPLACE PROCEDURE public.testing_procedure(p_msg text)

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

BEGIN

INSERT into public.testing(demo_column) values(p_msg);

END;

$BODY$;



call public.testing_procedure('Hello Akram, We are watching Knowledge 360');



SELECT * from public.testing;

How To Create A Simple Stored Procedure In PostgreSQL And Call Using pgA...

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

-- purpose is to create a simple stored procedure in postgreSQL

-- and call it using pgAdmin





CREATE TABLE public.testing (demo_column text);





CREATE OR REPLACE PROCEDURE public.testing_procedure()

LANGUAGE 'plpgsql'

AS $BODY$

DECLARE

v_msg text;

BEGIN

v_msg := 'Hello Akram, We are watching Knowledge 360';



Insert into public.testing(demo_column) values (v_msg);

END;

$BODY$;





call public.testing_procedure();





SELECT * from public.testing;

How To Handle Exception In A Stored Function/Procedure In PostgreSQL | E...

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

-- without exception block

-- purpose is to handle exception in a stored function in PostgreSQL and call it



SELECT public.testing_function(100,0);



CREATE OR REPLACE FUNCTION public.testing_function(p_num1 numeric, p_num2 numeric)

RETURNS numeric

LANGUAGE 'plpgsql'

COST 100

VOLATILE

AS $BODY$

DECLARE

v_result numeric;

BEGIN

v_result := p_num1/p_num2;

RETURN v_result;

END;

$BODY$;







---with exception block



SELECT public.testing_function(100,0);



CREATE OR REPLACE FUNCTION public.testing_function(p_num1 numeric, p_num2 numeric)

RETURNS text

LANGUAGE 'plpgsql'

COST 100

VOLATILE

AS $BODY$

DECLARE

v_result text;

BEGIN

BEGIN

v_result := p_num1/p_num2;

EXCEPTION WHEN OTHERS THEN

v_result := 'You cannot divide by Zero, try Again';

END;



RETURN v_result;

END;

$BODY$;

How To Create A Parameterized Stored Function In PostgreSQL || Pass Para...

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 public.testing_function(101,'Hello Akram');


CREATE OR REPLACE FUNCTION public.testing_function(p_num numeric,p_msg text)



RETURNS text

LANGUAGE 'plpgsql'



COST 100

VOLATILE



AS $BODY$



DECLARE

v_return_msg text;

BEGIN



v_return_msg := p_msg ||' '||p_num;



return v_return_msg;



END;

$BODY$;

How To Create A Stored Function In PostgreSQL And Call Stored 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 😢.

SELECT public.testing_function();



CREATE OR REPLACE FUNCTION public.testing_function()



RETURNS text

LANGUAGE 'plpgsql'



COST 100

VOLATILE



AS $BODY$



DECLARE

v_return_msg text;

BEGIN



v_return_msg := 'Hello Akram';



return v_return_msg;



END;

$BODY$;