You can create a view over a commonly used query, which gave a name to the query that you can refer to like an ordinary table.
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
SELECT * FROM myview;
- Making liberal use of views is a key aspect of good SQL database design.
- Views allow you to encapsulate the details of the structure of your tables behind consistent interfaces.
You want to make sure that no one can insert rows in the weather table that do not have a matching entry in the cities table. This is called maintaining the referential integrity of your data. PostgreSQL can do this for you.
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
A transaction bundles multiple steps into a single, all-or-nothing operation. A transaction is said to be atomic: from the point of view of other transactions, it either happens completely or not at all. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all. Furthermore, a transactional database guarantees that all updated made by a transaction are logged in permanent storage (i.e., on disk) before the transaction is reported complete.
PostgreSQL treats every SQL statement as being executed within a transaction. A group of statements surrounded by BEGIN and COMMIT is called a transaction block.
-- A transaction block
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- etc. etc.
COMMIT;
Savepoints allow you to selectively discard parts of the transaction while committing the rest. After defining a savepoint with SAVEPOINT, you can if need roll back to the savepoint with ROLLBACK TO.
BEGIN;
UPDATE accounts
SET balance = balance - 100.00
WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts
SET balance = balance + 100.00
WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts
SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;
A window function performs a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions do not cause rows to become grouped into a single output row -- the rows retain their separate identities.
-- compare each employee's salary with the average salary in his/her department
SELECT depname, empno, salary, avg(salary)
OVER (PARTITION BY depname) -- This clause determines
--- exactly how the rows are split up for processing by the
--- window function. PARTITION BY specifies dividing the
--- rows into partitions of 'depname'. For each row, the
--- window function is computed across the rows that fall
--- into the same partition as the current row.
FROM empsalary;
-- control the order in which the rows are processed by window functions (ORDER BY within OVER)
SELECT depname, empno, salary, rank() -- The rank() function
--- produces a numerical rank within the current row's
--- partition for each distinct ORDER BY value, in order`
--- defined in the ORDER BY clause.
OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
-- when ORDER BY is omitted, the default window frame consists of all rows in the partition
SELECT salary, sum(salary)
OVER ()
FROM empsalary;
-- when ORDER BY is defined, the window frame consists of the first (lowest) salary up through the current once, including any duplicates of the current one
SELECT salary, sum(salary)
OVER (ORDER BY salary)
FROM empsalary;
-- filter or group rows after the window calculations are performed
SELECT depname, empno, salary, enroll_date
FROM
(SELECT depname, empno, salary, enroll_date,
rank() OVER (PARITION BY depname ORDER BY salary DESC, empno) AS pos
FROM empsalary
) AS ss
WHERE pos < 3;
-- using multiple window functions
SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w as (PARTITION BY depname ORDER BY salary DESC);
- Window functions are permitted only in the
SELECT list and the ORDER BY clause of the query.
- Window functions execute after regular aggregate functions.
CREATE TABLE cities (
name text,
population real,
altitude int -- (in ft)
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
-- all cities, including state capitals, that are located at an altitude over 500 feet
SELECT name, altitude
FROM cities
WHERE altitude > 500;
-- all cities that are not state capitals and are situated at an altitude over 500 feet
SELECT name, altitude
FROM ONLY cities -- ONLY indicates that the query should be
--- run only over the 'cities' table, and not tables below
--- 'cities' in the inheritance hierarchy
WHERE altitude > 500;
Although inheritance is frequently useful, it has not been integrated with unique constraints or foreign keys, which limits its usefulness.
