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沒有調(diào)用function（少了一些nvarieties里的sub-query）
--存放這個varieties有一些冗余量淌，并且數(shù)據(jù)可能會過時
--add a new column to hold #varieties per store
alter table Stores add column varieties integer;
--sets this value up initially
--在空的column中加入數(shù)據(jù)
update Stores set varieties=nvarieties(id);
1.修改表結(jié)構(gòu)alter table
alter table table_name add column column_name column_type;
2.更新表數(shù)據(jù) 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 函數(shù)：sql built-in函數(shù)吏砂，用于判斷表/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
