1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>3.5.Inheritance</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="tutorial-advanced.html" title="Chapter3.Advanced Features">
<link rel="prev" href="tutorial-transactions.html" title="3.4.Transactions">
<link rel="next" href="tutorial-conclusion.html" title="3.6.Conclusion">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="tutorial-inheritance"></a>3.5.Inheritance</h2></div></div></div>
<a name="id570165"></a><p> Inheritance is a concept from object-oriented databases. It opens
up interesting new possibilities of database design.
</p>
<p> Let's create two tables: A table <code class="classname">cities</code>
and a table <code class="classname">capitals</code>. Naturally, capitals
are also cities, so you want some way to show the capitals
implicitly when you list all cities. If you're really clever you
might invent some scheme like this:
</p>
<pre class="programlisting">CREATE TABLE capitals (
name text,
population real,
altitude int, -- (in ft)
state char(2)
);
CREATE TABLE non_capitals (
name text,
population real,
altitude int -- (in ft)
);
CREATE VIEW cities AS
SELECT name, population, altitude FROM capitals
UNION
SELECT name, population, altitude FROM non_capitals;</pre>
<p>
This works OK as far as querying goes, but it gets ugly when you
need to update several rows, for one thing.
</p>
<p> A better solution is this:
</p>
<pre class="programlisting">CREATE TABLE cities (
name text,
population real,
altitude int -- (in ft)
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);</pre>
<p>
</p>
<p> In this case, a row of <code class="classname">capitals</code>
<em class="firstterm">inherits</em> all columns (<code class="structfield">name</code>,
<code class="structfield">population</code>, and <code class="structfield">altitude</code>) from its
<em class="firstterm">parent</em>, <code class="classname">cities</code>. The
type of the column <code class="structfield">name</code> is
<code class="type">text</code>, a native <span class="productname">PostgreSQL</span>
type for variable length character strings. State capitals have
an extra column, state, that shows their state. In
<span class="productname">PostgreSQL</span>, a table can inherit from
zero or more other tables.
</p>
<p> For example, the following query finds the names of all cities,
including state capitals, that are located at an altitude
over 500 ft.:
</p>
<pre class="programlisting">SELECT name, altitude
FROM cities
WHERE altitude > 500;</pre>
<p>
which returns:
</p>
<pre class="screen"> name | altitude
-----------+----------
Las Vegas | 2174
Mariposa | 1953
Madison | 845
(3 rows)</pre>
<p>
</p>
<p> On the other hand, the following query finds
all the cities that are not state capitals and
are situated at an altitude of 500 ft. or higher:
</p>
<pre class="programlisting">SELECT name, altitude
FROM ONLY cities
WHERE altitude > 500;</pre>
<p>
</p>
<pre class="screen"> name | altitude
-----------+----------
Las Vegas | 2174
Mariposa | 1953
(2 rows)</pre>
<p>
</p>
<p> Here the <code class="literal">ONLY</code> before <code class="literal">cities</code>
indicates that the query should be run over only the
<code class="classname">cities</code> table, and not tables below
<code class="classname">cities</code> in the inheritance hierarchy. Many
of the commands that we have already discussed [mdash ]
<code class="command">SELECT</code>, <code class="command">UPDATE</code>, and
<code class="command">DELETE</code> [mdash ] support this <code class="literal">ONLY</code>
notation.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p> Although inheritance is frequently useful, it has not been integrated
with unique constraints or foreign keys, which limits its usefulness.
See <a href="ddl-inherit.html" title="5.8.Inheritance">Section5.8, “Inheritance”</a> for more detail.
</p>
</div>
</div></body>
</html>
|
