granicus.if.org Git - postgresql/blob - doc/src/sgml/advanced.sgml

   1 <Chapter Id="advanced">
   2 <Title>Advanced <ProductName>Postgres</ProductName> <Acronym>SQL</Acronym> Features</Title>
   3
   4 <Para>
   5      Having covered the basics  of  using  <ProductName>Postgres</ProductName>  <Acronym>SQL</Acronym>  to
   6      access your data, we will now discuss those features of
   7      <ProductName>Postgres</ProductName> that distinguish  it  from  conventional  data
   8      managers.   These  features  include  inheritance, time
   9      travel and non-atomic  data  values  (array-  and
  10      set-valued attributes).
  11      Examples   in   this  section  can  also  be  found  in
  12      <FileName>advance.sql</FileName> in the tutorial directory.
  13 (Refer to <XRef LinkEnd="QUERY"> for how to use it.)
  14 </Para>
  15
  16 <Sect1>
  17 <Title>Inheritance</Title>
  18
  19 <Para>
  20      Let's create two classes. The capitals  class  contains
  21      state  capitals  which  are also cities. Naturally, the
  22      capitals class should inherit from cities.
  23
  24 <ProgramListing>
  25 CREATE TABLE cities (
  26     name            text,
  27     population      float,
  28     altitude        int            -- (in ft)
  29 );
  30
  31 CREATE TABLE capitals (
  32     state           char2
  33 ) INHERITS (cities);
  34 </ProgramListing>
  35
  36      In this case, an  instance  of  capitals  <FirstTerm>inherits</FirstTerm>  all
  37      attributes  (name,  population,  and altitude) from its
  38      parent, cities.  The type  of  the  attribute  name  is
  39      <Type>text</Type>,  a  native  <ProductName>Postgres</ProductName>  type  for variable length
  40      ASCII strings.  The type of the attribute population is
  41      <Type>float</Type>,  a  native <ProductName>Postgres</ProductName> type for double precision
  42      floating point numbers.  State capitals have  an  extra
  43      attribute, state, that shows their state.  In <ProductName>Postgres</ProductName>,
  44      a  class  can inherit from zero or more other classes,
  45      and a query can reference either  all  instances  of  a
  46      class  or  all  instances  of  a  class plus all of its
  47      descendants.
  48 <Note>
  49 <Para>
  50 The inheritance hierarchy is a  directed  acyclic graph.
  51 </Para>
  52 </Note>
  53 For example, the  following  query  finds
  54      all  the cities that are situated at an attitude of 500ft or higher:
  55
  56 <ProgramListing>
  57 SELECT name, altitude
  58     FROM cities
  59     WHERE altitude &gt; 500;
  60
  61 +----------+----------+
  62 |name      | altitude |
  63 +----------+----------+
  64 |Las Vegas | 2174     |
  65 +----------+----------+
  66 |Mariposa  | 1953     |
  67 +----------+----------+
  68 </ProgramListing>
  69
  70 <Para>
  71      On the other hand, to find the  names  of  all  cities,
  72      including  state capitals, that are located at an altitude
  73      over 500ft, the query is:
  74
  75 <ProgramListing>
  76 SELECT c.name, c.altitude
  77     FROM cities* c
  78     WHERE c.altitude > 500;
  79 </ProgramListing>
  80
  81      which returns:
  82
  83 <ProgramListing>
  84 +----------+----------+
  85 |name      | altitude |
  86 +----------+----------+
  87 |Las Vegas | 2174     |
  88 +----------+----------+
  89 |Mariposa  | 1953     |
  90 +----------+----------+
  91 |Madison   | 845      |
  92 +----------+----------+
  93 </ProgramListing>
  94
  95      Here the <Quote>*</Quote> after cities indicates that the query should
  96      be  run over cities and all classes below cities in the
  97      inheritance hierarchy.  Many of the  commands  that  we
  98      have  already discussed (<Command>select</Command>, <Command>update</Command> and <Command>delete</Command>)
  99      support this <Quote>*</Quote> notation, as do others, like <Command>alter</Command>.
 100 </Para>
 101
 102 </Sect1>
 103
 104 <Sect1>
 105 <Title>Non-Atomic Values</Title>
 106
 107 <Para>
 108      One  of  the tenets of the relational model is that the
 109      attributes of a relation are atomic.  <ProductName>Postgres</ProductName> does not
 110      have  this  restriction; attributes can themselves contain
 111      sub-values that can be  accessed  from  the  query
 112      language.   For example, you can create attributes that
 113      are arrays of base types.
 114
 115 <Sect2>
 116 <Title>Arrays</Title>
 117
 118 <Para>
 119      <ProductName>Postgres</ProductName> allows attributes of an instance to be defined
 120      as  fixed-length  or  variable-length multi-dimensional
 121      arrays. Arrays of any base type  or  user-defined  type
 122      can  be created. To illustrate their use, we first create a
 123      class with arrays of base types.
 124
 125 <ProgramListing>
 126 CREATE TABLE SAL_EMP (
 127     name            text,
 128     pay_by_quarter  int4[],
 129     schedule        text[][]
 130 );
 131 </ProgramListing>
 132 </Para>
 133
 134 <Para>
 135      The above query will create a class named SAL_EMP  with
 136      a  <FirstTerm>text</FirstTerm>  string (name), a one-dimensional array of <FirstTerm>int4</FirstTerm>
 137      (pay_by_quarter),  which  represents   the   employee's
 138      salary by quarter and a two-dimensional array of <FirstTerm>text</FirstTerm>
 139      (schedule),  which  represents  the  employee's  weekly
 140      schedule.   Now  we  do  some  <FirstTerm>INSERTS</FirstTerm>s; note that when
 141      appending to an array, we  enclose  the  values  within
 142      braces  and  separate  them  by commas.  If you know <FirstTerm>C</FirstTerm>,
 143      this is not unlike the syntax for  initializing  structures.
 144
 145 <ProgramListing>
 146 INSERT INTO SAL_EMP
 147     VALUES ('Bill',
 148     '{10000, 10000, 10000, 10000}',
 149     '{{"meeting", "lunch"}, {}}');
 150
 151 INSERT INTO SAL_EMP
 152     VALUES ('Carol',
 153     '{20000, 25000, 25000, 25000}',
 154     '{{"talk", "consult"}, {"meeting"}}');
 155 </ProgramListing>
 156
 157      By  default,  <ProductName>Postgres</ProductName>  uses  the "one-based" numbering
 158      convention for arrays -- that is, an array  of  n  elements starts with array[1] and ends with array[n].
 159      Now,  we  can  run  some queries on SAL_EMP.  First, we
 160      show how to access a single element of an  array  at  a
 161      time.   This query retrieves the names of the employees
 162      whose pay changed in the second quarter:
 163
 164 <ProgramListing>
 165 SELECT name
 166     FROM SAL_EMP
 167     WHERE SAL_EMP.pay_by_quarter[1] &lt;&gt;
 168     SAL_EMP.pay_by_quarter[2];
 169
 170 +------+
 171 |name  |
 172 +------+
 173 |Carol |
 174 +------+
 175 </ProgramListing>
 176 </Para>
 177
 178 <Para>
 179      This query retrieves  the  third  quarter  pay  of  all
 180      employees:
 181
 182 <ProgramListing>
 183 SELECT SAL_EMP.pay_by_quarter[3] FROM SAL_EMP;
 184
 185
 186 +---------------+
 187 |pay_by_quarter |
 188 +---------------+
 189 |10000          |
 190 +---------------+
 191 |25000          |
 192 +---------------+
 193 </ProgramListing>
 194 </Para>
 195
 196 <Para>
 197      We  can  also  access  arbitrary slices of an array, or
 198      subarrays.  This query  retrieves  the  first  item  on
 199      Bill's schedule for the first two days of the week.
 200
 201 <ProgramListing>
 202 SELECT SAL_EMP.schedule[1:2][1:1]
 203     FROM SAL_EMP
 204     WHERE SAL_EMP.name = 'Bill';
 205
 206 +-------------------+
 207 |schedule           |
 208 +-------------------+
 209 |{{"meeting"},{""}} |
 210 +-------------------+
 211 </ProgramListing>
 212 </Para>
 213
 214 </Sect1>
 215
 216 <Sect1>
 217 <Title>Time Travel</Title>
 218
 219 <Para>
 220 As of <ProductName>Postgres</ProductName> v6.2, <Emphasis>time travel is no longer supported</Emphasis>. There are
 221 several reasons for this: performance impact, storage size, and a pg_time file which grows
 222 toward infinite size in a short period of time.
 223 </Para>
 224
 225 <Para>
 226 New features such as triggers allow one to mimic the behavior of time travel when desired, without
 227 incurring the overhead when it is not needed (for most users, this is most of the time).
 228 See examples in the <FileName>contrib</FileName> directory for more information.
 229 </Para>
 230
 231 <Note>
 232 <Title>Time travel is deprecated</Title>
 233 <Para>
 234 The remaining text in this section is retained only until it can be rewritten in the context
 235 of new techniques to accomplish the same purpose. Volunteers? - thomas 1998-01-12
 236 </Para>
 237 </Note>
 238
 239 <Para>
 240      <ProductName>Postgres</ProductName> supports the notion of time travel.  This feature
 241      allows a user  to  run  historical  queries.   For
 242      example,  to  find  the  current population of Mariposa
 243      city, one would query:
 244
 245 <ProgramListing>
 246 SELECT * FROM cities WHERE name = 'Mariposa';
 247
 248 +---------+------------+----------+
 249 |name     | population | altitude |
 250 +---------+------------+----------+
 251 |Mariposa | 1320       | 1953     |
 252 +---------+------------+----------+
 253 </ProgramListing>
 254
 255      <ProductName>Postgres</ProductName> will automatically find the version  of  Mariposa's
 256      record valid at the current time.
 257      One can also give a time range.  For example to see the
 258      past and present populations  of  Mariposa,  one  would
 259      query:
 260
 261 <ProgramListing>
 262 SELECT name, population
 263     FROM cities['epoch', 'now']
 264     WHERE name = 'Mariposa';
 265 </ProgramListing>
 266
 267      where  "epoch"  indicates  the  beginning of the system
 268      clock.
 269 <Note>
 270 <Para>
 271 On UNIX systems, this is always  midnight,  January  1, 1970 GMT.
 272 </Para>
 273 </Note>
 274 </Para>
 275
 276 <Para>
 277      If  you  have  executed all of the examples so
 278      far, then the above query returns:
 279
 280 <ProgramListing>
 281 +---------+------------+
 282 |name     | population |
 283 +---------+------------+
 284 |Mariposa | 1200       |
 285 +---------+------------+
 286 |Mariposa | 1320       |
 287 +---------+------------+
 288 </ProgramListing>
 289
 290 <Para>
 291      The default beginning of a time range is  the  earliest
 292      time representable by the system and the default end is
 293      the current time; thus, the above  time  range  can  be
 294      abbreviated as ``[,].''
 295 </Para>
 296
 297 <Sect1>
 298 <Title>More Advanced Features</Title>
 299
 300 <Para>
 301 <ProductName>Postgres</ProductName> has many features not touched upon in this
 302 tutorial introduction, which has been oriented toward newer users of <Acronym>SQL</Acronym>.
 303 These are discussed in more detail in both the User's and Programmer's Guides.
 304
 305 </Chapter>