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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>Inheritance</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="PREVIOUS"
TITLE="Arrays"
HREF="arrays.html"><LINK
REL="NEXT"
TITLE="The Query Language"
HREF="query-ug.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="arrays.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="query-ug.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
><A
NAME="INHERIT"
>Chapter 11. Inheritance</A
></H1
><P
> Let's create two classes. The capitals class contains
state capitals which are also cities. Naturally, the
capitals class should inherit from cities.
<PRE
CLASS="PROGRAMLISTING"
>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 <I
CLASS="FIRSTTERM"
>inherits</I
> all
attributes (name, population, and altitude) from its
parent, cities. The type of the attribute name is
<SPAN
CLASS="TYPE"
>text</SPAN
>, a native <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> type for variable length
ASCII strings. The type of the attribute population is
<SPAN
CLASS="TYPE"
>float</SPAN
>, a native <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> type for double precision
floating point numbers. State capitals have an extra
attribute, state, that shows their state. In <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>,
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.
<BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>The inheritance hierarchy is a actually a directed acyclic graph.</P
></BLOCKQUOTE
>
For example, the following query finds
all the cities that are situated at an attitude of 500ft or higher:
<PRE
CLASS="PROGRAMLISTING"
>SELECT name, altitude
FROM cities
WHERE altitude > 500;
+----------+----------+
|name | altitude |
+----------+----------+
|Las Vegas | 2174 |
+----------+----------+
|Mariposa | 1953 |
+----------+----------+</PRE
> </P
><P
> 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:
<PRE
CLASS="PROGRAMLISTING"
>SELECT c.name, c.altitude
FROM cities* c
WHERE c.altitude > 500;</PRE
>
which returns:
<PRE
CLASS="PROGRAMLISTING"
>+----------+----------+
|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 -- <B
CLASS="COMMAND"
>select</B
>, <B
CLASS="COMMAND"
>update</B
> and <B
CLASS="COMMAND"
>delete</B
> --
support this “*” notation, as do others, like <B
CLASS="COMMAND"
>alter</B
>.</P
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="arrays.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="query-ug.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Arrays</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
>;</TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>The Query Language</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
|
