1. Additional video from last week

--1.Functions returning single atomic value without any database access (immutable)
--SQL简单函数
CREATE OR REPLACE FUNCTION
    add2a(a integer, b integer) RETURNS integer
AS $$
    SELECT a + b;
$$ LANGUAGE 'sql';
--语言这里的sql是区分大小写的
--使用如下
SELECT add2a(3, 5);
 add2a
-------
     8
(1 row)

--PLpgsql简单函数
CREATE OR REPLACE FUNCTION
    add2b(a integer, b integer) RETURNS integer
AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE 'plpgsql';
--语言这里的plpgsql同样是区分大小写的
--plpgsql写法是以BEGIN开始，END;结束
--使用如下
SELECT add2b(3, 5);
 add2b
-------
     8
(1 row)


--2.Functions returning a single tuple with no database access
CREATE TYPE Pair AS (x integer, y integer);

CREATE OR REPLACE FUNCTION
    mkpair(a integer, b integer) RETURNS Pair
AS $$
DECLARE 
    p Pair;
BEGIN
    p.x := a;
    p.y := b;
    return p;
END;
$$ LANGUAGE 'plpgsql';
--使用如下
SELECT mkpair(3, 4);
 mkpair
--------
 (3,4)
(1 row)
SELECT * FROM mkpair(3, 4);
 x | y
---+---
 3 | 4
(1 row)


--3.Fuction returning a set of atomic values (SETOF)
CREATE OR REPLACE FUNCTION
    seq(hi integer) RETURNS setof integer
AS $$
DECLARE
    i integer;
BEGIN
    i := 1;
    while(i <= hi) loop
        return next i;
        i := i + 1;
    end loop;
    return;
END;
$$ LANGUAGE 'plpgsql';
--plpgsql使用古老的语言风格，assign是:=，循环不使用括号，而是用loop + end loop;
--使用如下
SELECT * FROM seq(5);
 seq
-----
   1
   2
   3
   4
   5
(5 rows)


--4.Function returning a set of tuples (i.e. table)
CREATE OR REPLACE FUNCTION
    squares(hi integer) RETURNS SETOF Pair
AS $$
DECLARE
    i integer;
    p Pair;
BEGIN
    FOR i in 1..hi loop
        p.x := i;
        p.y := i * i;
        return next p;
    end loop;
    return;
END;
$$ LANGUAGE 'plpgsql';
--使用如下
SELECT * FROM squares(3);
 x | y
---+---
 1 | 1
 2 | 4
 3 | 9
(3 rows)
SELECT x AS "n", y as "n^2" FROM squares(5);
 n | n^2
---+-----
 1 |   1
 2 |   4
 3 |   9
 4 |  16
 5 |  25
(5 rows)


--5.Function that reads from the database and returns a set of atomic values
CREATE OR REPLACE FUNCTION
    pizzaWith(_topping text) RETURNS setof text
AS $$
DECLARE
    _p Pizzas;
BEGIN
    for _p in 
        SELECT p.*
        FROM Pizzas p
            JOIN Has h ON h.pizza = p.id
            JOIN Toppings t ON h.topping = t.id
        WHERE t.name = _topping
    loop
        return next _p.name;
    end loop;
END;
$$ LANGUAGE 'plpgsql';
--local variable命名以_开始，以区分database和local variable
--使用如下
SELECT * FROM pizzaWith('cheddar');

--6.Function that reads from the database and returns a set of tuples (i.e. a table)
CREATE OR REPLACE FUNCTION
    pizzaWith1(_topping text) RETURNS setof Pizzas
AS $$
DECLARE
    _p Pizzas;
BEGIN
    for _p in 
        SELECT p.*
        FROM Pizzas p
            JOIN Has h ON h.pizza = p.id
            JOIN Toppings t ON h.topping = t.id
        WHERE t.name = _topping
    loop
        return next _p;
    end loop;
END;
$$ LANGUAGE 'plpgsql';
--使用如下
SELECT * FROM pizzaWith1('cheddar');

2. Extending SQL

通常可以创建以下一些类型，一些可以用sql实现，但是很多用extending方式更加容易：

2.1 new data types

可以用sql实现，例如：

create domain Positive as integer check (value > 0);
create type Rating as enum ('poor','ok','excellent');
create type Pair as (x integer, y integer);

2.2 more operations/aggregates for use in queires

2.2.1 循环不可以用sql实现，例如：

-- Factorial functions

-- iterative (while)
create or replace function
    fac(n integer) returns integer
as $$
declare
    i integer;
    f integer := 1;
begin
    if (n < 1) then
        raise exception 'Invalid fac(%)',n; 
        --n 表示string中%的位置的value
    end if;
    i := 2;
    while (i <= n) loop
        f := f * i;
        i := i + 1;
    end loop;
    return f;
end;
$$ language plpgsql;


-- iterative (for)
create or replace function
    facc(n integer) returns integer
as $$
declare
    i integer;
    f integer := 1;
