BST operation using P/L SQL

create or replace function accumbst()
    returns void as $$
declare
    currentValue int;
    accumulatedValue int;
    cr cursor is
        select id, parentId, value from testbst
        order by id desc;   
    rw testbst%rowtype;
begin
    delete from testreportbst;   
    open cr;
    <<loop1>>
    loop
        fetch cr into rw;
        exit when not found;
        insert into testreportbst values(rw.id, rw.parentId, rw.value);
    end loop loop1;

    close cr;
    open cr;

    <<loop2>>   
    loop
        fetch cr into rw;
        exit when not found;
        select value into currentValue from testreportbst where id = cast(rw.id as text);
        select value into accumulatedValue from testreportbst where id = cast(rw.parentId as text);
        accumulatedValue := accumulatedValue + currentValue;
        update testreportbst set value = accumulatedValue where id = cast(rw.parentId as text);
    end loop loop2;      
    close cr;
end $$ language plpgsql;

Inserting data read by cursor from a table to another table using cursor

--There 4 actions in working with cursor
--declare the cursor
--open the cursor
--fetch the data from cursor and use the data in program statements
--close the cursor
create or replace function printtestbst()
    returns void as $$
declare
    c_testbst scroll cursor for
        select id, parentId, value from testbst;   
    rw testbst%rowtype;
begin
    delete from testreportbst;   
    open c_testbst;
    loop
        fetch c_testbst into rw;
        exit when not found;       
        insert into testreportbst values(rw.id, rw.parentId, rw.value, 'A');
    end loop;
      
    close c_testbst;   
end $$language plpgsql;

Inserting data from a table to another table using cursor

--There 4 actions in working with cursor
--declare the cursor
--open the cursor
--fetch the data from cursor and use the data in program statements
--close the cursor
create or replace function printtestbst()
    returns void as $$
declare
    c_testbst scroll cursor for
        select id, parentId, value from testbst;   
    rw testbst%rowtype;
begin
    delete from testreportbst;   
    open c_testbst;
    loop
        fetch c_testbst into rw;
        exit when not found;       
        insert into testreportbst values(rw.id, rw.parentId, rw.value, 'A');
    end loop;
      
    close c_testbst;   
end $$language plpgsql;

Printing data using loop and cursor

--There 4 actions in working with cursor
--declare the cursor
--open the cursor
--fetch the data from cursor and use the data in program statements
--close the cursor
create or replace function printtestbst()
    returns void as $$
declare
    c_testbst scroll cursor for
        select id, parentId, value from testbst;   
    rw testbst%rowtype;
begin
    open c_testbst;
    loop
        fetch c_testbst into rw;
        exit when not found;       
        raise notice 'print : % % %', rw.id, rw.parentId, rw.value;
    end loop;
      
    close c_testbst;   
end $$language plpgsql;

Printing the tables : using cursor

--There 4 actions in working with cursor
--declare the cursor
--open the cursor
--fetch the data from cursor and use the data in program statements
--close the cursor
create or replace function printtestbst()
    returns void as $$
declare
    c_testbst scroll cursor for
        select id, parentId, value from testbst;   
    rw testbst%rowtype;
