e.g. 1
--how many varieties of pizza each store sells
create or replace function
nvarieties(_store_id integer) returns integer
as $$
declare
nv integer;
begin
select count(*) into nv
from Stores t
join Suburbs s on t.located_in = s.id
join soldin si on si.store = t.id
where t.id = _store_id;
return nv;
end;
$$ language 'plpgsql';
select nvarieties(100);
-- a view to give #varieties per store
create view storeInfo(location, varieties)
as
select s.name, nvarieties(t.id)
from Stores t
join Suburbs s on t.located_in = s.id;
-- a more efficient view to give #varieties per store
create view storeInfo(location, varieties)
as
select s.name,t.varieties
from Stores t
join Suburbs s on t.located_in = s.id;
第二个View没有调用function（少了一些nvarieties里的sub-query）
--存放这个varieties有一些冗余，并且数据可能会过时
--add a new column to hold #varieties per store
alter table Stores add column varieties integer;
--sets this value up initially
--在空的column中加入数据
update Stores set varieties=nvarieties(id);
1.修改表结构alter table
alter table table_name add column column_name column_type;
2.更新表数据 update table
update table_name set column_name=value;
Trigger 引入
drop trigger if exists maintainVcount on SoldIn;
create trigger maintainVcount
after insert or delete on SoldIn
for each row
execute procedure changeVarieties();
create or replace function
changeVarieties() returns trigger
as $$
begin
if TG_OP = 'INSERT' then
update Stores
set varieties = varieties + 1
where id = NEW.Store;
elseif TG_OP = 'DELETE' then
update Stores
set varieties = vartieies -1
where id = OLD.store;
else
raise exception ' changeVarieties() failed'
end if;
return null;
end;
$$ language = 'plpgsql'
exists 函数：sql built-in函数，用于判断表/sub-query中条目存在性
单独使用，如果无条目为False，否则为True
on SoldIn: trigger在表SoldIn的Event中 激活
After
slide上的解释：NEW contains current value and OLD contains the previous value of changed tuple
John Sherpherd: We are not
for each row: 使用each，自动在使用group by
