From c8cfb0cea88fec22f5aa0582fe846b46baf77eb1 Mon Sep 17 00:00:00 2001 From: Thomas G. Lockhart Date: Sun, 1 Mar 1998 08:16:16 +0000 Subject: SGML source for new documentation. --- doc/src/sgml/advanced.sgml | 304 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 304 insertions(+) create mode 100644 doc/src/sgml/advanced.sgml (limited to 'doc/src/sgml/advanced.sgml') diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml new file mode 100644 index 00000000000..ccbb054483d --- /dev/null +++ b/doc/src/sgml/advanced.sgml @@ -0,0 +1,304 @@ + +Advanced <ProductName>Postgres</ProductName> <Acronym>SQL</Acronym> Features + + + Having covered the basics of using Postgres SQL 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 + advance.sql in the tutorial directory. +(Refer to for how to use it.) + + + +Inheritance + + + Let's create two classes. The capitals class contains + state capitals which are also cities. Naturally, the + capitals class should inherit from cities. + + +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 native Postgres type for variable length + ASCII strings. The type of the attribute population is + float, a native Postgres type for double precision + floating point numbers. State capitals have an extra + attribute, state, that shows their state. In Postgres, + a class can inherit from zero or more other classes, + and a query can reference either all instances of a + class or all instances of a class plus all of its + descendants. + + +The inheritance hierarchy is a directed acyclic graph. + + +For example, the following query finds + all the cities that are situated at an attitude of 500ft 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 500ft, 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. + + + + + +Non-Atomic Values + + + 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. + + +Arrays + + + 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. + + +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"},{""}} | ++-------------------+ + + + + + + +Time Travel + + +As of Postgres v6.2, time travel is no longer supported. There are +several reasons for this: performance impact, storage size, and a pg_time file which grows +toward infinite size in a short period of time. + + + +New features such as triggers allow one to mimic the behavior of time travel when desired, without +incurring the overhead when it is not needed (for most users, this is most of the time). +See examples in the contrib directory for more information. + + + +Time travel is deprecated + +The remaining text in this section is retained only until it can be rewritten in the context +of new techniques to accomplish the same purpose. Volunteers? - thomas 1998-01-12 + + + + + 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: + + +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. + + +On UNIX systems, this is always midnight, January 1, 1970 GMT. + + + + + 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 ``[,].'' + + + +More Advanced Features + + +Postgres has many features not touched upon in this +tutorial introduction, which has been oriented toward newer users of SQL. +These are discussed in more detail in both the User's and Programmer's Guides. + + -- cgit v1.2.3