---------------------------------------------------------------------------
--
-- advanced.sql-
--    more POSTGRES SQL features. (These are not part of the SQL-92
--    standard.)
--
--
-- Copyright (c) 1994, Regents of the University of California
--
-- $Id: advanced.source,v 1.1.1.1 1996/07/09 06:22:34 scrappy Exp $
--
---------------------------------------------------------------------------

-----------------------------
-- Inheritance:
--	a table can inherit from zero or more tables. A query can reference
--	either all rows of a table or all rows of a table plus all of its
--	descendants.
-----------------------------

-- For example, the capitals table inherits from cities table. (It inherits 
-- all data fields from cities.)

CREATE TABLE cities (
	name		text,
	population	float8,
	altitude	int		-- (in ft)
)

CREATE TABLE capitals (
	state		char2
) INHERITS (cities);

-- now, let's populate the tables
INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63)
INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174)
INSERT INTO cities VALUES ('Mariposa', 1200, 1953)

INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA')
INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI')

SELECT * FROM cities
SELECT * FROM capitals;

-- like before, a regular query references rows of the base table only

SELECT name, altitude
FROM cities
WHERE altitude > 500;

-- on the other hand, you can find all cities, including capitals, that
-- are located at an altitude of 500 'ft or higher by:

SELECT c.name, c.altitude
FROM cities* c
WHERE c.altitude > 500;


-----------------------------
-- Time Travel:
--	this feature allows you to run historical queries. 
-----------------------------

-- first, let's make some changes to the cities table (suppose Mariposa's
-- population grows 10% this year)

UPDATE cities
SET population = population * 1.1
WHERE name = 'Mariposa';

-- the default time is the current time ('now'):

SELECT * FROM cities WHERE name = 'Mariposa';

-- we can also retrieve the population of Mariposa ever has. ('epoch' is the
-- earliest time representable by the system)

SELECT name, population
FROM cities['epoch', 'now']	-- can be abbreviated to cities[,]
WHERE name = 'Mariposa';


----------------------
-- Arrays:
--      attributes can be arrays of base types or user-defined types
----------------------

CREATE TABLE sal_emp (
	name	text,
	pay_by_quarter	int4[],
	schedule	char16[][]
);

-- insert instances with array attributes.  Note the use of braces

INSERT INTO sal_emp VALUES (
	'Bill',
	'{10000,10000,10000,10000}',
	'{{"meeting", "lunch"}, {}}')

INSERT INTO sal_emp VALUES (
	'Carol',
	'{20000,25000,25000,25000}',
	'{{"talk", "consult"}, {"meeting"}}');

----------------------
-- queries on array attributes
----------------------
SELECT name FROM sal_emp WHERE
	sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2];

-- retrieve third quarter pay of all employees

SELECT sal_emp.pay_by_quarter[3] FROM sal_emp;

-- select subarrays

SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE	
	sal_emp.name = 'Bill';


-- clean up (you must remove the children first)
DROP TABLE sal_emp
DROP TABLE capitals
DROP TABLE cities;