COMP9311 Database Systems WEEK5

1. SQL Queries Exercise

Queries on Beer Database Exercise

（1）What beers are made by Toohey's?

SELECT name
FROM Beers
WHERE manf = 'Toohey''s'
;
--1.SQL中的string是单引号
--2.SQL string中的转义字符是'
--3.不要忘记SQL句尾的分号

（2）Show beers with headings "Beer", "Brewer".

SELECT name AS "Beer", manf AS  "Brewer"
FROM Beers
;

（3）Find the brewers whose beers John likes.

SELECT DISTINCT b.manf 
FROM Beers b
  JOIN  Likes l ON (b.name = l.beer)
WHERE l.drinker = 'John'
;
--由于manf不是primary key，所以并不是unique的，要加限制条件distinct来避免重复显示
--select后面的manf要加上table名字，因为from多个table，不加table名字会弄混是谁的attribute

（4）Find pairs of beers by the same manufacturer.

SELECT b1.name, b2.name
FROM Beers b1
  JOIN Beers b2 ON (b1.manf = b2.manf)
WHERE b1.name < b2.name
;
--这里要加限制条件 b1.name < b2.name，不然会出现两个一样的名字，或者两个名字调换顺序重复出现的情况

（5）Find beers that are the only one by their brewer.

SELECT name
FROM Beers
WHERE manf IN 
  (SELECT manf
  FROM Beers
  GROUP BY manf
  having count(name) = 1)
;
--select语句的嵌套
--where限制条件的IN语句
--group by分组
--having的限制条件
--distinct和group by的区分：group by要求distinct，但distinct无法实现group by的功能

（6）Find the beers sold at bars where John drinks.

SELECT DISTINCT beer
FROM Frequents f
  JOIN Bars b ON (f.bar = b.name)
  JOIN Sells s ON (s.bar = b.name)
WHERE f.drinker = 'John'
;
--如果select的column是唯一的，不需要加限定
--JOIN默认是inner join
--JOIN还有outer, full，根据需要加限定
--另外关于natural join，不能加ON的限制条件，自动在多个table的公共列上join
--由于Bars table的存在只是为了关联另两个table，所以可以简化如下
SELECT DISTINCT s.beer
FROM Sells s
  JOIN Frequents f ON (s.bar = f.bar)
WHERE f.drinker = 'John'
;

（7）How many different beers are there?

SELECT count(name)
FROM Beers
;

（8）How many different brewers are there?

SELECT count(DISTINCT manf)
FROM Beers
;

（9）How many beers does each brewer make

SELECT manf, count(*)
FROM Beers
GROUP BY manf
;
--因为后续可能有用，写成view
CREATE OR REPLACE VIEW BrewersBeers(brewer, nbeers) AS
SELECT manf, count(*)
FROM Beers
GROUP BY manf
;

（10）Which brewer makes the most beers?

SELECT brewer
FROM BrewersBeers
WHERE nbeers = 
  (SELECT max(nbeers)
  FROM BrewersBeers)
;

（11）Bars where either Gernot or John drink

SELECT bar
FROM Frequents 
WHERE drinker = 'Gernot' OR drinker = 'John'
;
--or
SELECT bar
FROM Frequents 
WHERE drinker IN ('Gernot', 'John')
;

（12）Bars where both John and Gernot drink

SELECT d1.bar, d1.drinker, d2.drinker
FROM Frequents d1
  JOIN Frequents d2 ON (d1.bar = d2.bar)
WHERE d1.drinker = 'Gernot' AND d2.drinker = 'John'
;
--必须创建两个同名的table，不能用以下方法写，因为drinker cannot have two values in a given tuple
SELECT bar 
FROM Frequents 
WHERE drinker = 'Gernot' and drinker = 'John'
;

（18）Which beers are sold at all bars?

