RDBMS-Stored Functions

 Assignment 2 - Stored Functions

Stored functions are user defined functions, that are created using the CREATE FUNCTION 

statement. The functions, thus created, are called stored functions because they are stored as 

database objects within the PostgreSQL database. The CREATE FUNCTION command names 

the new function, states its arguments and return type. 

Creating a stored function: 

Syntax:-

CREATE [OR REPLACE] FUNCTION function_name (arguments) 

RETURNS return_datatype AS $variable_name$

   DECLARE

      declaration;

      [...]

   BEGIN

      < function_body >

      [...]

      RETURN { variable_name | value }

   END; LANGUAGE plpgsql;

Where,

function-name specifies the name of the function.

[OR REPLACE] option allows modifying an existing function.

The function must contain a return statement.

RETURN clause specifies that data type you are going to return from the function. The return_datatype can be a base, composite, or domain type, or can reference the type of a table column.

function-body contains the executable part.

The AS keyword is used for creating a standalone function.

plpgsql is the name of the language that the function is implemented in. Here, we use this option for PostgreSQL, it Can be SQL, C, internal, or the name of a user-defined procedural language. For backward compatibility, the name can be enclosed by single quotes.

In PostgreSQL, both stored procedures and user-defined functions are created with CREATE FUNCTION statement. There are differences between the notion of stored procedures and functions in database systems:

	Stored Procedure	Function
Use in an expression
Return a value
Return values as OUT parameters
Return a single result set		(as a table function)
Return multiple result sets

So in most cases, the purpose of a stored procedure is to:

• Perform actions without returning any result (INSERT, UPDATE operations i.e.)
• Return one or more scalar values as OUT parameters
• Return one or more result sets

Usually the purpose of a user-defined function is to process the input parameters and return a new value.

Attributes: 

PL/pgSQL provides variable attributes that basically assists or helps the database programmer , when working with database objects. These attributes are %TYPE and %ROWTYPE. 

The TYPE attribute : Used to declare a variable with the data type of a referenced database 

object ( a table column). 

Syntax :Variable_nametable_name.column_name%TYPE ; 

The %ROWTYPE attribute : Used to declare a PL/pgSQL record variable to have the same 

structure as the rows in a table , that we specify in the function block. 

Syntax :Variable_nametable_name%ROWTYPE;

Flow Control Satement Syntax :-

1 The IF/THEN statement 

If < Condition> Then 

 statement; 

 [..] 

Else 

 Statement;

 [..] 

End if;

2. Nested If-else if 

If Then 

 Statement; 

 [..] 

Else if Then 

 Statement; 

 [….] 

Else if Then 

Statement; 

[……] 

Else 

Statement; 

[….] 

End if; 

End if; 

End if;

3.Loops

 LOOP 

Statement; 

[………] 

EXIT [label] WHEN 

END LOOP;

4. While Loop 

While Condition 

 Loop

 Statement; 

 […….] 

 End loop;

5.For Loop

For Loop–Index In {Reverse} expression1 ..... expression2 

 Loop 

 Statement; 

 [……..] 

 End loop;

6. For Loop (iterate through a query resultset)

For {record_variable | %rowtype_variable } IN 

select_statement

 LOOP 

 Statement; 

 [……….] 

 END LOOP

SET A

Using If.-Then-else, case, for, while and unconditional loops

1. Find maximum and minimum from two numbers 

 create or replace function max_min (x int , y int )returns int as $$

declare 

max int; 

min int;

begin

if x>y then

max:=x; 

min:=y;

else 

max:=y; 

min:=x;

end if; 

raise NOtice ' Max:=% Min:=% ',max,min;

return 1;

end;

$$ language plpgsql;

 select max_min(10,5);

NOTICE:   Max:=10 Min:=5 

 max_min 

---------

       1

(1 row)

postgres=# select max_min(2,5);

NOTICE:   Max:=5 Min:=2 

 max_min 

---------

       1

(1 row)

2. Check the number is positive, negative or zero.

create or replace function chk_no (n int )returns int as $$

begin

if n>0 then

raise Notice ' % is Positive',n;

else if n<0 then

raise Notice ' % is Negative',n;

else 

raise Notice ' % is Zero',n;

end if; 

end if;

return 1;

end;

$$ language plpgsql;

postgres=# select chk_no(5);

NOTICE:   5 is Positive

 chk_no 

--------

      1

(1 row)

postgres=# select chk_no(-2);

NOTICE:   -2 is Negative

 chk_no 

