PL/pgSQL in postgres

Declare Variable  

Q1:-

 do
$$
declare
   num1 integer:=96;
   num2 integer:=100;
   num3 integer;
   begin
   num3 = num1 + num2;
   raise notice 'add two no : %' , num3 ;
   
   
    -- raise notice 'The number of films: %', message;
end;
$$ 

Q2: Declare variable integer and varchar and numeric 

do $$
declare
   counter    integer := 1;
   first_name varchar(50) := 'KAMAL';
   last_name  varchar(50) := 'Doe';
   payment    numeric(11,2) := 100.5;
begin
   raise notice '% % % has been paid % USD',
       counter,
       first_name,
       last_name,
       payment;
end $$;

@: Declare variable time and sleep

do $$
declare
create_time time := now();
begin
raise notice '%' ,create_time ;
perform pg_sleep (10);

raise notice '%' ,create_time ;

end
$$

Q:-Fetch data from table using %type (Copy data type)

do $$
declare
    e_id emp_details.emp_id%type:=1;
    e_name emp_details.emp_name%type;
    e_email emp_details.emp_email%type;
    begin
   
SELECT emp_id, emp_name, emp_email, create_on
  FROM emp_details into e_id , e_name , e_email
   where emp_id=2;
raise notice 'emp_id is % and emp_name is % or emp_email %',e_id ,e_name,e_email;
end ;
$$

Q :- Global block and local block called value .

do $$

<<global_block>>
declare
    counter integer := 10;
    begin
        raise notice 'This is global block is %', counter;
--------------------internal value call------------------------------
declare    
    val integer :=20;
    begin
    raise notice 'This is local variable %',val;
    raise notice 'This is global variable called in local block %',global_block.counter;   
    end;
   
end global_block $$;

 do $$
 

Q:-PL/pgSQL Select Into statement example

@Select into is used for select data from database and store in declared data .

declare
Total_emp integer;
begin
select count(*) into Total_emp  
FROM emp_details ;

raise notice 'This is total emp: %', Total_emp;

end; $$

 OUTPUT:

NOTICE:  This is total emp: 5

PL/pgSQL row types example: 

Row type is used for store whole row in select into and extract data

as per your requirement.  

Syntax : row_variable table_name%ROWTYPE;

do $$

declare
select_emp_val  emp_details%rowtype;
begin

select * from emp_details
into select_emp_val
where emp_id=2 ;

raise notice 'This is name of employee id:- % name:- % email:- %',
select_emp_val.emp_id,
select_emp_val.emp_name,
select_emp_val.emp_email;
end ;$$


PL/pgSQL record examples

PostgreSQL provides a “type” called the record that is similar to the row-type.

do $$
declare
rec record ;
begin

    select emp_id,emp_name,emp_email
    into rec 
    from emp_details where emp_id=2;

    raise notice 'This is record of file % % %', rec.emp_id,rec.emp_name,rec.emp_email;
   
end;
$$

language plpgsql;

 

Using record variables in the for loop statement

do $$
declare
rec record ;
begin
    for rec in  select emp_id,emp_name,emp_email
    from emp_details
    where emp_id < 7
loop
    raise notice 'This is record of file % % %', rec.emp_id,rec.emp_name,rec.emp_email;
end loop;   
end;
$$
language plpgsql;

PL/pgSQL constants example

do $$
declare
 pi constant numeric :=3.14 ;
 net_price numeric := 20.5;
 begin

 raise notice 'This is file %',net_price * ( pi +1);
 
 end;

 $$

NOTICE:  This is file 84.870

Now, if you try to change the value of the constant as follows:

do $$
declare
 pi constant numeric :=3.14 ;
 net_price numeric := 20.5;
 begin

 raise notice 'This is file %',net_price * ( pi +1);
 pi:=10;
 

end; $$

ERROR:  "pi" is declared CONSTANT
LINE 8:  pi:=10;
         ^
********** Error **********

1) PL/pgSQL if-then statement

do $$
declare
   select_emp_details emp_details%rowtype;
   input_id emp_details.emp_id%type :=2;
begin
   select *  from emp_details into select_emp_details  where emp_id=input_id;
    if not found then
        raise notice 'The employee % % could not found',input_id ,select_emp_details.emp_name;
       
   end if ;
end $$;

2) PL/pgSQL if-then-else statement

do $$
declare
   select_emp_details emp_details%rowtype;
   input_id emp_details.emp_id%type :=2;
begin
   select *  from emp_details into select_emp_details  where emp_id=input_id;
    if not found then
        raise notice 'The employee % % could not found',input_id ,select_emp_details.emp_name;
        else
        raise notice 'The employee % id  name %',input_id ,select_emp_details.emp_name;
   end if ;
  
end $$;

 PL/pgSQL if-then-elsif Statement

Table Name :- emp_details

do $$
declare
    v_emp_value emp_details%rowtype ;
    v_emp_name varchar(100);
begin
SELECT * FROM emp_details into v_emp_value where emp_id=5;
if not found then
raise notice 'Emp details is not found';
else
    if v_emp_value.emp_id=2 then
        raise notice '%',v_emp_value.emp_name;
        elsif  v_emp_value.emp_id=3 then
        raise notice '%',v_emp_value.emp_name;
        elsif  v_emp_value.emp_id=4 then
        raise notice '%',v_emp_value.emp_name;
        else
            raise notice '%',v_emp_value.emp_name;
    end if ;
end if;
end $$