advance.sql in the tutorial directory. (Refer to the
+ introduction of the previous chapter for how to use
+ it.)
+
+CREATE TABLE cities ( + name text, + population float, + altitude int -- (in ft) + ); + + CREATE TABLE capitals ( + state char2 + ) INHERITS (cities); ++ In this case, an instance of capitals inherits all + attributes (name, population, and altitude) from its + parent, cities. The type of the attribute name is + text, a built-in POSTGRES type for variable length + ASCII strings. The type of the attribute population is + float4, a built-in POSTGRES type for double precision + floating point numbres. State capitals have an extra + attribute, state, that shows their state. In POSTGRES, + a class can inherit from zero or more other classes,[4] + and a query can reference either all instances of a + class or all instances of a class plus all of its + descendants. For example, the following query finds + all the cities that are situated at an attitude of 500 + 'ft or higher: + +
SELECT name, altitude + FROM cities + WHERE altitude > 500; + + + +----------+----------+ + |name | altitude | + +----------+----------+ + |Las Vegas | 2174 | + +----------+----------+ + |Mariposa | 1953 | + +----------+----------+ ++ On the other hand, to find the names of all cities, + including state capitals, that are located at an altitude + over 500 'ft, the query is: + +
SELECT c.name, c.altitude + FROM cities* c + WHERE c.altitude > 500; ++ which returns: + +
+----------+----------+ + |name | altitude | + +----------+----------+ + |Las Vegas | 2174 | + +----------+----------+ + |Mariposa | 1953 | + +----------+----------+ + |Madison | 845 | + +----------+----------+ ++ Here the * after cities indicates that the query should + be run over cities and all classes below cities in the + inheritance hierarchy. Many of the commands that we + have already discussed -- select, update and delete -- + support this * notation, as do others, like alter command. + +
SELECT * FROM cities WHERE name = 'Mariposa'; + + +---------+------------+----------+ + |name | population | altitude | + +---------+------------+----------+ + |Mariposa | 1320 | 1953 | + +---------+------------+----------+ ++ POSTGRES will automatically find the version of Mariposa's + record valid at the current time. + One can also give a time range. For example to see the + past and present populations of Mariposa, one would + query: + +
SELECT name, population + FROM cities['epoch', 'now'] + WHERE name = 'Mariposa'; ++ where "epoch" indicates the beginning of the system + clock.[5] If you have executed all of the examples so + far, then the above query returns: + +
+---------+------------+ + |name | population | + +---------+------------+ + |Mariposa | 1200 | + +---------+------------+ + |Mariposa | 1320 | + +---------+------------+ ++ The default beginning of a time range is the earliest + time representable by the system and the default end is + the current time; thus, the above time range can be + abbreviated as ``[,].'' + +
* CREATE TABLE SAL_EMP ( + name text, + pay_by_quarter int4[], + schedule char16[][] + ); ++ The above query will create a class named SAL_EMP with + a text string (name), a one-dimensional array of int4 + (pay_by_quarter), which represents the employee's + salary by quarter and a two-dimensional array of char16 + (schedule), which represents the employee's weekly + schedule. Now we do some INSERTSs; note that when + appending to an array, we enclose the values within + braces and separate them by commas. If you know C, + this is not unlike the syntax for initializing structures. + +
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"}}');
+
+ By default, POSTGRES uses the "one-based" numbering
+ convention for arrays -- that is, an array of n elements starts with array[1] and ends with array[n].
+ Now, we can run some queries on SAL_EMP. First, we
+ show how to access a single element of an array at a
+ time. This query retrieves the names of the employees
+ whose pay changed in the second quarter:
+
+* SELECT name + FROM SAL_EMP + WHERE SAL_EMP.pay_by_quarter[1] <> + SAL_EMP.pay_by_quarter[2]; + + +------+ + |name | + +------+ + |Carol | + +------+ ++ This query retrieves the third quarter pay of all + employees: + +
* SELECT SAL_EMP.pay_by_quarter[3] FROM SAL_EMP; + + + +---------------+ + |pay_by_quarter | + +---------------+ + |10000 | + +---------------+ + |25000 | + +---------------+ ++ We can also access arbitrary slices of an array, or + subarrays. This query retrieves the first item on + Bill's schedule for the first two days of the week. + +
* SELECT SAL_EMP.schedule[1:2][1:1]
+ FROM SAL_EMP
+ WHERE SAL_EMP.name = 'Bill';
+
+ +-------------------+
+ |schedule |
+ +-------------------+
+ |{{"meeting"},{""}} |
+ +-------------------+
+
+
++