--------

      1

(1 row)

postgres=# select chk_no(0);

NOTICE:   0 is Zero

 chk_no 

--------

      1

(1 row)

3. Find maximum and minimum from three numbers

create or replace function max_min (x int , y int, z int )returns int as $$

declare 

max int; 

min int;

begin

if x>y AND x>z then

max:=x; 

else if y>x AND y>z then 

max:=y; 

else

max:=z;

end if; 

end if;

return max;

end;

$$ language plpgsql;

4. Find number is even or odd

create or replace function even_odd (n int )returns int as $$

declare 

rem int;

begin

rem:=n%2;

if rem=0 then

raise Notice ' % is Even',n;

else 

raise Notice ' % is Odd',n;

end if; 

return 1;

end;

$$ language plpgsql;

postgres=# select even_odd(4);

NOTICE:   4 is Even

 even_odd 

----------

        1

(1 row)

postgres=# select even_odd(9);

NOTICE:   9 is Odd

 even_odd 

----------

        1

(1 row)

5. Find sum of first 10 numbers (using unconditional loop)

CREATE OR REPLACE FUNCTION getSum() RETURNS int AS $$

DECLARE

   SUM Int := 0;

BEGIN

   FOR I IN 1 .. 10

   LOOP

      SUM := SUM + I;

   END LOOP;

   Return SUM;

END;

$$ language 'plpgsql';

CREATE FUNCTION

assignment=# select getSum();

 getsum 

--------

     55

(1 row)

6. Display all odd numbers from 1 to 50.

create or replace function oddno ()returns void as $$

declare 

rem int;

begin

FOR I IN 1 .. 50

LOOP

rem:=I%2;

if rem=1 then

raise Notice ' % is odd',I;

end if; 

End Loop;

end;

$$ language plpgsql;

CREATE FUNCTION

assignment=# select oddno();

NOTICE:   1 is odd

NOTICE:   3 is odd

NOTICE:   5 is odd

NOTICE:   7 is odd

NOTICE:   9 is odd

NOTICE:   11 is odd

NOTICE:   13 is odd

NOTICE:   15 is odd

NOTICE:   17 is odd

NOTICE:   19 is odd

NOTICE:   21 is odd

NOTICE:   23 is odd

NOTICE:   25 is odd

NOTICE:   27 is odd

NOTICE:   29 is odd

NOTICE:   31 is odd

NOTICE:   33 is odd

NOTICE:   35 is odd

NOTICE:   37 is odd

NOTICE:   39 is odd

NOTICE:   41 is odd

NOTICE:   43 is odd

NOTICE:   45 is odd

NOTICE:   47 is odd

NOTICE:   49 is odd

 oddno 

-------

 

(1 row)

7. Find sum and average of first n numbers using conditional loop(while)

CREATE OR REPLACE FUNCTION nautral_sum(num integer) RETURNS int AS $$

DECLARE

sum integer = 0; 

avg float =0.0;

i integer = 1; 

BEGIN

IF (num <= 1) THEN 

RETURN 1;

END IF;

WHILE(i <= num) LOOP

sum = sum + i; 

i = i+1;

END LOOP; 

 avg=sum/num;

raise notice 'Sum = %',sum;

raise Notice 'Avg = %',avg;

return 0;

END;

$$ LANGUAGE plpgsql;

CREATE FUNCTION

assignment=# select nautral_sum(5);

NOTICE:  Sum = 15

NOTICE:  Avg = 3

 nautral_sum 

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

           0

(1 row)

8. Count even numbers from given range(m to n) (for)

create or replace function Even_No (m int , n int )returns void as $$

declare 

rem int;

begin

FOR I IN m .. n

LOOP

rem:=I%2;

if rem=0 then

raise Notice ' % is even',I;

end if; 

End Loop;

end;

$$ language plpgsql;

CREATE FUNCTION

select Even_No(5,10);

NOTICE:   6 is even

NOTICE:   8 is even

NOTICE:   10 is even

 even_no 

---------

 

(1 row)

9. Search the given number is in given range.

create or replace function find_no (m int , n int, no int )returns int as $$

begin 

FOR I IN m .. n

LOOP

if I=no then 

 raise notice '% is found', no;

 return 1;

end if;

end loop;

raise notice '% is not found',no;

return 0;

end;

$$ language plpgsql;

CREATE FUNCTION

assignment=# select find_no(5,10,6);

NOTICE:  6 is found

 find_no 

---------

       1

