granicus.if.org Git - postgresql/blob - src/tutorial/advanced.source

   1 ---------------------------------------------------------------------------
   2 --
   3 -- advanced.sql-
   4 --    more POSTGRES SQL features. (These are not part of the SQL-92
   5 --    standard.)
   6 --
   7 --
   8 -- Copyright (c) 1994, Regents of the University of California
   9 --
  10 -- $Id: advanced.source,v 1.1.1.1 1996/07/09 06:22:34 scrappy Exp $
  11 --
  12 ---------------------------------------------------------------------------
  13
  14 -----------------------------
  15 -- Inheritance:
  16 --      a table can inherit from zero or more tables. A query can reference
  17 --      either all rows of a table or all rows of a table plus all of its
  18 --      descendants.
  19 -----------------------------
  20
  21 -- For example, the capitals table inherits from cities table. (It inherits
  22 -- all data fields from cities.)
  23
  24 CREATE TABLE cities (
  25         name            text,
  26         population      float8,
  27         altitude        int             -- (in ft)
  28 )
  29
  30 CREATE TABLE capitals (
  31         state           char2
  32 ) INHERITS (cities);
  33
  34 -- now, let's populate the tables
  35 INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63)
  36 INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174)
  37 INSERT INTO cities VALUES ('Mariposa', 1200, 1953)
  38
  39 INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA')
  40 INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI')
  41
  42 SELECT * FROM cities
  43 SELECT * FROM capitals;
  44
  45 -- like before, a regular query references rows of the base table only
  46
  47 SELECT name, altitude
  48 FROM cities
  49 WHERE altitude > 500;
  50
  51 -- on the other hand, you can find all cities, including capitals, that
  52 -- are located at an altitude of 500 'ft or higher by:
  53
  54 SELECT c.name, c.altitude
  55 FROM cities* c
  56 WHERE c.altitude > 500;
  57
  58
  59 -----------------------------
  60 -- Time Travel:
  61 --      this feature allows you to run historical queries.
  62 -----------------------------
  63
  64 -- first, let's make some changes to the cities table (suppose Mariposa's
  65 -- population grows 10% this year)
  66
  67 UPDATE cities
  68 SET population = population * 1.1
  69 WHERE name = 'Mariposa';
  70
  71 -- the default time is the current time ('now'):
  72
  73 SELECT * FROM cities WHERE name = 'Mariposa';
  74
  75 -- we can also retrieve the population of Mariposa ever has. ('epoch' is the
  76 -- earliest time representable by the system)
  77
  78 SELECT name, population
  79 FROM cities['epoch', 'now']     -- can be abbreviated to cities[,]
  80 WHERE name = 'Mariposa';
  81
  82
  83 ----------------------
  84 -- Arrays:
  85 --      attributes can be arrays of base types or user-defined types
  86 ----------------------
  87
  88 CREATE TABLE sal_emp (
  89         name    text,
  90         pay_by_quarter  int4[],
  91         schedule        char16[][]
  92 );
  93
  94 -- insert instances with array attributes.  Note the use of braces
  95
  96 INSERT INTO sal_emp VALUES (
  97         'Bill',
  98         '{10000,10000,10000,10000}',
  99         '{{"meeting", "lunch"}, {}}')
 100
 101 INSERT INTO sal_emp VALUES (
 102         'Carol',
 103         '{20000,25000,25000,25000}',
 104         '{{"talk", "consult"}, {"meeting"}}');
 105
 106 ----------------------
 107 -- queries on array attributes
 108 ----------------------
 109 SELECT name FROM sal_emp WHERE
 110         sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2];
 111
 112 -- retrieve third quarter pay of all employees
 113
 114 SELECT sal_emp.pay_by_quarter[3] FROM sal_emp;
 115
 116 -- select subarrays
 117
 118 SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE
 119         sal_emp.name = 'Bill';
 120
 121
 122 -- clean up (you must remove the children first)
 123 DROP TABLE sal_emp
 124 DROP TABLE capitals
 125 DROP TABLE cities;