create table a(aid int primary key,aname text, type text check (type in('urban','rural')));
 create table p(pno int primary key,name text,birthdate date, income money,aid int references a(aid));
 
 insert into a values('12','aa','rural'),('11','bb','rural'),('13','cc','urban');
 insert into p values('1','rutu','09/09/2005','9000000','11'),('2','ruhi','09/08/2005','8000000','13'),
('3','rutuja','08/09/2005','7000000','12');
select * from a;
 select * from p;
 
create or replace function r()
 returns void as'
 begin
 update p set income=income+income*''0.10'' 
 where aid in(select aid from a where type=''rural'');
 end;
 'language plpgsql;
 select r();
 select * from p;
 
 
 create or replace function t()
 returns trigger as'
 begin
 raise notice''person record is being deleted'';
 return old;
 end;
 'language plpgsql;
 
 create trigger t
 before delete on p
 for each row execute 
 procedure t();
 
 delete from p where pno='1';
 select * from p;
 
 create or replace function n()
 returns text as'
 declare 
 r p.name%type;
 c cursor for select p.name from p,a where 
 p.aid=a.aid and a.type=''urban'';
 begin
 open c;
 loop
 fetch c into r;
 exit when not found;
 raise notice ''pname=% '',r;
 end loop;
 close c;
 end;
 'language plpgsql;
 select n();