1. Additional video

1.1 Aggregate

--Aggregate to count all values (even nulls)

CREATE FUNCTION
    oneMore(sum int, x integer) RETURNS integer
AS $$
BEGIN
    if x is null then
        return sum + 1;
    else
        return sum + 1;
    end if;
END;
$$ LANGUAGE plpgsql;

CREATE AGGREGATE countAll(integer)(
    stype = integer, --the accumulator type
    initcond = 0, --initial accumulator value
    sfunc = oneMore --newstate function
); 



--pizza example
SELECT p.name as pizza, list(t.name)
FROM Pizzas p
    JOIN Has h ON p.id = h.pizza
    JOIN Toppings t ON t.di = h.Toppings
GROUP BY p.name;
--aggregate to concat strings, using '|' as separator
CREATE OR REPLACE FUNCTION 
    append(soFar text, item text) RETURNS text
AS $$
BEGIN
    if soFar = '' then
        return item;
    else 
        return soFar||'|'||item;
    end if;
END;
$$ LANGUAGE plpgsql

DROP AGGREGATE if EXISTS list(text);  --重要Ｊ行洹！

CREATE AGGREGATE list(text)(
    stype = text, --the accumulator type
    initcond = '', --initial accumulator value
    sfunct = append --newstate function
);

1.2 Trigger

--Maintain count of pizzas available in each store

drop trigger if exists maintainVcount on SoldIn;

create or replace function
    changeVarieties() returns trigger
as $$
begin
    if TG_OP = 'INSERT' then
        update Stores
        set varieties = varieties + 1
        where id = NEW.stroe;
    elsif TG_OP = 'DELETE' then
        update Stores
        set varieties = varieties - 1
        where id = OLD.store;
    else
        raise exception 'changeVarieties() failed';
    end if;
    return null;
end;
$$ language plpgsql;


create trigger maintainVcount 
after insert or delete on SoldIn
for each row
execute procedure changeVarieties();

2. Trigger

PostgreSQL的syntax如下：

CREATE TRIGGER TriggerName
{AFTER|BEFORE} Event1 [OR Event2 ...]
--After一般用于update，before用于insert
[ WHEN (Condition)]
FOR EACH {ROW|STATEMENT}
--Row每次都修改豹芯，Statement是所有執(zhí)行完后統(tǒng)一修改勇边。假設(shè)有1million數(shù)據(jù)要修改请唱，最好用statement讯赏，是在trigger運(yùn)行結(jié)束后統(tǒng)一操作义黎，而row的話每條數(shù)據(jù)都要執(zhí)行一次润文，效率較低姐呐。
EXECUTE PROCEDURE FunctionName(args ...);

2.1 例1

Consider a database of people in the USA, ensure that only valid state codes are used.

CREATE TABLE Person(
  id integer,
  ssn varchar(11) unique,
  ...
  state char(2),
  primary key (id)
);
CREATE TABLE States(
  id integer,
  code char(2) unique,
  ...
  primary key (id)
);

TRIGGER:

CREATE FUNCTION CheckState() RETURNS TRIGGER
AS $$
BEGIN
  new.state = upper(trim(new.state))
  --trim()用來(lái)去除多余雜項(xiàng)，這里去除的是空格典蝌，之后轉(zhuǎn)換為upper case
  if (new.state !~ '^[A-Z][A-Z]$') then
    raise exception 'Code must be two alpha chars';
    --檢查格式是否正確
  end if;
  select * from States where code = new.state;
  --檢查state是否在state的table中曙砂，是否legal
  if (not found) then
    raise exception 'Invalid code %', new.state;
    --用%來(lái)替代后面的variable，這里是new.state
  end if;
  return new;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER CheckState BEFORE insert or update
ON Person for each ROW EXECUTE PROCEDURE checkstate();

2.2 例2

department salary totals changes with individual salary change.
需要保證下面的情況成立

CREATE ASSERTION TotalSalary CHECK(
  not exists (
    select d.id from Department d
    where d.totSal <>
      (select sum(e.salary)
      from Employee e
      where e.dept = d.id)
  )
)

TRIGGER1: new employees arrive

CREATE FUNCTION totalSalary1() RETURNS TRIGGER
AS $$
BEGIN
  if (new.dept is not null) then
    update Department
    set totSal = totSal + new.salary
    where Department.id = new.dept;
  end if;
  return new;
END;
$$ LANGUAGE plpgsql;

create trigger TotalSalary1 
after insert on Employees
for each row execute procedure totalSalary1();

TRIGGER2: employees change departments/salaries

create function totalSalary2() returns trigger
as $$
begin
  update Department
  set totSal = totSal + new.salary
  where Department.id = new.dept;
  update Department
  set totSal = totSal - old.salary
  where Department.id = old.dept;
  return new;
end;
$$ language plpgsql;

create trigger TotalSalary2 
after update on Employee
for each row execute procedure totalSalary2();

TRIGGER3: employees leave

create function totalSalary3() returns trigger
as $$
begin
  if (old.dept is not null) then
    update Department
    set totalSal = totalSal - old.salary
    where Department.id = old.dept;
  end if;
  return old;
end;
$$ language plpgsql;

create trigger TotalSalary3
after delete on Employee
for each row execute procedure totalSalary3();

3. Database Programming

大多數(shù)常見(jiàn)語(yǔ)言都可以操作數(shù)據(jù)庫(kù)骏掀，如C的libpq鸠澈，Java的JDBC(Java Database Connectivity)。常見(jiàn)的調(diào)用操作數(shù)據(jù)庫(kù)的結(jié)構(gòu)如下：

db = connect_to_dbms(DBname, User/Password);
query = build_SQL("SqlStatementTemplate", values);
results = execute_query(db, query);
while (more_tuples_in(results))
{
  tuple = fetch_row_from(results);
  ...
}

以PHP為例：

$db = dbConnect("dbname = myDB");
...
$query = "select a,b,c from R where c >= %d";
$result = dbQuery($db, mkSQL($query, $min);
while ($tuple = dbNext($result)){
  $tmp = $tuple["a"] - $tuple["b"] - $tuple["c"]
  ...
  list($a, $b, $c) = $tuple;
  $tmp = $a - $b - $c;
}
...