SELECT name
FROM Beers b
WHERE not exists(
  (SELECT name FROM Bars)
  except
  (SELECT bar FROM Sells WHERE beer = b.name)
);
--or
SELECT beer, count(bar)
FROM Sells
GROUP BY beer
HAVING count(bar) = (SELECT count(*) FROM Bars);

（22）How many bars in suburbs where dinkers live?
(must include all such suburbs, even if no bars)

SELECT d.addr, count(b.name)
FROM Drinkers d
  LEFT OUTER JOIN Bars b ON (d.addr = b.addr)
GROUP BY d.addr
;

2. SQL additional videos

Football Exerciese Schema

TABLE GOAL:
id(integer), scoredin(integer), scoredby(integer), timescored(integer), rating(character varying(20))

TABLE MATCH:
id(integer), city(character varying(50)), playedon(date)

TABLE PLAYER:
id(integer), name(character varying(50)), birthday(date), memberof(integer), position(character varying(20))

TABLE TEAM:
id(integer), country(character varying(20))

2.1 SQL Queries on One Table

（1）What teams are there?

SELECT *
FROM team
;
--给column命名再加上order，如下
SELECT country AS "Team"
FROM team
ORDER BY country
;

（2）How many teams are there?

SELECT count(*)
FROM team
;
--同样，如果想改变输出column名称，如下：
SELECT count(*) AS “#teams”
FROM team
;
--因为table中的country没有重复，所以count(*)也可以写成count(country)
--如果有重复，count(*)可以写成count(DISTINCT country)

（3）How many players are there?

SELECT count(*)
FROM player
;

（4）What is the age of the youngest player?

SELECT min(age(birthday))
FROM player 
;

（5）Who is the youngest player?

SELECT name
FROM player 
WHERE age(birthday) =
  (SELECT min(age(birthday))
  FROM player)
;
--birthday和age之间是计算关系，因此可以简化如下
SELECT name
FROM player 
WHERE birthday =
  (SELECT max(birthday)
  FROM player)
;

（6）Which players are over 40? Under 30?

SELECT name
FROM player
WEHRE age(birthday) > '40 years' ::interval
;
SELECT name
FROM player
WEHRE age(birthday) < '30 years' ::interval
;
--a::b将类型a转换为类型b，这里是把date类型转换成date interval

（7）What kind of ratings are given to goals?

SELECT DISTINCT rating
FROM goal
;

（8）How many of each kind of rating?

SELECT rating, count(*)
FROM goal
GROUP BY rating
;

（9）Only show ratings which appear more than 50 times.

SELECT rating, count(*)
FROM goal
GROUP BY rating
HAVING count(*) > 50
;
--having用来给group加condition

2.2 SQL Queries on Multiple Table

（1）List each player and their team.

SELECT p.name, t.country AS team
FROM Player p
  JOIN Team t ON (p.memberof = t.id)
;

（2）What is the average age of each team?

SELECT t.country AS team, avg(age(p.birthday))
FROM Player p
  JOIN Team t ON (p.memberof = t.id)
GROUP BY t.country
;

（3）How many matches did Diego Maradona play in?

SELECT count(*)
FROM Player p
  JOIN Team t ON (p.memberof = t.id)
  JOIN Involves i ON (i.team = t.id)
WHERE p.name = 'Diego Mardona'
;

（4）How many goals did each player score?

SELECT p.name, count(g.id)
FROM Player p
  JOIN Goal g ON (g.scoredBy = p.id)
GROUP BY p.name
ORDER BY count(g.id) desc
;

（5）Include players who scored no goals.

SELECT p.name, count(g.id)
FROM Player p
  LEFT OUTER JOIN Goal g ON (g.scoredBy = p.id)
GROUP BY p.name
ORDER BY count(g.id)
;

（6）Who has scored more than one "amazing" goal?

SELECT p.name, count(g.id)
FROM Player p
  JOIN Goal g ON (g.scoredBy = p.id)
WHERE g.rating = 'amazing'
GROUP BY p.name
HAVING count(g.id) > 1
ORDER BY p.name
;

