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);
     
     
      

     

     
      
     
      
   

Views and misc

Create schema employeeSchema;

Create view employeeschema.Employeeview
As
Select 
employeekey,
personfirstname firstname, 
personlastname lastname,
personemail email,
positionname,
employeepositionstartdate startdate
From employee
inner join Person
using(personkey)
inner join employeeposition
using(employeekey)
inner join jobposition
using (positionkey);

Select * From employeeschema.employeeview;
Select personfirstname, personlastname from employeeschema.employeeview;
Select firstname, lastname from employeeschema.employeeview;

--updateable
--if there are no joins, no calculated fields, 

Create view employeeschema.emailview
As
Select personlastname,
personfirstname,
personemail
From person;

Create or replace view employeeschema.emailview
As
Select personlastname,
personfirstname,
personemail,
persondateadded
From person
Where personlastname like 'J%'
with check option;

Select * from employeeschema.emailview;
Select * from person where personkey=(select max(personkey)from person);



Update employeeschema.emailview
Set personfirstname='Jason'
where personemail='jAnderson@gmail.com';

Insert into employeeschema.emailview(personlastname, personfirstname, personemail, 
          persondateadded)
values('Brown','Nelson', 'nb@hotmail.com', current_timestamp);

Create materialized view employeeschema.grantview
As
Select Grantapplicationkey, grantapplicationdate, 
granttypename, personlastname, grantapplicationamount
From person
join grantapplication
using (personkey)
join granttype
using (granttypekey);

Select * from employeeschema.grantview where grantapplicationkey =
(Select max(grantapplicationkey) from grantapplication);

Insert into grantapplication(grantapplicationdate, granttypekey, personkey, 
 grantapplicationamount, grantapplicationexplain )
values (current_timestamp, 1, 50, 400.00, 'lunch time');

refresh materialized view employeeschema.grantview;

SQL 2

SELECT * FROM pythonclub3.officer;
Use Sakila;
Select * from Actor;

Select first_name, last_name, title, description
From actor
inner join film_actor
using(actor_id)
inner Join Film
using(film_id)
Where last_name='Guiness'
And first_name='Sean'
Or title Like 'S%';

Select first_name, last_name, title, description
From actor
inner join film_actor
On actor.actor_id=film_actor.actor_id
inner Join Film
On Film.film_id=film_actor.film_id
Where last_name='Guiness';

Select * from payment
Where payment_date between '2005-06-01' and '2005-06-30';

Select * from payment where amount > 5;

Use Pythonclub3;
Insert into Member(memberlastName, memberFirstName, memberEmail, DateJoined)
Values('Jones','Dale','dj@gmail.com','2019-02-19');
Select * from member;
Insert into officermember(StartDate, member_idmember, Officer_idOfficer)
values('2019-02-20',2,2);
Insert into Location(LocationName, Address, City, zipcode, locationPhone)
values('Central','1701 Broadway','Seattle','98122', '2065551234');
Insert into meeting(MeetingDate, MeetingTime, MeetingTopic, OfficerMember_idOfficerMember, Location_idLocation)
Values('2019-02-20', '16:00:00', 'Stuff and more stuff', 2,1);

Update member
Set memberfirstname='Bill'
Where idmember=2;

Delete from member where idmember=2;

Insert into Member(memberlastName, memberFirstName, memberEmail, DateJoined)
Values('Mann','Doug','dm@gmail.com','2019-02-19'),
('Doe','jane','jd@gmail.com','2019-02-19'),
('Nelson','Anne','an@gmail.com','2019-02-19'),
('Brown','Sue','sb@gmail.com','2019-02-19');

Create and alter tables

Create table location
(
 locationkey serial primary key,
 locationname text not null,
 locationaddress text not null,
 locationcity text default 'Seattle',
 locationstate char(2) default 'WA',
 locationzip varchar(11) Not Null,
 locationphone varchar(13) Not null,
 Locationemail text not null unique,
 locationURL text

);

Create table event
(
 eventkey serial primary key,
 eventname text not null,
 eventdate Date not null,
 eventtime Time not null,
 eventdescription text,
 employeekey int references employee(Employeekey)
);

Create table locationroom
(
 locationroomkey serial,
 locationkey int,
 roomnumber int not null,
 eventkey int
);

Alter table locationroom
add primary key(locationroomkey);

Alter table locationroom
Add constraint fk_location Foreign key(locationkey)
    references location(locationkey);
 
Alter table locationroom
Add constraint fk_eventForeign Foreign Key(eventkey)
    references Event(eventkey);
 
Create table personevent
(
  personkey int not null,
  eventkey int not null
);

Alter table personevent 
Add primary key (personkey, eventkey);

Alter table personevent
Add constraint fk_personevent_person Foreign key (personkey)
   references person(personkey),
Add constraint fk_personevent_event Foreign key (eventkey)
   references event(eventkey);  
   
 Alter table person
 add announcement Boolean Default true;
 
 Select * from person;
 
 Alter table person
drop column announcement;

--temp tables
Create temp table emaillist
(
 lastname text,
 firstname text,
 email text
);

Insert into emaillist(lastname, firstname, email)
Select personlastname, personfirstname, personemail from person;

select * from emaillist;

Drop table emaillist;

Select personlastname, personfirstname, personemail 
into temp emaillist from person;