begin
    if (n < 1) then
        raise exception 'Invalid fac(%)',n;
    end if;
    for i in 2 .. n loop 
    -- ..表示2 to n
        f := f * i;
    end loop;
    return f;
end;
$$ language plpgsql;


-- recursive
create or replace function
    facr(n bigint) returns bigint
as $$
begin
    if (n < 1) then
        raise exception 'Invalid fac(%)',n;
    elsif (n = 1) then
        return 1;
    else
        return n * facr(n-1);
    end if;
end;
$$ language plpgsql;

2.2.2 现代query types

除了之前文章提到的select/project/join, aggregation, grouping的query方式，现代很多query分为两种类型：

2.2.2.1 recursive，用来manage hierarchies, graphs

定义如下：

with recursive T(a1, a2, ...) as
(
    non-recursive select
  union
    recursive select involving T
)
select ... from T where ...

要求：
The subqueries generating T cannot be arbirtrary
（1）non-recursive select:
does not refer to T
generates an initial set of tuples for T (initialisation)
（2）recursive select：
must be a query involving T
must include a where condition
must eventually return an empty result (termination)
例如：

with recursive nums(n) as (
    select 1
  union
    select n+1 from nums where n < 100
)
select sum(n) from nums;
-- which produces ...
 sum  
------
 5050

2.2.2.2 window，用来spread group-by summaries

将下例的分组用一个tuple显示出来

select student, avg(mark)
from   CourseEnrolments
group  by student;

 student  |  avg         
----------+-------
 46000936 | 64.75
 46001128 | 73.50

下例 attach student's average mark to each enrolment，table中不存在avg这一列，但是用window的方式可以实现在每列数据后都加入该学生的avg。