（7）Give a list of mathes (teams, place, date)

SELECT t1.country, t2.country, m.city, m.playedOn 
FROM Match m 
  JOIN Team t1 ON (t1.id = i1.team)
  JOIN Team t2 ON (t2.id = i2.team)
  JOIN Involves i1 ON ( i1.match = m.id)
  JOIN Involves i2 ON ( i2.match = m.id)
WHERE t1.country < t2.country
ORDER BY m.id
;
--要建立2个同类table的join，因为一场match有2支球队
--因为不想看到两只相同的team作为对手，所以要加WHERE的限制

2.3 SQL Views

（1）Players and their goals

CREATE OR REPLACE VIEW PlayerGoals AS
SELECT p.name AS player, count(g.id) AS ngoals
FROM Player p
  JOIN Goal g ON (p.id = g.scoredBy)
GROUP BY p.name
;
--create后接replace是为了避免创建已经存在的view，但是replace view会继承variable type，所以有的时候直接replace view会有bug，需要drop view view_name;之后再创建
--view能够储存在database中，方便调用，尤其是在复杂的查询中会简化逻辑
--使用view的方法如下，和常规SQL table的使用方法一致
SELECT * FROM PlayerGoals;
SELECT * FROM PlayerGoals ORDER BY ngoals desc;
SELECT * FROM PlayerGoals WHERE player = 'Yu Haidong';
--另一种写法可以在VIEW NAME后给alias
CREATE OR REPLACE VIEW 
  PlayerGoals(Player, ngoals) 
AS
SELECT p.name, count(g.id)
FROM Player p
  JOIN Goal g ON (p.id = g.scoredBy)
GROUP BY p.name
;

（2）List of matches (teams, place, date)

CREATE OR REPLACE VIEW
  MatchInfo(team1, team2, city, played)
AS
SELECT t1.country, t2.country, m.city, m.playedon
FROM match m
  JOIN involves i1 ON (i1.match = m.id)
  JOIN involves i2 ON (i2.match = m.id)
  JOIN team t1 ON (i1.team = t1.id)
  JOIN team t2 ON (i2.team = t2.id)
WHERE t1.country < t2.country
;
SELECT * FROM matchinfo;
SELECT * FROM matchinfo WHERE city = 'Madrid';
SELECT * FROM matchinfo WHERE (team1 = 'Brazil' or team2 = 'Brazil');

（3）Give matches stats (teams, goals, place, date)

CREATE OR REPLACE VIEW
  GoalsByTeamInMatch(match, team, ngoals)
AS
SELECT g.scoredIn, p.memberOf, count(g.id)
FROM Goal g
  JOIN Player p ON (p.id = g.scoredBy)
GROUP BY g.scoredIn, p.memberOf
;

CREATE OR REPLACE VIEW
  TeamsInMatch(match, team, country)
AS
SELECT i.match, i.team, t.country
FROM Involves i 
  JOIN Team t ON (i.team = t.id)
;

CREATE OR REPLACE VIEW
  TeamScores(match, country, ngoals)
AS
SELECT tim.match, tim.country, coalesce(gtm.ngoals, 0)
-- coalesce(gtm.ngoals, 0)作用是checkgtm.ngoals是否为NULL，如果是NULL，则用0替换，如果不是NULL，使用value
FROM TeamsInMatch tim
  LEFT OUTER JOIN GoalsByTeamInMatch gtm
    ON (tim.team = gtm.team and tim.match = gtm.match)
；

CREATE OR REPLACE VIEW 
  MatchScores(match, team1, ngoals1, team2, ngoals2)
AS
SELECT t1.match, t1.country, t1.ngoals, t2.country, t2.ngoals
FROM TeamScores t1
  JOIN   TeamScores t2 ON (t1.match = t2.match and t1.country < t2.country)
;

CREATE OR REPLACE VIEW Match Stats
AS
SELECT m.city AS location, m.playedOn AS date,
  ms.team1, ms.ngoals, ms.team2, ms.ngoals2
