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.2 1998/02/28 23:37:08 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 -- removed for v6.3, but possible using triggers.
63 -- see contrib/spi/README for more information.
64 -----------------------------
66 -- first, let's make some changes to the cities table (suppose Mariposa's
67 -- population grows 10% this year)
70 -- SET population = population * 1.1
71 -- WHERE name = 'Mariposa';
73 -- the default time is the current time ('now'):
75 -- SELECT * FROM cities WHERE name = 'Mariposa';
77 -- we can also retrieve the population of Mariposa ever has. ('epoch' is the
78 -- earliest time representable by the system)
80 -- SELECT name, population
81 -- FROM cities['epoch', 'now'] -- can be abbreviated to cities[,]
82 -- WHERE name = 'Mariposa';
85 ----------------------
87 -- attributes can be arrays of base types or user-defined types
88 ----------------------
90 CREATE TABLE sal_emp (
92 pay_by_quarter int4[],
96 -- insert instances with array attributes. Note the use of braces
98 INSERT INTO sal_emp VALUES (
100 '{10000,10000,10000,10000}',
101 '{{"meeting", "lunch"}, {}}');
103 INSERT INTO sal_emp VALUES (
105 '{20000,25000,25000,25000}',
106 '{{"talk", "consult"}, {"meeting"}}');
108 ----------------------
109 -- queries on array attributes
110 ----------------------
111 SELECT name FROM sal_emp WHERE
112 sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2];
114 -- retrieve third quarter pay of all employees
116 SELECT sal_emp.pay_by_quarter[3] FROM sal_emp;
120 SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE
121 sal_emp.name = 'Bill';
124 -- clean up (you must remove the children first)