1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
---------------------------------------------------------------------------
--
-- advanced.sql-
-- more POSTGRES SQL features. (These are not part of the SQL-92
-- standard.)
--
--
-- Copyright (c) 1994, Regents of the University of California
--
-- $Id: advanced.source,v 1.1.1.1 1996/07/09 06:22:34 scrappy Exp $
--
---------------------------------------------------------------------------
-----------------------------
-- Inheritance:
-- a table can inherit from zero or more tables. A query can reference
-- either all rows of a table or all rows of a table plus all of its
-- descendants.
-----------------------------
-- For example, the capitals table inherits from cities table. (It inherits
-- all data fields from cities.)
CREATE TABLE cities (
name text,
population float8,
altitude int -- (in ft)
)
CREATE TABLE capitals (
state char2
) INHERITS (cities);
-- now, let's populate the tables
INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63)
INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174)
INSERT INTO cities VALUES ('Mariposa', 1200, 1953)
INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA')
INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI')
SELECT * FROM cities
SELECT * FROM capitals;
-- like before, a regular query references rows of the base table only
SELECT name, altitude
FROM cities
WHERE altitude > 500;
-- on the other hand, you can find all cities, including capitals, that
-- are located at an altitude of 500 'ft or higher by:
SELECT c.name, c.altitude
FROM cities* c
WHERE c.altitude > 500;
-----------------------------
-- Time Travel:
-- this feature allows you to run historical queries.
-----------------------------
-- first, let's make some changes to the cities table (suppose Mariposa's
-- population grows 10% this year)
UPDATE cities
SET population = population * 1.1
WHERE name = 'Mariposa';
-- the default time is the current time ('now'):
SELECT * FROM cities WHERE name = 'Mariposa';
-- we can also retrieve the population of Mariposa ever has. ('epoch' is the
-- earliest time representable by the system)
SELECT name, population
FROM cities['epoch', 'now'] -- can be abbreviated to cities[,]
WHERE name = 'Mariposa';
----------------------
-- Arrays:
-- attributes can be arrays of base types or user-defined types
----------------------
CREATE TABLE sal_emp (
name text,
pay_by_quarter int4[],
schedule char16[][]
);
-- insert instances with array attributes. Note the use of braces
INSERT INTO sal_emp VALUES (
'Bill',
'{10000,10000,10000,10000}',
'{{"meeting", "lunch"}, {}}')
INSERT INTO sal_emp VALUES (
'Carol',
'{20000,25000,25000,25000}',
'{{"talk", "consult"}, {"meeting"}}');
----------------------
-- queries on array attributes
----------------------
SELECT name FROM sal_emp WHERE
sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2];
-- retrieve third quarter pay of all employees
SELECT sal_emp.pay_by_quarter[3] FROM sal_emp;
-- select subarrays
SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE
sal_emp.name = 'Bill';
-- clean up (you must remove the children first)
DROP TABLE sal_emp
DROP TABLE capitals
DROP TABLE cities;
|
