Text Size: Normal / Large

Home → Documentation → Manuals → PostgreSQL 8.0

This page in other versions: 9.2 / 9.1 / 9.0 / 8.4 | Unsupported versions: 9.3 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / devel

PostgreSQL 8.0.26 Documentation
Prev	Fast Backward	Chapter 3. Advanced Features	Fast Forward	Next

3.5. Inheritance

Inheritance is a concept from object-oriented databases. It opens up interesting new possibilities of database design.

Let's create two tables: A table cities and a table capitals. Naturally, capitals are also cities, so you want some way to show the capitals implicitly when you list all cities. If you're really clever you might invent some scheme like this:

CREATE TABLE capitals (
  name       text,
  population real,
  altitude   int,    -- (in ft)
  state      char(2)
);

CREATE TABLE non_capitals (
  name       text,
  population real,
  altitude   int     -- (in ft)
);

CREATE VIEW cities AS
  SELECT name, population, altitude FROM capitals
    UNION
  SELECT name, population, altitude FROM non_capitals;

This works OK as far as querying goes, but it gets ugly when you need to update several rows, for one thing.

A better solution is this:

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

CREATE TABLE capitals (
  state      char(2)
) INHERITS (cities);

In this case, a row of capitals inherits all columns (name, population, and altitude) from its parent, cities. The type of the column name is text, a native PostgreSQL type for variable length character strings. State capitals have an extra column, state, that shows their state. In PostgreSQL, a table can inherit from zero or more other tables.

For example, the following query finds the names of all cities, including state capitals, that are located at an altitude over 500 ft.:

SELECT name, altitude
  FROM cities
  WHERE altitude > 500;

which returns:

   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845
(3 rows)

On the other hand, the following query finds all the cities that are not state capitals and are situated at an altitude of 500 ft. or higher:

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
(2 rows)

Here the ONLY before cities indicates that the query should be run over only the cities table, and not tables below cities in the inheritance hierarchy. Many of the commands that we have already discussed — SELECT, UPDATE, and DELETE — support this ONLY notation.

Note: Although inheritance is frequently useful, it has not been integrated with unique constraints or foreign keys, which limits its usefulness. See Section 5.5 for more detail.

Prev	Home	Next
Transactions	Up	Conclusion

Comments

2005-05-16 01:46:07.228132

/*
This example demonstrates using PostgreSQL table inheritance to maintain a row change history table.
The idea was suggested by Greg Patnude on pgsql-general mailing list, Wed, 23 Mar 2005. I did some
minor refinement and then worked out this illustrative sample script.
*/

\set ON_ERROR_STOP ON

DROP SCHEMA test CASCADE;
CREATE SCHEMA test AUTHORIZATION postgres;

-- This option makes it unnecessary to use the "ONLY" keyword in your SELECT and UPDATE statements.
SET SQL_INHERITANCE TO OFF;

SET search_path = test, pg_catalog;
SET default_with_oids = false;

CREATE TABLE test_table (
person_pk SERIAL,
last_name character varying(24),
first_name character varying(24),
gender character(1),
marital_status character(1)
);

INSERT INTO test_table VALUES (1, 'Funny', 'Doug', 'M', 'S');
INSERT INTO test_table VALUES (2, 'Mayonnaise', 'Patty', 'M', NULL);
INSERT INTO test_table VALUES (3, 'Klotz', 'Roger ', NULL, 'S');
INSERT INTO test_table VALUES (4, 'Bone', 'Thaddeaus', 'M', 'S');

ALTER TABLE ONLY test_table ADD CONSTRAINT person_pkey PRIMARY KEY (person_pk);

CREATE TABLE test_table_history(
action VARCHAR(6),
update_date TIMESTAMP NOT NULL DEFAULT NOW(),
update_user NAME NOT NULL DEFAULT CURRENT_USER
) INHERITS (test.test_table) WITHOUT OIDS;

CREATE RULE test_table_ru AS ON UPDATE TO test.test_table
DO INSERT INTO test.test_table_history
SELECT *, 'UPDATE' FROM ONLY test.test_table WHERE person_pk = old.person_pk;

CREATE RULE test_table_rd AS ON DELETE TO test.test_table
DO INSERT INTO test.test_table_history
SELECT *, 'DELETE' FROM ONLY test.test_table WHERE person_pk = old.person_pk;

