Functions and triggers

--functions
Create or replace function myCube(num Integer)
Returns integer
As $$
Begin
RETURN num * num * num;
End;
$$ language plpgsql;



Select myCube(5);

create or replace function employeeschema.donationpercentages
(amount numeric, percentage numeric)
Returns numeric
AS $$
Begin
--semicolons required
Return amount * percentage;
End;
$$ language plpgsql;

select donationkey, donationamount, 
employeeschema.donationpercentages(donationamount, .76)"to charity"
From donation;

select donationkey, donationamount, 
employeeschema.donationpercentages(donationamount, 76)"to charity"
From donation;

create orreplace function employeeschema.donationpercentages
(amount numeric, percentage numeric)
Returns numeric
AS $$
Begin
if percentage > 1
   Then percentage = percentage/100;
end if;
--semicolons required
Return round((amount * percentage),2);
End;
$$ language plpgsql;

Create or replace function makeusername(firstname text, lastname text)
returns text
AS $$
Begin
Return Lower(substring(firstname,1,1)|| lastname); 
end;
$$ language plpgsql;

Select personfirstname, personlastname, 
makeusername(personfirstname, personlastname) as username
From person;

create schema donorschema;

Create or replace function donorschema.getdonations
(donorkey integer)
Returns table
(
  "Date" timestamp,
  amount numeric,
  validation UUID
)
AS $$
BEGIN
Return query
Select donationdate, donationamount, donationvalidation
From donation
Where personkey=donorkey;
End;
$$ language plpgsql;
Select * from donation;

Select * from donorschema.getdonations(6);

Alter table person
add personannouncements boolean default true;

Create or replace function addperson
(
 firstname text,
 lastname text,
 email text,
 phone text,
 announce boolean,
 apartment text,
 street text,
 city text,
 "state" char(2),
 zipcode char(11),
 passwd varchar(50)
) returns void
as $$
Insert into person(
personfirstname, personlastname, personemail,
personprimaryphone, persondateadded, personannouncements)
values(firstname, lastname, email, phone, 
current_timestamp, announce);

Insert into personaddress(personkey, personaddressstreet, 
personaddresscity, personaddressstate, personaddresszipcode,
personaddressapt)
values(currval('person_personkey_seq'), street, city, 
"state",zipcode, apartment);

Insert into logintable(personkey, personusername, personpassword)
Values(currval('person_personkey_seq'), makeusername(firstname, lastname),
createpassword(passwd));
$$ language sql;

Select addperson('Joseph,','Rogers', 'jrogers@gmail.com',
'2065552345', True, '641', '222 8th avenue', 'Seattle', 'WA',
'98100', 'RogersPass');

Select * from person where personkey =138
Select * from personaddress where personkey=138;
Select * from logintable where personkey=138;

Create or replace function updateperson
(
 pkey integer,
 firstname text,
 lastname text,
 email text,
 phone text,
 announce boolean,
 apartment text,
 street text,
 city text,
 "state" char(2),
 zipcode char(11)
 
) returns void
as $$
Update person
set personlastname=lastname,
personfirstname=firstname,
personemail=email,
personprimaryphone=phone,
personannouncements=announce
Where personkey=pkey;

update personaddress
Set personaddressstreet=street,
personaddresscity=city,
personaddressstate="state",
personaddresszipcode=zipcode,
personaddressapt=apartment
where personkey = pkey;

$$ language sql;
      
Select updateperson(138,'Joseph,','Rogers', 'jrogers@gmail.com',
'2065552345', False, '641', '222 8th avenue', 'Bellevue', 'WA',
'98100');

alter table grantapplication
add isflagged boolean default False; 
      
Create or replace function flagrequest()
Returns trigger as
$Body$
Begin
If NEW.grantapplicationamount >
   (Select granttypeonetimemax
    from granttype
    where granttypekey = new.granttypekey)
Then
 Update grantapplication
    Set isflagged =True
    where grantapplicationkey=new.grantapplicationkey;
end if;
Return new;
End;
$Body$ language plpgsql;

Create trigger flag_over_amounts
After Insert
on grantapplication
For each row
Execute procedure flagrequest();
      
Insert into grantapplication(Grantapplicationdate, personkey, granttypekey,
    grantapplicationamount, grantapplicationexplain)
     values(current_timestamp, 138, 1, 150.00, 'hungry');
      
Select * from grantapplication where grantapplicationkey = 
      (select max(grantapplicationkey) from grantapplication);