1---------------------------------------------------------------------------
2--
3-- advanced.sql-
4--    Tutorial on advanced PostgreSQL features
5--
6--
7-- Copyright (c) 1994, Regents of the University of California
8--
9-- src/tutorial/advanced.source
10--
11---------------------------------------------------------------------------
12
13-----------------------------
14-- Inheritance:
15--	A table can inherit from zero or more tables.  A query can reference
16--	either all rows of a table or all rows of a table plus all of its
17--	descendants.
18-----------------------------
19
20-- For example, the capitals table inherits from cities table. (It inherits
21-- all data fields from cities.)
22
23CREATE TABLE cities (
24	name		text,
25	population	float8,
26	elevation	int		-- (in ft)
27);
28
29CREATE TABLE capitals (
30	state		char(2)
31) INHERITS (cities);
32
33-- Now, let's populate the tables.
34INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63);
35INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174);
36INSERT INTO cities VALUES ('Mariposa', 1200, 1953);
37
38INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA');
39INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI');
40
41SELECT * FROM cities;
42SELECT * FROM capitals;
43
44-- You can find all cities, including capitals, that
45-- are located at an elevation of 500 ft or higher by:
46
47SELECT c.name, c.elevation
48FROM cities c
49WHERE c.elevation > 500;
50
51-- To scan rows of the parent table only, use ONLY:
52
53SELECT name, elevation
54FROM ONLY cities
55WHERE elevation > 500;
56
57
58-- clean up (you must remove the children first)
59DROP TABLE capitals;
60DROP TABLE cities;
61