SELECT * FROM test_table ORDER BY 1;
/*
person_pk | last_name | first_name | gender | marital_status
-----------+------------+------------+--------+----------------
1 | Funny | Doug | M | S
2 | Mayonnaise | Patty | M |
3 | Klotz | Roger | | S
4 | Bone | Thaddeaus | M | S
(4 rows)
*/

UPDATE test_table SET marital_status = 'S' WHERE NOT last_name ILIKE 'bone';
UPDATE test_table SET marital_status = 'M' WHERE last_name ILIKE 'bone';
UPDATE test_table SET gender = 'F' WHERE last_name ILIKE 'Mayonnaise';
/*
UPDATE 3
UPDATE 1
UPDATE 1
*/

SELECT * FROM test_table ORDER BY 1;
/*
person_pk | last_name | first_name | gender | marital_status
-----------+------------+------------+--------+----------------
1 | Funny | Doug | M | S
2 | Mayonnaise | Patty | F | S
3 | Klotz | Roger | | S
4 | Bone | Thaddeaus | M | M
(4 rows)
*/

SELECT * FROM test_table_history ORDER BY update_date;
/*
person_pk | last_name | first_name | gender | marital_status | action | update_date | update_user
-----------+------------+------------+--------+----------------+--------+----------------------------+-------------
1 | Funny | Doug | M | S | UPDATE | 2005-05-15 20:48:20.85222 | btober
2 | Mayonnaise | Patty | M | | UPDATE | 2005-05-15 20:48:20.85222 | btober
3 | Klotz | Roger | | S | UPDATE | 2005-05-15 20:48:20.85222 | btober
4 | Bone | Thaddeaus | M | S | UPDATE | 2005-05-15 20:48:20.881803 | btober
2 | Mayonnaise | Patty | M | S | UPDATE | 2005-05-15 20:48:20.930088 | btober
(5 rows)
*/

DELETE FROM test_table WHERE last_name ILIKE 'bone';
/*
DELETE 1
*/

2005-09-20 07:36:09.341017

/*
The following is based on suggestion by Mike Rylander posted on Postgresql-General Sun, 18 Sep 2005.

Rylander's original suggestion employed a trigger and tracked
only row updates. My implementation makes use of rules and
handles both updates and deletions.
*/

\o output.txt
\set ON_ERROR_STOP OFF

DROP SCHEMA auditor CASCADE;
DROP SCHEMA test CASCADE;

\set ON_ERROR_STOP ON

-- Create a schema to contain all of our audit tables and the creator function
CREATE SCHEMA auditor;

CREATE OR REPLACE FUNCTION auditor.create_auditor(name, name)
RETURNS bool AS
'
BEGIN
-- This is the function that does the heavy lifting of creating audit tables
-- and the triggers that will populate them.

