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.3 1999/07/08 15:28:51 momjian 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           char(2)
  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 --      removed for v6.3, but possible using triggers.
  63 --      see contrib/spi/README for more information.
  64 -----------------------------
  65
  66 -- first, let's make some changes to the cities table (suppose Mariposa's
  67 -- population grows 10% this year)
  68
  69 -- UPDATE cities
  70 -- SET population = population * 1.1
  71 -- WHERE name = 'Mariposa';
  72
  73 -- the default time is the current time ('now'):
  74
  75 -- SELECT * FROM cities WHERE name = 'Mariposa';
  76
  77 -- we can also retrieve the population of Mariposa ever has. ('epoch' is the
  78 -- earliest time representable by the system)
  79
  80 -- SELECT name, population
  81 -- FROM cities['epoch', 'now']  -- can be abbreviated to cities[,]
  82 -- WHERE name = 'Mariposa';
  83
  84
  85 ----------------------
  86 -- Arrays:
  87 --      attributes can be arrays of base types or user-defined types
  88 ----------------------
  89
  90 CREATE TABLE sal_emp (
  91         name    text,
  92         pay_by_quarter  int4[],
  93         schedule        text[][]
  94 );
  95
  96 -- insert instances with array attributes.  Note the use of braces
  97
  98 INSERT INTO sal_emp VALUES (
  99         'Bill',
 100         '{10000,10000,10000,10000}',
 101         '{{"meeting", "lunch"}, {}}');
 102
 103 INSERT INTO sal_emp VALUES (
 104         'Carol',
 105         '{20000,25000,25000,25000}',
 106         '{{"talk", "consult"}, {"meeting"}}');
 107
 108 ----------------------
 109 -- queries on array attributes
 110 ----------------------
 111 SELECT name FROM sal_emp WHERE
 112         sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2];
 113
 114 -- retrieve third quarter pay of all employees
 115
 116 SELECT sal_emp.pay_by_quarter[3] FROM sal_emp;
 117
 118 -- select subarrays
 119
 120 SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE
 121         sal_emp.name = 'Bill';
 122
 123
 124 -- clean up (you must remove the children first)
 125 DROP TABLE sal_emp;
 126 DROP TABLE capitals;
 127 DROP TABLE cities;