begin
    open c_testbst;
    fetch c_testbst into rw;
    raise notice 'print : % % %', rw.id, rw.parentId, rw.value;
    fetch c_testbst into rw;
    raise notice 'print : % % %', rw.id, rw.parentId, rw.value;
    fetch c_testbst into rw;
    raise notice 'print : % % %', rw.id, rw.parentId, rw.value;
    fetch c_testbst into rw;
    raise notice 'print : % % %', rw.id, rw.parentId, rw.value;
    fetch c_testbst into rw;
    raise notice 'print : % % %', rw.id, rw.parentId, rw.value;
    fetch c_testbst into rw;
    raise notice 'print : % % %', rw.id, rw.parentId, rw.value;
    fetch c_testbst into rw;
    raise notice 'print : % % %', rw.id, rw.parentId, rw.value;
    fetch c_testbst into rw;
    raise notice 'print : % % %', rw.id, rw.parentId, rw.value;
    fetch c_testbst into rw;
    raise notice 'print : % % %', rw.id, rw.parentId, rw.value;
    fetch c_testbst into rw;
    raise notice 'print : % % %', rw.id, rw.parentId, rw.value;
    fetch c_testbst into rw;
    raise notice 'print : % % %', rw.id, rw.parentId, rw.value;
    fetch c_testbst into rw;
    raise notice 'print : % % %', rw.id, rw.parentId, rw.value;
    fetch c_testbst into rw;
    raise notice 'print : % % %', rw.id, rw.parentId, rw.value;fetch c_testbst into rw;
    raise notice 'print : % % %', rw.id, rw.parentId, rw.value;
    fetch c_testbst into rw;
    raise notice 'print : % % %', rw.id, rw.parentId, rw.value;
      
    close c_testbst;   
end $$language plpgsql;

Create table for storing query result

create table testreportbst
(
id char(4),
parentId char(4),
value int,
status varchar,
primary key(id), foreign key(id) references testbst);

Printing ascending number using loop : second example

--argument is 2 integers
--prints integers from lowest to highest

create or replace function printnumber(l int, h int)
    returns void as $$
declare
    i int;   
begin
       << loop1 >>
       for i in l..h loop
        raise notice '%', i;     
       end loop loop1;   
end $$language plpgsql;

Printing Numbers ascending

--first function
--argument is 2 integers
--prints integers from lowest to highest

create or replace function printnumber(l int, h int)
    returns void as $$
declare
    i int;   
begin
       << outer_loop >>
       FOR i IN l..h LOOP
        raise notice '%', i;     
       END loop outer_loop;   
end $$language plpgsql;

Creating function with varchar arguments

--argument is name
create or replace function printname(name varchar)
    returns varchar as $$
begin
    return 'Hello, How Are You -> ' || name;
end $$language plpgsql;

Making function for adding 2 numbers

--uses lowercase
create or replace function add2numbers(a int, b int)
    returns int as $$
begin
    return a+b;
end; $$ language plpgsql;

Dropping function

drop function hello_world()

P/L SQL example : multiply number

CREATE FUNCTION multiply2numbers(a INT, b INT)
RETURNS INT AS
BEGIN

   RETURN a * b;

END;

P/L SQL Hello World

-- hello-world.sql

CREATE FUNCTION hello_world()
RETURNS VOID AS
BEGIN

  RAISE NOTICE 'Comment Allez Vous';

END;

Insert Data

insert into testbst values(1000, 0,    0);
insert into testbst values(1100, 1000, 0);
insert into testbst values(1110, 1100, 0);
insert into testbst values(1111, 1110, 100);
insert into testbst values(1112, 1110, 100);
insert into testbst values(1200, 1000, 0);
insert into testbst values(1210, 1200, 0);
insert into testbst values(1211, 1210, 200);
insert into testbst values(1212, 1210, 200);
insert into testbst values(1220, 1200, 0);
insert into testbst values(1221, 1220, 10);
insert into testbst values(1222, 1220, 10);
insert into testbst values(1223, 1220, 10);
insert into testbst values(1224, 1220, 10);
insert into testbst values(1300, 1000, 0);
insert into testbst values(1310, 1300, 300);

Create Table

android=> create table testbst
(
id char(4),
parentId char(4),
value int,
primary key(id), foreign key(id) references testbst);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "testbst_pkey" for table "testbst"
CREATE TABLE

Create Database

postgres@debian1:/home/android$ createdb android

login

android@debian1:~$ su
Password:
root@debian1:/home/android# su postgres
postgres@debian1:/home/android$ psql
psql (9.1.18)
Type "help" for help.

postgres=# \q
postgres@debian1:/home/android$ exit
exit
root@debian1:/home/android# exit
exit
android@debian1:~$