-- Create the audit table: auditor.{schema}_{table}
EXECUTE \'
CREATE TABLE auditor.\' || $1 || \'_\' || $2 || \' (
update_action VARCHAR(6) NOT NULL,
update_date TIMESTAMP NOT NULL DEFAULT NOW(),
update_user NAME NOT NULL DEFAULT CURRENT_USER
) INHERITS (\' || $1 || \'.\' || $2 || \') WITHOUT OIDS;
\';

EXECUTE \'
CREATE RULE \'|| $2 ||\'_ru AS ON UPDATE TO \'|| $1 ||\'.\'|| $2 ||\'
DO INSERT INTO auditor.\'|| $1 ||\'_\'|| $2 ||\'
SELECT OLD.*, \'\'UPDATE\'\';
\';

EXECUTE \'
CREATE RULE \'|| $2 ||\'_rd AS ON DELETE TO \'|| $1 ||\'.\'|| $2 ||\'
DO INSERT INTO auditor.\'|| $1 ||\'_\'|| $2 ||\'
SELECT OLD.*, \'\'DELETE\'\';
\';

RETURN TRUE;
END;
'
LANGUAGE 'plpgsql' VOLATILE;

/* BEGIN EXAMPLE */

CREATE SCHEMA test AUTHORIZATION postgres;

-- This option makes it unnecessary to use the "ONLY" keyword in your SELECT and UPDATE statements.
\set SQL_INHERITANCE TO OFF;

\set search_path = test, pg_catalog;
\set default_with_oids = false;

CREATE TABLE test.person (
first_name character varying(24),
last_name character varying(24),
gender character(1),
marital_status character(1)
) WITHOUT OIDS;

INSERT INTO test.person VALUES ('Charlie', 'Bucket', 'M', 'S');
INSERT INTO test.person VALUES ('Grandpa', 'Joe', 'M', NULL);
INSERT INTO test.person VALUES ('Veruca', 'Salt', NULL, 'S');
INSERT INTO test.person VALUES ('Augustus', 'Gloop', 'M', 'S');
INSERT INTO test.person VALUES ('Micheal', 'Teevee', 'M', 'S');
INSERT INTO test.person VALUES ('Violet', 'Beaureguard', 'M', 'S');

SELECT * FROM test.person;
/*
first_name | last_name | gender | marital_status
------------+-------------+--------+----------------
Charlie | Bucket | M | S
Grandpa | Joe | M |
Veruca | Salt | | S
Augustus | Gloop | M | S
Micheal | Teevee | M | S
Violet | Beaureguard | M | S
(6 rows)
*/

SELECT auditor.create_auditor('test', 'person');

UPDATE test.person set marital_status = 'M' WHERE last_name = 'Joe';
SELECT * FROM test.person;
/*
first_name | last_name | gender | marital_status
------------+-------------+--------+----------------
Charlie | Bucket | M | S
Veruca | Salt | | S
Augustus | Gloop | M | S
Micheal | Teevee | M | S
Violet | Beaureguard | M | S
Grandpa | Joe | M | M
(6 rows)
*/
SELECT * FROM auditor.test_person;
/*
first_name | last_name | gender | marital_status | update_action | update_date | update_user
------------+-----------+--------+----------------+---------------+----------------------------+-------------
Grandpa | Joe | M | | UPDATE | 2005-09-20 03:26:23.063965 | postgres
(1 row)
*/

UPDATE test.person set first_name = 'Joe', last_name = 'Bucket' WHERE last_name = 'Joe';
SELECT * FROM test.person;
/*
UPDATE 1
first_name | last_name | gender | marital_status
------------+-------------+--------+----------------
Charlie | Bucket | M | S
Veruca | Salt | | S
Augustus | Gloop | M | S
Micheal | Teevee | M | S
Violet | Beaureguard | M | S
Joe | Bucket | M | M
(6 rows)
*/
SELECT * FROM auditor.test_person;
/*
first_name | last_name | gender | marital_status | update_action | update_date | update_user
------------+-----------+--------+----------------+---------------+----------------------------+-------------
Grandpa | Joe | M | | UPDATE | 2005-09-20 03:26:23.063965 | postgres
Grandpa | Joe | M | M | UPDATE | 2005-09-20 03:26:23.13654 | postgres
(2 rows)
*/

UPDATE test.person set gender = 'F' WHERE last_name = 'Salt';
SELECT * FROM test.person;
/*
first_name | last_name | gender | marital_status
------------+-------------+--------+----------------
Charlie | Bucket | M | S
Augustus | Gloop | M | S
Micheal | Teevee | M | S
Violet | Beaureguard | M | S
Joe | Bucket | M | M
Veruca | Salt | F | S
(6 rows)
*/
SELECT * FROM auditor.test_person;
/*
first_name | last_name | gender | marital_status | update_action | update_date | update_user
------------+-----------+--------+----------------+---------------+----------------------------+-------------
Grandpa | Joe | M | | UPDATE | 2005-09-20 03:26:23.063965 | postgres
Grandpa | Joe | M | M | UPDATE | 2005-09-20 03:26:23.13654 | postgres
Veruca | Salt | | S | UPDATE | 2005-09-20 03:26:23.175714 | postgres
(3 rows)
*/

DELETE FROM test.person WHERE last_name = 'Salt';
SELECT * FROM test.person;
/*
first_name | last_name | gender | marital_status
------------+-------------+--------+----------------
Charlie | Bucket | M | S
Augustus | Gloop | M | S
Micheal | Teevee | M | S
Violet | Beaureguard | M | S
Joe | Bucket | M | M
(5 rows)
*/
SELECT * FROM auditor.test_person;
/*
first_name | last_name | gender | marital_status | update_action | update_date | update_user
------------+-----------+--------+----------------+---------------+----------------------------+-------------
Grandpa | Joe | M | | UPDATE | 2005-09-20 03:26:23.063965 | postgres
Grandpa | Joe | M | M | UPDATE | 2005-09-20 03:26:23.13654 | postgres
Veruca | Salt | | S | UPDATE | 2005-09-20 03:26:23.175714 | postgres
Veruca | Salt | F | S | DELETE | 2005-09-20 03:26:23.201887 | postgres
(4 rows)
*/

/* END EXAMPLE */