CREATE TABLE Countries( name TEXT PRIMARY KEY, population TEXT, continent TEXT, currency TEXT );
CREATE TABLE Countries( name TEXT PRIMARY KEY, population INT, continent CHAR(2), currency CHAR(3) );
CREATE TABLE Transfer(
sender TEXT NOT NULL,
receiver TEXT NOT NULL,
amount INT CHECK (amount>0),
);
CREATE TABLE TakenCourses( student TEXT, course TEXT, grade CHAR(1) CHECK (grade IN ('U','3','4','5')) );
CREATE TABLE Transfer(
sender TEXT,
receiver TEXT,
amount INT CHECK (amount>0),
CHECK (receiver<>sender)
);
CREATE TABLE Transfer(
sender TEXT,
receiver TEXT,
amount INT CHECK (amount>0),
CONSTRAINT not_to_self CHECK (receiver<>sender)
);
CHECK (idnr LIKE '%-%')
CHECK (idnr LIKE '______-____')
CHECK (idnr SIMILAR TO '[0-9]{10}-[0-9]{4}')
CREATE TABLE Transfers( sender TEXT REFERENCES Accounts(holder) recipient TEXT REFERENCES Accounts(holder) amount INT CHECK (amount>0), … );
CREATE TABLE Transfers( sender TEXT REFERENCES Accounts(holder) ON UPDATE CASCADE, recipient TEXT REFERENCES Accounts(holder) ON UPDATE CASCADE, amount INT CHECK (amount>0), … );
ON UPDATE CASCADE
,
changes to the primary keys in Accounts are propagated to the references
in Transfers.
ON UPDATE SET NULL
ON DELETE SET NULL
ON DELETE CASCADE
CREATE ASSERTION minimum_balance AS
CHECK ( (SELECT SUM(balance) FROM Accounts) >= 100000) );
CREATE FUNCTION name(parameters) RETURNS type AS $$ ... $$ LANGUAGE language ;
SQL
: pure SQL statements
plpgsql
: PL/pgSQL – SQL Procedural Language.
CREATE OR REPLACE FUNCTION
…
$$…text…$$
is a string literal, similar to
'…text…'
.
CREATE TRIGGER name AFTER|BEFORE INSERT/UPDATE/DELETE ON table FOR EACH ROW|STATEMENT EXECUTE FUNCTION function_name();
… AFTER UPDATE OR DELETE ON table …
CREATE TRIGGER name INSTEAD OF INSERT|UPDATE|DELETE ON view FOR EACH ROW|STATEMENT EXECUTE FUNCTION function_name();
INSTEAD OF
trigger you can make changes to the underlying
tables in response to INSERT
, UPDATE
or
DELETE
on the view.
RAISE EXCEPTION 'some error message'; RAISE EXCEPTION 'the total is just %, which is too low', total;
RAISE NOTICE 'some informative message';
NEW.
attribute_name
refers to the new value of an attribute.
OLD.
attribute_name
refers to the old value of an attribute.
DECLARE total INT; … BEGIN total := (SELECT SUM(balance) FROM Accounts); … END
IF condition THEN … ELSEIF condition THEN … ELSE … END IF;
waiting.sql
.