FROM Match m 
  JOIN MatchScores ms ON (m.id = ms.match)
;

3. SQL Problem Solving

3.1 Steps in solving probelms in SQL:

(1)know the schema, read the query request identify components of result tuples
(2)identify relevant data items and tables in schema build (3)intermediate result tables (joins)
(4)combine intermediate tables to produce result compute values to appear in result tuples

3.2 JOIN

A very small database to demonstrate different joins，包括没有ON限定的natural join，默认的inner join，限定的left/right outer join和full join。

create table R (
    x  integer primary key,
    y  text
);

insert into R values (1,'abc');
insert into R values (2,'def');
insert into R values (3,'ghi');

create table S (
    z  char(1) primary key,
    x  integer references R(x)
);

insert into S values ('a',1);
insert into S values ('b',3);
insert into S values ('c',1);
insert into S values ('d',null);

natural join没有ON的限定，要求两个table中的variable和type都一样，这里的x(integer)都一样，是common variable

select * from R natural join S;
--result
x   y   z
1   abc a
1   abc c
3   ghi b

join means inner join (inner is optional and is the default)，两个table的交集

select * from R join S on (R.x = S.x);
-- 含义等于select * from R, S where R.x = S.x;
--result
x   y   z   x
1   abc a   1
1   abc c   1
3   ghi b   3

outer not compulsory when left, right, and full are used。
left outer join意味着所有join后面left table的instance都要出现，无论是否满足后面的条件，左侧table全显示以及与之关联的右侧table

select * from R left outer join S on (R.x = S.x); 
--result
x   y   z   x
1   abc a   1
1   abc c   1
2   def 
3   ghi b   3

right outer join意味着所有join后面right table的instance都要出现，无论是否满足后面的条件，右侧table全显示以及与之关联的左侧table

select * from R right outer join S on (R.x = S.x);
--result
x   y   z   x
1   abc a   1
1   abc c   1
3   ghi b   3
        d

full outer join意味着所有join后面left和right的instance都要出现，无论是否满足后面的条件，两个table的并集

select * from R full join S on (R.x = S.x);
--result
x   y   z   x
1   abc a   1
1   abc c   1
2   def 
3   ghi b   3
        d

4. SQL + other language

SQL is a powerful language for manipulating relational data. But it is not a powerful programming language. Sometimes we need some functions beyond SQL:
– we need to implement user interactions
– we need to control sequences of database operations
– we need to process query results in complex ways

SQL功能有限，常见的包括：data definition language (CREATE TABLE...)，contraints (domain, key, referential integrity)，query language (SELECT ... FROM ... WHERE ...)，views (give names to SQL queries)，不足以完成很多任务要求。
常见的extending SQL：new data types，object-orientation，more powerful constraint checking，packaging/parameterizing queries，more functions/aggregates for use in queries，event-based triggered actions，massive data, spread over a network。

4.1 SQL Data Types

（1）DDL
atomic types: integer, float, character, boolean
ability to define tuple types (CREATE TABLE)
（2）Self-defined types
basic types: CREATE DOMAIN
tuple types: CREATE TYPE

CREATE DOMAIN DomainName [ AS ] DataType
[ DEFAULT expression ]
[ CONSTRAINT ConstrName constraint ]
--example
CREATE DOMAIN UnswCourseCode 
AS text CHECK ( value ~ '[A - Z ]{4}[0 -9]{4} ' );

CREATE TYPE TypeName AS
( AttrName1 DataType1 , AttrName2 DataType2 , ...)
--example
CREATE TYPE ComplexNumber 
AS (r float , i float); 
CREATE TYPE CourseInfo AS (
course UnswCourseCode, 
syllabus text,
lecturer text
);

CREATE TYPE is different from CREATE TABLE:
– does not create a new (empty) table
– does not provide for key constraints
– does not have an explicit specification of domain constraints

4.2 SQL Limitations

