<TITLE>The POSTGRES95 User Manual - ADVANCED POSTGRES SQL FEATURES</TITLE>

<font size=-1>

<A HREF="pg95user.html">[ TOC ]</A>

<A HREF="query.html">[ Previous ]</A>

<A HREF="extend.html">[ Next ]</A>

<H1>5. ADVANCED POSTGRES <B>SQL</B> FEATURES</H1>

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);

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 &gt; 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&#42; c

WHERE c.altitude &gt; 500;

which returns:

<pre> +----------+----------+

|name | altitude |

+----------+----------+

|Las Vegas | 2174 |

+----------+----------+

|Mariposa | 1953 |

+----------+----------+

|Madison | 845 |

+----------+----------+

Here the &#42; 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 &#42; 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 &#42; 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:

<pre> SELECT name, population

FROM cities['epoch', 'now']

WHERE name = 'Mariposa';

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 |

+---------+------------+

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> &#42; CREATE TABLE SAL_EMP (

name text,

pay_by_quarter int4[],

schedule char16[][]

);

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"}}');

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> &#42; SELECT name

FROM SAL_EMP

WHERE SAL_EMP.pay_by_quarter[1] &lt;&gt;

SAL_EMP.pay_by_quarter[2];

+------+

|name |

+------+

|Carol |

+------+

This query retrieves the third quarter pay of all

employees:

<pre> &#42; 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.

<pre> &#42; SELECT SAL_EMP.schedule[1:2][1:1]

FROM SAL_EMP

WHERE SAL_EMP.name = 'Bill';

+-------------------+

|schedule |

+-------------------+

|{{"meeting"},{""}} |

+-------------------+

<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>

<font size=-1>

<A HREF="pg95user.html">[ TOC ]</A>

<A HREF="query.html">[ Previous ]</A>

<A HREF="extend.html">[ Next ]</A>