(1 row)

10.Display a number in word (Using Case) and loop.

CREATE OR REPLACE FUNCTION no_word (x integer) RETURNS text AS $$ 

DECLARE

num text;

BEGIN 

CASE

        WHEN x = 0 THEN num := 'ZERO';

        WHEN x = 1 THEN num := 'ONE';

WHEN x = 2 THEN num := 'TWO';

WHEN x = 3 THEN num := 'THREE';

WHEN x = 4 THEN num := 'FOUR';

WHEN x = 5 THEN num := 'FIVE';

WHEN x = 6 THEN num := 'SIX';

WHEN x = 7 THEN num := 'SEVEN';

WHEN x = 8 THEN num := 'EIGHT';

WHEN x = 9 THEN num := 'NINE';

END CASE;

RETURN num;

END; 

$$ 

LANGUAGE plpgsql;

CREATE FUNCTION

assignment=# select no_word(5);

 no_word 

---------

 FIVE

(1 row)

SET B Bank database 

BRANCH (BID INTEGER, BRNAME CHAR (30), BRCITY CHAR (10))

 CUSTOMER (CNO INTEGER, CNAME CHAR (20), CADDR CHAR (35), CITY CHAR(20))

LOAN_APPLICATION (LNO INTEGER, LAMTREQUIRED MONEY, LAMTAPPROVED MONEY, L_DATE DATE) 

 The relationship is as follows: BRANCH, CUSTOMER, LOAN_APPLICATION are related with ternary relationship. TERNARY (BID INTEGER, CNO INTEGER, LNO INTEGER).

Queries:-

create table branch(bid int primary key,bname varchar(15),bcity varchar(15));

create table customer(cno int primary key,cname varchar(15),caddr varchar(15),city varchar(15));

create table loan_app(lno int primary key,l_amt_required money,l_amt_approved money,l_date date);

create table b_c_loan(bid int references branch(bid),cno int references customer(cno),lno int references loan_app(lno));

Insert Values :-

 insert into branch values(1,'aundh','nashik');

 insert into branch values(2,'deccon','pune');

insert into branch values(3,'m.g.road','mumbai');

 insert into branch values(4,'rk','nashik');

 insert into branch values(5,'cbs','nashik');

insert into customer values(1,'anil','as colnay','pune');

 insert into customer values(2,'sunil','as colnay','pune');

insert into customer values(3,'ajay','as colnay','pune');

 insert into customer values(4,'samadhan','mg colnay','nashik');

 insert into customer values(5,'mahesh','mg colnay','nashik');

insert into loan_app values(1,'50000','40000','02-07-2014');

insert into loan_app values(2,'60000','60000','15-07-2014');

 insert into loan_app values(3,'23000','22000','29-07-2014');

 insert into loan_app values(4,'45000','45000','23-07-2014');

 insert into loan_app values(5,'35000','34000','23-08-2014');

insert into b_c_loan values(1,1,1);

 insert into b_c_loan values(1,2,5);

 insert into b_c_loan values(1,2,4);

 insert into b_c_loan values(1,4,2);

 insert into b_c_loan values(1,3,4);

 insert into b_c_loan values(1,4,4);

 insert into b_c_loan values(1,5,2);

 insert into b_c_loan values(1,5,2);

a) Write a function that returns the total number of customers of a particular branch.( Acceptbranch name as input parameter.)

 

create function s4a_a(varchar ) returns integer as $$

declare

name alias for $1;

cnt integer :=0;

temp record;

begin

 for temp in select customer.cno,bname from customer,branch,b_c_loan where customer.cno=b_c_loan.cno and 

branch.bid=b_c_loan.bid loop

          if temp.bname=name then

                   cnt:=cnt+1;

           end if;

   end loop;

   return cnt;

 end;

$$ language plpgsql;

 

 b) Write a function to find the maximum loan amount approved

create function s4a_b() returns money as $$

declare

m money :='0';

temp record;

begin

 for temp in select l_amt_approved from loan_app loop

           if m<temp.l_amt_approved then

                   m:=temp.l_amt_approved;

           end if;

   end loop;

  return m;

 end;

 $$ language plpgsql;

 

SET B:

Using Project-Employee database

a) Write a function to accept project name as input and returns the number of employees working on the project.

b) Write a function to find the number of employees whose date of joining is before ‘03/10/2010’

 

create table project(pno int primary key,pname varchar(15),ptype varchar(15),duration int);
CREATE TABLE

 create table emp(eno int primary key,ename varchar(15),j_date date);