Consider the problem of withdrawal from a bank account:
If a bank customer attempts to withdraw more funds than they have in their account, then indicate ‘Insufficient Funds’, otherwise update the account.
SQL solution:

SELECT ' Insufficient Funds '
FROM Accounts
WHERE acctNo = AcctNum AND balance < Amount;

UPDATE Accounts
SET balance = balance - Amount
WHERE acctNo = AcctNum AND balance >= Amount;

SELECT ' New balance : ' || balance 
--||表示字符串连接
FROM Accounts
WHERE acctNo = AcctNum;

由于SQL没有条件控制语句，只能用上述笨拙的办法实现，每段程序都要运行，无法使用控制语句帮助提高效率。这个程序有2种情况：
– displays ‘Insufficient Funds’, UPDATE has no effect, displays unchanged balance
– UPDATE occurs as required, displays changed balance

4.3 Database Programming

It is a combination of manipulation of data in DB(via SQL) + conventional programming(via procedural code)
例如，可以通过以下一些方式实现：
– passing SQL commands via a "call-level" interface
(PL is decoupled from DBMS; most flexible; e.g. Java/JDBC, PHP)
– embedding SQL into augmented programming languages
(requires PL pre-processor; typically DBMS-specific; e.g. SQL/C)
– special-purpose programming languages in the DBMS
(integrated with DBMS; enables extensibility; e.g. PL/SQL, PLpgSQL)
使用PLpgSQLde strored-procedure approach实现上例：

PLpgSQL = Procedural Language extensions to PostgreSQL

The PLpgSQL interpreter
CREATE FUNCTION
  withdraw(acctNum text, amount integer) RETURNS text AS $$
DECLRE bal integer; 
BEGIN
  SELECT balance into bal 
  FROM Accounts
  WHERE acctNo = acctNum; 
  IF (bal < amount) THEN
    RETURN 'Insufficient Funds';
  ELSE
    UPDATE Accounts
    SET balance = balance - amount
    WHERE acctNo = acctNum;
    SELECT balance INTO bal
    FROM Accounts 
    WHERE acctNo = acctNum; 
    RETURN 'New Balance: ' || bal;
   END IF;
END;
$$ language plpgsql;

这段程序使用了parameter "DECLRE bal integer; "，使用了control sentences "IF... THEN... ELSE... END IF;"，程序中2对$$之间的是PLpgSQL语言，并在结尾的时候注明"language plpgsql;"。

4.4 Stored Procedures

（1）Stored procedures:
– procedures/functions that are stored in DB along with data
– written in a language combining SQL and procedural ideas
– provide a way to extend operations available in database
– executed within the DBMS (close coupling with query engine)
（2）Benefits of using stored procedures:
– minimal data transfer cost SQL ↔ procedural code
– user-defined functions can be nicely integrated with SQL
– procedures are managed like other DBMS data (ACID)
– procedures and the data they manipulate are held together
（3）PostgreSQL (Functions defined in SQL)

CREATE OR REPLACE FUNCTION
  funcName(arg1type, arg2type, ....)
  RETURNS rettype 
AS $$
  SQL statements
$$ LANGUAGE sql;

Within the function, arguments are accessed as $1, $2, ...
Return value: result of the last SQL statement.
rettype can be any PostgreSQL data type (include tuples, tables).
Function returning a table: returns setof TupleType
例如：

-- max price of specified beer 
CREATE OR REPLACE FUNCTION
  maxPrice(text) RETURNS float 
AS $$
  SELECT max(price) FROM Sells WHERE beer = $1; 
--beer = $1，变量不能使用变量名，只能用$1, $2...
$$ language sql;
--usage example
SELECT maxPrice('New');
SELECT bar, price 
FROM sells
WHERE beer = 'New' AND price = maxPrice('New');

-- set of Bars from specified suburb 
CREATE OR REPALCE FUNCTION
  hotelsIn(text) RETURNS setof Bars 
