diff options
Diffstat (limited to 'doc/manual/advanced.html')
| -rw-r--r-- | doc/manual/advanced.html | 237 |
1 files changed, 237 insertions, 0 deletions
diff --git a/doc/manual/advanced.html b/doc/manual/advanced.html new file mode 100644 index 00000000000..35ae6744bb7 --- /dev/null +++ b/doc/manual/advanced.html @@ -0,0 +1,237 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - ADVANCED POSTGRES SQL FEATURES</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="query.html">[ Previous ]</A> +<A HREF="extend.html">[ Next ]</A> +</font> +<HR> +<H1>5. ADVANCED POSTGRES <B>SQL</B> FEATURES</H1> +<HR> + Having covered the basics of using POSTGRES <B>SQL</B> to + access your data, we will now discuss those features of + POSTGRES that distinguish it from conventional data + managers. These features include inheritance, time + travel and non-atomic data values (array- and + set-valued attributes). + Examples in this section can also be found in + <CODE>advance.sql</CODE> in the tutorial directory. (Refer to the + introduction of the <A HREF="query.html">previous chapter</A> for how to use + it.) + +<H2><A NAME="inheritance">5.1. Inheritance</A></H2> + Let's create two classes. The capitals class contains + state capitals which are also cities. Naturally, the + capitals class should inherit from cities. + +<pre> CREATE TABLE cities ( + name text, + population float, + altitude int -- (in ft) + ); + + CREATE TABLE capitals ( + state char2 + ) INHERITS (cities); +</pre> + In this case, an instance of capitals <B>inherits</B> all + attributes (name, population, and altitude) from its + parent, cities. The type of the attribute name is + <B>text</B>, a built-in POSTGRES type for variable length + ASCII strings. The type of the attribute population is + <B>float4</B>, 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,<A HREF="#4"><font size=-1>[4]</font></A> + 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: + +<pre> SELECT name, altitude + FROM cities + WHERE altitude > 500; + + + +----------+----------+ + |name | altitude | + +----------+----------+ + |Las Vegas | 2174 | + +----------+----------+ + |Mariposa | 1953 | + +----------+----------+ +</pre> + 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: + +<pre> SELECT c.name, c.altitude + FROM cities* c + WHERE c.altitude > 500; +</pre> + which returns: + +<pre> +----------+----------+ + |name | altitude | + +----------+----------+ + |Las Vegas | 2174 | + +----------+----------+ + |Mariposa | 1953 | + +----------+----------+ + |Madison | 845 | + +----------+----------+ +</pre> + 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. + +<H2><A NAME="time-travel">5.2. Time Travel</A></H2> + POSTGRES supports the notion of time travel. This feature + allows a user to run historical queries. For + example, to find the current population of Mariposa + city, one would query: + +<pre> SELECT * FROM cities WHERE name = 'Mariposa'; + + +---------+------------+----------+ + |name | population | altitude | + +---------+------------+----------+ + |Mariposa | 1320 | 1953 | + +---------+------------+----------+ +</pre> + 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: + +<pre> SELECT name, population + FROM cities['epoch', 'now'] + WHERE name = 'Mariposa'; +</pre> + where "epoch" indicates the beginning of the system + clock.<A HREF="#5"><font size=-1>[5]</font></A> If you have executed all of the examples so + far, then the above query returns: + +<pre> +---------+------------+ + |name | population | + +---------+------------+ + |Mariposa | 1200 | + +---------+------------+ + |Mariposa | 1320 | + +---------+------------+ +</pre> + 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 ``[,].'' + +<H2><A NAME="non-atomic-values">5.3. Non-Atomic Values</A></H2> + One of the tenets of the relational model is that the + attributes of a relation are atomic. POSTGRES does not + have this restriction; attributes can themselves contain + sub-values that can be accessed from the query + language. For example, you can create attributes that + are arrays of base types. + +<H3><A NAME="arrays">5.3.1. Arrays</A></H3> + POSTGRES allows attributes of an instance to be defined + as fixed-length or variable-length multi-dimensional + arrays. Arrays of any base type or user-defined type + can be created. To illustrate their use, we first create a + class with arrays of base types. + +<pre> * CREATE TABLE SAL_EMP ( + name text, + pay_by_quarter int4[], + schedule char16[][] + ); +</pre> + The above query will create a class named SAL_EMP with + a <B>text</B> string (name), a one-dimensional array of <B>int4</B> + (pay_by_quarter), which represents the employee's + salary by quarter and a two-dimensional array of <B>char16</B> + (schedule), which represents the employee's weekly + schedule. Now we do some <B>INSERTS</B>s; note that when + appending to an array, we enclose the values within + braces and separate them by commas. If you know <B>C</B>, + this is not unlike the syntax for initializing structures. + +<pre> 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"}}'); +</pre> + 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: + +<pre> * SELECT name + FROM SAL_EMP + WHERE SAL_EMP.pay_by_quarter[1] <> + SAL_EMP.pay_by_quarter[2]; + + +------+ + |name | + +------+ + |Carol | + +------+ +</pre> + This query retrieves the third quarter pay of all + employees: + +<pre> * SELECT SAL_EMP.pay_by_quarter[3] FROM SAL_EMP; + + + +---------------+ + |pay_by_quarter | + +---------------+ + |10000 | + +---------------+ + |25000 | + +---------------+ +</pre> + 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. + +<pre> * SELECT SAL_EMP.schedule[1:2][1:1] + FROM SAL_EMP + WHERE SAL_EMP.name = 'Bill'; + + +-------------------+ + |schedule | + +-------------------+ + |{{"meeting"},{""}} | + +-------------------+ + +</pre> +<p> +<HR> +<A NAME="4"><B>4.</B></A> i.e., the inheritance hierarchy is a directed acyclic +graph.<br> +<A NAME="5"><B>5.</B></A> On UNIX systems, this is always midnight, January 1, +1970 GMT.<br> +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="query.html">[ Previous ]</A> +<A HREF="extend.html">[ Next ]</A> +</font> + |