CREATE TABLE

 create table prog_emp(pno int references project(pno),eno int references emp(eno),s_date date);
CREATE TABLE

 

Input Values:-

insert into project values(1,'system','computer',100);
 insert into project values(2,'robotics','computer',100);
 insert into project values(3,'cloud network','computer',50);
 insert into project values(4,'microprocessor','electronics',150);
 insert into project values(5,'eye senser','electronics',250);
 insert into project values(6,'remote car','meachinical',75);

insert into emp values(1,'anil','04-04-2010');
 insert into emp values(2,'ajay','23-05-2010');
 insert into emp values(3,'nadim','18-08-2011');
 insert into emp values(4,'samadhan','28-12-2012');
 insert into emp values(5,'mahesh','28-12-2012');
 

insert into prog_emp values(1,1,'04-04-2010');
 insert into prog_emp values(1,2,'23-05-2010');
 insert into prog_emp values(2,2,'23-05-2010');
 insert into prog_emp values(3,1,'25-05-2011');
insert into prog_emp values(3,1,'25-05-2011');
insert into prog_emp values(3,4,'28-12-2012');
insert into prog_emp values(4,5,'28-12-2013');

 

Write a function to accept project name as input and returns the number of employees working on the project.

create or replace function ass2SetD_3(varchar ) returns integer as $$
declare
    name alias for $1;
    cnt integer :=0;
    temp record;
 begin
    for temp in select emp.eno,pname from emp,prog_emp,project where emp.eno=prog_emp.eno and prog_emp.pno=project.pno loop
            if temp.pname=name then
                    cnt:=cnt+1;
            end if;
    end loop;
    return cnt;
 end;
 $$ language plpgsql;

 

 Write a function which accepts employee name and prints the details of the project which the employee works on.

create or replace function ass2SetD_2(enm varchar) returns integer as $$
declare
    temp record;
 begin
   for temp in select project.pno, pname,ptype,duration ,ename from project, emp, prog_emp where project.pno=prog_emp.pno and emp.eno =prog_emp.eno  loop
            if temp.ename=enm then
                    Raise Notice ' Project No : %',temp.pno;
                    Raise Notice ' Project Name : %',temp.pname;
                    Raise Notice ' Project type : %',temp.ptype;
                    Raise Notice ' Project Duration : %',temp.duration;
            end if;
    end loop;
    return 1;
 end;
 $$ language plpgsql;

SET C
Student- Teacher database

 

STUDENT (SNO INTEGER, S_NAME CHAR(30), S_CLASS CHAR(10), S_ADDR
CHAR(50))
TEACHER (TNO INTEGER, T_NAME CHAR (20), QUALIFICATION CHAR
(15),EXPERIENCE INTEGER)
The relationship is as follows: STUDENT-TEACHER: M-M with descriptive attribute SUBJECT.

 

Queries:-

create table student(sno int primary key , s_name varchar(30), s_class varchar(10), s_addr char(50));
CREATE TABLE

 create table teacher(tno int primary key , t_name varchar(20), quali char(15), experience int);
CREATE TABLE

 create table stud_teach(sno int references student(sno), tno int references teacher(tno),subject varchar(30));
CREATE TABLE

 

Input values:-

insert into student values(1,'anil','sybcs','karanjgaon');
insert into student values(2,'ajay','sybcs','songaon');
 insert into student values(3,'nadim','sybcs','chatori');
insert into student values(4,'samadhan','sybcs','chatori');
insert into student values(5,'mahesh','sybcs','malsakore');

 insert into teacher values(1,'Borse madam','MCS,Ph.D',7);
insert into teacher values(2,'davre madam','MCS',4);
 insert into teacher values(3,'Jadhav sir','Msc',3);
insert into teacher values(4,'Bidkar sir','BCS',2);
  insert into teacher values(5,'Gawali madam','BCS',3);

 insert into stud_teach values(1,1,'RD');
 insert into stud_teach values(2,1,'SYS.PRO');
insert into stud_teach values(3,1,'RD');
 insert into stud_teach values(3,2,'DS');
 insert into stud_teach values(5,2,'DS'); 

a) Write a function to find name of the most experienced teacher for “Mathematics”.

 Create or replace function as2SetC_1() returns text as $$
declare
exp int;
temp record;
begin
 for temp in select t_name,experience from teacher ,stud_teach where teacher.tno=stud_teach.tno and subject='math' loop
           if exp<temp.experience then
                   exp:=temp.experience;
           end if;
   end loop;
  return temp.t_name;
 end;
 $$ language plpgsql;

 