select *, avg(mark)
over   (partition by student)
--over相当于在哪里加入window信息，这里是在每一个student后面加入avg(mark）的信息
from   CourseEnrolments;

 student  | course | mark | grade | stueval |  avg         
----------+--------+------+-------+---------+-------
 46000936 |  11971 |   68 | CR    |       3 | 64.75
 46000936 |  12937 |   63 | PS    |       3 | 64.75
 46000936 |  12045 |   71 | CR    |       4 | 64.75
 46000936 |  11507 |   57 | PS    |       2 | 64.75
 46001128 |  12932 |   73 | CR    |       3 | 73.50
 46001128 |  13498 |   74 | CR    |       5 | 73.50
 46001128 |  11909 |   79 | DN    |       4 | 73.50
 46001128 |  12118 |   68 | CR    |       4 | 73.50

练习题：Using window functions, write an SQL function to find students whose mark is < 60% of average mark for course.

create or replace function
    under(integer) returns setof CourseEnrolments
as $$
select student,course,mark,grade,stueval
from   CourseAverages
where  course = $1 and mark < 0.6*avg
$$
language sql stable;
--stable含义: access the database without change it

-- Generate the CourseAverages table using window function

create view CourseAverages as
select student,course,mark,grade,stueval,avg(mark)
over   (partition by course)
from   CourseEnrolments;
--注意：CourseEnrolments的avg(mark)是一个学生所有学科的平均分，21行的partition by course则是根据课程的平均分

2.2.3 With queries

为了实现一个复杂的查询，往往需要中间有很多小查询，但往往并不需要这些中间查询永久存在，所以可以用with queiries来临时创建中间的view或其他步骤。例如：

with V as (select a,b,c from ... where ...),
     W as (select d,e from ... where ...)
select V.a as x, V.b as y, W.e as z
from   V join W on (v.c = W.d);
--或者
select V.a as x, V.b as y, W.e as z
from   (select a,b,c from ... where ...) as V,
       (select d,e from ... where ...) as W
where  V.c = W.d;

2.2.4 Aggregates

Aggregates reduce a collection of values into a single result.例如count(tuples), sum(numbers)

AggState = initial state
for each item V {
    # update AggState to include V
    AggState = newState(AggState, V)
}
return final(AggState)

例如，table R是一系列a,b,c tuple组成的table，使用aggragate可以显示精简后的信息，如sum和count：

--table R
a | b | c      
---+---+---
 1 | 2 | x       a | sum | count
 1 | 3 | y      ---+-----+-------
 2 | 2 | z       1 |   5 |     2
 2 | 1 | a       2 |   6 |     3
 2 | 3 | b

select a,sum(b),count(*) from R group by a

 a | sum | count
 ---+-----+-------
 1 |   5 |     2
 2 |   6 |     3

user-defined aggregates:
BaseType ... type of input values
StateType ... type of intermediate states
state mapping function: sfunc(state,value) → newState
[optionally] an initial state value (defaults to null)
[optionally] final function: ffunc(state) → result

CREATE AGGREGATE AggName(BaseType) (
    sfunc     = NewStateFunction,
    stype     = StateType,
    initcond  = InitialValue,  --optional
    finalfunc = FinalResFunction, --optional
    sortop    = OrderingOperator  --optional
);

例如：

create aggregate myCount(anyelement) (
    stype    = int,    -- the accumulator type
    initcond = 0,      -- initial accumulator value
    sfunc    = oneMore -- increment function
);

create function
    oneMore(sum int, x anyelement) returns int
as $$
begin return sum + 1; end;
$$ language plpgsql;

例如：

create type IntPair as (x int, y int);

create function
    AddPair(sum int, p IntPair) returns int
as $$
begin return p.x + p.y + sum; end;
$$ language plpgsql;

create aggregate sum2(IntPair) (
    stype     = int,
    initcond  = 0,
    sfunc     = AddPair
);

练习题：Define a concat aggregate that takes a column of string values and returns a comma-separated string of values

select count(*), concat(name) from Employee;
-- returns e.g.
  count |         concat
 -------+----------------------
      4 | John,Jane,David,Phil

2.3 more powerful constraint checking

包括attribute constraint，例如

age     integer check (age > 15),

relation constraint和referential integrity，例如

create table Employee (
   id      integer primary key,
   name    varchar(40),
   salary  real,
   age     integer check (age > 15),
   worksIn integer
              references Department(id),
   constraint PayOk check (salary > age*1000)
);

除此之外，还可以用assertion，但是assertion非常耗时耗能，每次改变table都一定要执行assertion，谨慎使用。

CREATE ASSERTION name CHECK (condition)

例如：

create assertion ClassSizeConstraint check (
   not exists (
      select c.id from Courses c, CourseEnrolments e
      where  c.id = e.course
      group  by c.id having count(e.student) > 9999
   )
);

create assertion AssetsCheck check (
   not exists (
      select branchName from Branches b
      where  b.assets <>
             (select sum(a.balance) from Accounts a
              where a.branch = b.location)
   )
);

2.4 event-based triggered actions

Triggers are procedures stored in the database and activated in response to database events (e.g. updates).

Triggers provide event-condition-action (ECA) programming:
--an event activates the trigger
--on activation, the trigger checks a condition
--if the condition holds, a procedure is executed (the action)

CREATE TRIGGER TriggerName
{AFTER|BEFORE}  Event1 [ OR Event2 ... ]
[ FOR EACH ROW ]
ON TableName
[ WHEN ( Condition ) ]
Block of Procedural/SQL Code ;

Triggers can be activated BEFORE or AFTER the event.
If activated BEFORE, can affect the change that occurs:
--NEW contains "proposed" value of changed tuple
--modifying NEW causes a different value to be placed in DB
If activated AFTER, the effects of the event are visible:
--NEW contains the current value of the changed tuple
--OLD contains the previous value of the changed tuple
--constraint-checking has been done for NEW

Note: OLD does not exist for insertion; NEW does not exist for deletion.

例如insert：

create trigger X before insert on T Code1;
create trigger Y after insert on T Code2;
insert into T values (a,b,c,...);

sequence of events:
1.execute Code1 for trigger X
2.code has access to (a,b,c,...) via NEW
3.code typically checks the values of a,b,c,..
4.code can modify values of a,b,c,.. in NEW
5.DBMS does constraint checking as if NEW is inserted
6.if fails any checking, abort insertion and rollback
7.execute Code2 for trigger Y
8.code has access to final version of tuple via NEW
9.code typically does final checking, or modifies other tables in database to ensure constraints are satisfied

Reminder: there is no OLD tuple for an INSERT trigger.

例如update：

create trigger X before update on T Code1;
create trigger Y after update on T Code2;
update T set b=j,c=k where a=m;

sequence of events:
1.execute Code1 for trigger X
2.code has access to current version of tuple via OLD
3.code has access to updated version of tuple via NEW
4.code typically checks new values of b,c,..
5.code can modify values of a,b,c,.. in NEW
6.do constraint checking as if NEW has replaced OLD
7.if fails any checking, abort update and rollback
8.execute Code2 for trigger Y
9.code has access to final version of tuple via NEW
10.code typically does final checking, or modifies other tables in database to ensure constraints are satisfied

Reminder: both OLD and NEW exist in UPDATE triggers.

例如delete：

create trigger X before delete on T Code1;
create trigger Y after delete on T Code2;
delete from T where a=m;

sequence of events:
1.execute Code1 for trigger X
2.code has access to (a,b,c,...) via OLD
3.code typically checks the values of a,b,c,..
4.DBMS does constraint checking as if OLD is removed
5.if fails any checking, abort deletion (restore OLD)
6.execute Code2 for trigger Y
7.code has access to about-to-be-deleted tuple via OLD
8.code typically does final checking, or modifies other tables in database to ensure constraints are satisfied

Reminder: tuple NEW does not exist in DELETE triggers.

Triggers in PostgreSQL:

CREATE TRIGGER TriggerName
{AFTER|BEFORE}  Event1 [OR Event2 ...]
ON TableName
[ WHEN ( Condition ) ]
FOR EACH {ROW|STATEMENT}
EXECUTE PROCEDURE FunctionName(args...);