1 ---------------------------------------------------------------------------
4 -- more POSTGRES SQL features. (These are not part of the SQL-92
8 -- Copyright (c) 1994, Regents of the University of California
10 -- $Id: advanced.source,v 1.1.1.1 1996/07/09 06:22:34 scrappy Exp $
12 ---------------------------------------------------------------------------
14 -----------------------------
16 -- a table can inherit from zero or more tables. A query can reference
17 -- either all rows of a table or all rows of a table plus all of its
19 -----------------------------
21 -- For example, the capitals table inherits from cities table. (It inherits
22 -- all data fields from cities.)
27 altitude int -- (in ft)
30 CREATE TABLE capitals (
34 -- now, let's populate the tables
35 INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63)
36 INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174)
37 INSERT INTO cities VALUES ('Mariposa', 1200, 1953)
39 INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA')
40 INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI')
43 SELECT * FROM capitals;
45 -- like before, a regular query references rows of the base table only
51 -- on the other hand, you can find all cities, including capitals, that
52 -- are located at an altitude of 500 'ft or higher by:
54 SELECT c.name, c.altitude
56 WHERE c.altitude > 500;
59 -----------------------------
61 -- this feature allows you to run historical queries.
62 -----------------------------
64 -- first, let's make some changes to the cities table (suppose Mariposa's
65 -- population grows 10% this year)
68 SET population = population * 1.1
69 WHERE name = 'Mariposa';
71 -- the default time is the current time ('now'):
73 SELECT * FROM cities WHERE name = 'Mariposa';
75 -- we can also retrieve the population of Mariposa ever has. ('epoch' is the
76 -- earliest time representable by the system)
78 SELECT name, population
79 FROM cities['epoch', 'now'] -- can be abbreviated to cities[,]
80 WHERE name = 'Mariposa';
83 ----------------------
85 -- attributes can be arrays of base types or user-defined types
86 ----------------------
88 CREATE TABLE sal_emp (
90 pay_by_quarter int4[],
94 -- insert instances with array attributes. Note the use of braces
96 INSERT INTO sal_emp VALUES (
98 '{10000,10000,10000,10000}',
99 '{{"meeting", "lunch"}, {}}')
101 INSERT INTO sal_emp VALUES (
103 '{20000,25000,25000,25000}',
104 '{{"talk", "consult"}, {"meeting"}}');
106 ----------------------
107 -- queries on array attributes
108 ----------------------
109 SELECT name FROM sal_emp WHERE
110 sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2];
112 -- retrieve third quarter pay of all employees
114 SELECT sal_emp.pay_by_quarter[3] FROM sal_emp;
118 SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE
119 sal_emp.name = 'Bill';
122 -- clean up (you must remove the children first)