PL/pgSql

The Full Form of PL/pg Sql is  Procedural language/Postgre Sql.
It is a Structured Query language. It is Almost Similar to Sql Queries but it is more structured and more easy than the Sql Commands and functions.

Specifications of the Pl/pg Sql is as Follows:

1) Database created has no Size Limit ie it can be unlimited.
2) The table created in it can have a maximum size of  32 TB and not more than that.
3) The maximum Row Size can be maximum  upto 1.6 TB and not more than that.
4) There can be any number of rows per tables .
5) It is Highly Customizable.

Postgres Sql is available under the liberal open source license(LOSL) to download and modify,distribute and use for development. It is a powerful tool that can be used for home applications, web development, ecommerce and at places where softwares or products of high RDBMS is required.

Below is a Simple example of Postgres sql function.



 
create table Doctor
    (
        doc_no        integer        primary key    ,
        doc_name     text        not null    ,
        address        text        not null    ,
        city        text        not null   
    );

create table Hospital
    (
        hosp_no        integer        primary key    ,
        hosp_name    text        not null    ,
        hosp_city    text        not null   
    );   

create table Doc_Hosp
    (
        doc_no        integer        references Doctor    ,
        hosp_no        integer        references Hospital   
    );

insert into Doctor values(1,'Mr. Chandan','Ram krishna Nagar','Nasik');
insert into Doctor values(2,'Mr. Rahul','Gagapur road','Pune');
insert into Doctor values(3,'Mr. Amit','Ojhar','Mumbai');
insert into Doctor values(4,'Mr. Prasanna','Trimurti','Nagpur');
insert into Doctor values(5,'Mr. Atul','Colony','Kanpur');

insert into Hospital values(1,'Grand Hospital','Nasik');
insert into Hospital values(2,'Charity Hospital','Pune');
insert into Hospital values(3,'Perfect Hospital','Nagpur');
insert into Hospital values(4,'Maya Hospital','Kanpur');
insert into Hospital values(5,'Hayat Hospital','Mumbai');

insert into Doc_Hosp values(1,2);
insert into Doc_Hosp values(1,3);
insert into Doc_Hosp values(1,4);
insert into Doc_Hosp values(1,5);
insert into Doc_Hosp values(2,3);
insert into Doc_Hosp values(2,4);
insert into Doc_Hosp values(3,5);
insert into Doc_Hosp values(4,4);
insert into Doc_Hosp values(5,3);
insert into Doc_Hosp values(5,1);


a) 
create or replace function ass1()
    returns text as'

declare
    cursor1 cursor for
            select doc_no,doc_name,city
            from doctor;
   
    cursor2 cursor(city text)  for
            select hosp_no
            from hospital
            where hospital.hosp_city <> city;

    cursor3 cursor(dno integer) for
            select *
            from doc_hosp
            where doc_hosp.doc_no = dno;
       
    temp1 record;           
    temp2 record;
    temp3 record;
    flag integer;
    output text := ''\n'';

begin
   
    open cursor1;
    loop
        fetch cursor1 into temp1;
        exit when not found;   

        flag := 1;
       
        open cursor2(temp1.city);
        loop
            fetch cursor2 into temp2;   
            exit when not found;

            open cursor3(temp1.doc_no);
            loop
                fetch cursor3 into temp3;   
                exit when not found;

                if(temp2.hosp_no = temp3.hosp_no) then
                    flag := 1;
                output := output||''Match found : ''||temp2.hosp_no||''\n'';
                    exit;   
                else
                    flag := 0;
                end if;
            end loop;
            close cursor3;   -- cursor3 end

            if(flag = 0) then
                exit;
            end if;   
        end loop;
        close cursor2;  -- cursor2 end

        if(flag = 1) then
            output := output || temp1.doc_name ||''\n'';
        end if;
    end loop;
    close cursor1;    -- cursor1 end

    return output;
end;

'language plpgsql

/*
    OUTPUT 

SLIP=# select ass1();
     ass1
---------------
 
Mr. Chandan

(1 row)

*/



b)   create or replace function ass2()returns text as'

declare
    cursor1 cursor for
            select doc_no,doc_name
            from doctor;   

    cursor2 cursor(dno integer) for
            select *
            from doc_hosp
            where doc_hosp.doc_no = dno;

    temp3 hospital %rowtype;
    temp1 record;
    temp2 record;
    output text := ''\n'';

begin
    open cursor1;
    loop
        fetch cursor1 into temp1;
        exit when not found;

        output := output || temp1.doc_name || ''\n'';

        open cursor2(temp1.doc_no);
        loop
            fetch cursor2 into temp2;
            exit when not found;   

            for temp3 in select * from hospital where hospital.hosp_no = temp2.hosp_no
            loop
                 output := output ||''\t''||temp3.hosp_name||''\n'';
            end loop; -- close for temp3
        end loop;
        close cursor2; -- close cursor2
    end loop;   
    close cursor1; -- close cursor1

    return output;
end;

'language plpgsql;
/*

    OUTPUT

SLIP=# select ass2();
                                                                                                             ass2                                                                                                    
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Mr. Chandan
        Charity Hospital
        Perfect Hospital
        Maya Hospital
        Hayat Hospital
Mr. Rahul
        Perfect Hospital
        Maya Hospital
Mr. Amit
        Hayat Hospital
Mr. Prasanna
        Maya Hospital
Mr. Atul
        Perfect Hospital
        Grand Hospital

(1 row)

*/