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;