b) Write a function to find the teacher teaching maximum number of subjects.

Using Simple Query :-

select t_name , count(subject) as cnt from teacher,stud_teach where teacher.tno=stud_teach.tno group by t_name;

Using Stored Function :-

create or replace function as2SetC_2() returns text as $$
declare
c int;
temp record;
begin
 for temp in select t_name , count(subject) as cnt from teacher,stud_teach where teacher.tno=stud_teach.tno group by t_name limit 1 loop
           if c<temp.cnt then
                   c:=temp.cnt;
           end if;
   end loop;
  return temp.t_name;
 end;
 $$ language plpgsql;

c) Write a function to find the number of teachers having qualification “Ph. D.”.

 Using Simple Query :-

select count(tno) from teacher where qualification like '%Ph.D%';

Using Stored Function :-
Create or Replace function as2SetC_3() returns integer as $$
declare
cnt integer :=0;
temp record;
begin
 for temp in select tno,quali from teacher loop
          if temp.quali='Ph.D' then
                   cnt:=cnt+1;
           end if;
   end loop;
   return cnt;
 end;
$$ language plpgsql;

 

SET D
Project – Employee Database

Project (pno int, pname char (30), ptype char (20), duration int)
Employee (empno int, ename char (20), joining_date date)
The relationship between Project and Employee is many to many with descriptive attribute  start_date.

Queries:-

create table project(pno int primary key,pname varchar(15),ptype varchar(15),duration int);
CREATE TABLE

 create table emp(eno int primary key,ename varchar(15),j_date date);
CREATE TABLE

 create table prog_emp(pno int references project(pno),eno int references emp(eno),s_date date);
CREATE TABLE

Input Values:-

insert into project values(1,'system','computer',100);
 insert into project values(2,'robotics','computer',100);
 insert into project values(3,'cloud network','computer',50);
 insert into project values(4,'microprocessor','electronics',150);
 insert into project values(5,'eye senser','electronics',250);
 insert into project values(6,'remote car','meachinical',75);

insert into emp values(1,'anil','04-04-2010');
 insert into emp values(2,'ajay','23-05-2010');
 insert into emp values(3,'nadim','18-08-2011');
 insert into emp values(4,'samadhan','28-12-2012');
 insert into emp values(5,'mahesh','28-12-2012');
 

insert into prog_emp values(1,1,'04-04-2010');
 insert into prog_emp values(1,2,'23-05-2010');
 insert into prog_emp values(2,2,'23-05-2010');
 insert into prog_emp values(3,1,'25-05-2011');
insert into prog_emp values(3,1,'25-05-2011');
insert into prog_emp values(3,4,'28-12-2012');
insert into prog_emp values(4,5,'28-12-2013');

a. Write a stored function to accept project type as an input and display all project names of that type.

create or replace function ass2SetD_1(ptype varchar) returns integer as $$
declare
    temp record;
 begin
   for temp in select pname, ptype from project loop
            if temp.ptype=ptype then
                    Raise Notice ' Project Name : %',temp.pname;
            end if;
    end loop;
    return 1;
 end;
 $$ language plpgsql;


b. Write a function which accepts employee name and prints the details of the project which the employee works on.

create or replace function ass2SetD_2(enm varchar) returns integer as $$
declare
    temp record;
 begin
   for temp in select project.pno, pname,ptype,duration ,ename from project, emp, prog_emp where project.pno=prog_emp.pno and emp.eno =prog_emp.eno  loop
            if temp.ename=enm then
                    Raise Notice ' Project No : %',temp.pno;
                    Raise Notice ' Project Name : %',temp.pname;
                    Raise Notice ' Project type : %',temp.ptype;
                    Raise Notice ' Project Duration : %',temp.duration;
            end if;
    end loop;
    return 1;
 end;
 $$ language plpgsql;


c. Write a function to accept project name as input and returns the number of employees working on the project.

create or replace function ass2SetD_3(varchar ) returns integer as $$
declare
    name alias for $1;
    cnt integer :=0;
    temp record;
 begin
    for temp in select emp.eno,pname from emp,prog_emp,project where emp.eno=prog_emp.eno and prog_emp.pno=project.pno loop
            if temp.pname=name then
                    cnt:=cnt+1;
            end if;
    end loop;
    return cnt;
 end;
 $$ language plpgsql;