AS $$
  SELECT * FROM Bars WHERE addr = $1; 
$$ language sql;
--usage example
SELECT * FROM hotelsIn('The Rocks');

（4）Defining PLpgSQL Functions

CREATE OR REPLACE 
  funcName(param1, param2, ....)
  RETURNS rettype
AS $$ 
DECLARE
  variable declarations
BEGIN
  code for function
END;
$$ LANGUAGE plpgsql;

例如：

CREATE OR REPLACE FUNCTION
  add(x text, y text) 
  RETURNS text
AS $$ 
DECLARE
  result text; -- local variable BEGIN
  result := x||''''||y;
  --:=是比较特殊的写法，和其他语言的=assign一样
  return result; 
END;
$$ LANGUAGE 'plpgsql';

never give aliases the same names as attributes.

PLpgSQL allows overloading (i.e. same name, different arg types)
例如：

CREATE FUNCTION add ( int , int ) RETURNS int AS
$$ BEGIN return $1 + $2 ; END ; $$ LANGUAGE plpgsql ;

CREATE FUNCTION add ( int , int , int ) RETURNS int AS
$$ BEGIN return $1 + $2 + $3 ; END ; $$ LANGUAGE plpgsql ;

CREATE FUNCTION add ( char (1) , int ) RETURNS int AS
$$ BEGIN return ascii ( $1 )+ $2 ; END ; $$ LANGUAGE plpgsql ;

But must differ in arg types, so cannot also define:

CREATE FUNCTION add ( char (1) , int ) RETURNS char AS
$$ BEGIN return chr ( ascii ( $1 )+ $2 ); END ; $$ LANGUAGE plpgsql ;

A PostgreSQL function can return a value which is
– void (i.e. no return value)
– an atomic data type (e.g. integer, text, ...)
– a tuple (e.g. table record type or tuple type)
– a set of atomic values (like a table column) – a set of tuples (i.e. a table)
例如：

CREATE TYPE Employee AS
  (id integer, name text, salary float, ...);

CREATE FUNCTION factorial(integer) 
  RETURNS integer ...
CREATE FUNCTION EmployeeOfMonth(date) 
  RETURNS Employee ...
CREATE FUNCTION allSalaries() 
  RETURNS setof float ...
CREATE FUNCTION OlderEmployees() 
  RETURNS setof Employee ...

SELECT factorial(5);
  -- returns one integer
SELECT EmployeeOfMonth('2008-04-01');
  -- returns (x,y,z,...)
SELECT * from EmployeeOfMonth('2008-04-01');
  -- one-row table
SELECT * from allSalaries();
  -- single-column table
SELECT * from OlderEmployees();
  -- subset of Employees

（5）Control Sturctures
Assigment

variable:=expression;

--example
tax := subtotal * 0.06;
my_record.user_id := 20;

Conditionals

IF ... THEN
IF...THEN...ELSE
IF ... THEN ... ELSIF ... THEN ... ELSE

--example
IF v_user_id > 0 THEN
  UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

Iteration

LOOP 
  Satement
END LOOP ;

--example
LOOP
  IF count > 0 THEN
    -- some computations 
  END IF;
END LOOP;

FOR int_var IN low .. high LOOP 
  Satement
END LOOP ;

--example
FOR i IN 1..10 LOOP
  -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;

（6）Select ... Into ...

SELECT Exp1 , Exp2 , ... , Expn 
INTO Var1 , Var2 , ... , Varn 
FROM TableList
WHERE Condition ...

--example
-- cost is local var , price is attr
SELECT price INTO cost
FROM StockList
WHERE item = ' Cricket Bat '; 
cost := cost * (1 + tax_rate ); 
total := total + cost ;

（7）Exceptions

BEGIN
  Statements ...
EXCEPTION
  WHEN Exceptions1 THEN
    StatementsForHandler1 
  WHEN Exceptions2 THEN
    StatementsForHandler2
  ... 
END;

--example
-- table T contains one tuple ( ' Tom ' , ' Jones ') 
DECLARE
  x INTEGER := 3; 
BEGIN
  UPDATE T SET firstname = ' Joe ' 
  WHERE lastname = ' Jones '; 
  -- table T now contains ( ' Joe ' , ' Jones ')
  x := x + 1;
  y := x / y; 
  -- y: = # of Tom Jones in Staff Table 
EXCEPTION
  WHEN division_by_zero THEN
  -- update on T is rolled back to ( ' Tom ' , ' Jones ') 
  RAISE NOTICE ' Caught division_by_zero '; 
  RETURN x ;
  -- value returned is 4
END;

（8）Cursors(游标)
A cursor is a variable that can be used to access the result of a particular SQL query
Simplest way to use cursors: implicitly via FOR ... IN
例如：

CREATE FUNCTION totsal () RETURNS REAL 
AS $$ 
DECLARE
  emp RECORD ;
  total REAL := 0; 
BEGIN
  FOR emp IN SELECT * FROM Employees 
  LOOP
    total := total + emp . salary; 
  END LOOP;
  RETURN total;
END; $$ LANGUAGE plpgsql;
-- emp RECORD是cursor

Basic operations on cursors: OPEN, FETCH, CLOSE

OPEN e ;
LOOP
  FETCH e INTO emp ;
  EXIT WHEN NOT FOUND ; 
  total := total + emp.salary ;
END LOOP ; 
CLOSE e ;

（9）Triggers
Triggers are:
– procedures stored in the database
– 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)

--Consider two triggers and an INSERT statement
CREATE TRIGGER X before insert on T Code1; 
CREATE TRIGGER Y after insert on T Code2; 
insert into T values (a,b,c,...);

--Consider two triggers and an UPDATE statement
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;

Triggers in PostgreSQL:

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

CREATE OR REPLACE FUNCTION name () RETURNS TRIGGER ..

例如：Consider a database of people in the USA, Person.state ∈ (select code from States), or exists (select id from States where code=Person.state)

CREATE TABLE Person (
  id integer primary key,
  ssn varchar(11) unique,
  ... e.g. family, given, street, town ... 
  state char(2), ...
);
CREATE TABLE States (
  id integer primary key,
  code char(2) unique,
  ... e.g. name, area, population, flag ...
);

CREATE TRIGGER checkState before insert or update on Person for each row execute procedure checkState();

CREATE FUNCTION checkState() returns trigger 
AS$$ 
BEGIN
  -- normalise the user-supplied value 
  new.state = upper(trim(new.state)); 
  if (new.state !~ '^[A-Z][A-Z]$') then
    raise exception 'Code must be two alpha chars';
  end if;
  -- implement referential integrity check 
  select * from States where code=new.state; 
  if (not found) then
    RAISE EXCEPTION 'Invalid code %', new.state;
  end if;
  return new; 
end;
$$ language plpgsql;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,591评论 6赞 501
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,448评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,823评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,204评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,228评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,190评论 1赞 299
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,078评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,923评论 0赞 274
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,334评论 1赞 310
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,550评论 2赞 333
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,727评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,428评论 5赞 343
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,022评论 3赞 326
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,672评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,826评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,734评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,619评论 2赞 354

推荐阅读更多精彩内容

  • 思念
    外面漆黑一片 想念铺满心面 脑海是你容颜 闪现早已千遍
    庄心言阅读 217评论 4赞 2
  • markdown简易版
    写这篇只是为了写作的时候方便查阅，强烈建议去看官方教程 标题 加多少个#就代表几级标题，总共可以加6个 列表： -...
    不知名小号阅读 180评论 0赞 1
  • “这个是我的玩具”，抢玩具，该不该
    在英国，教会，褪去宗教的外衣，也是可以堪称一个小型的慈善帮扶中心，每周为需要帮助的人提供免费的福利，诸如语言课程，...
    木木lindsay阅读 484评论 0赞 0