<!--

$PostgreSQL: pgsql/doc/src/sgml/advanced.sgml,v 1.43 2004/08/07 19:53:48 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/advanced.sgml,v 1.44 2004/08/08 01:51:05 momjian Exp $

-->

<chapter id="tutorial-advanced">

);

CREATE TABLE weather (

city varchar(80) references cities,

city varchar(80) references cities(city),

temp_lo int,

temp_hi int,

prcp real,

</indexterm>

<para>

Inheritance is a concept from object-oriented databases. It opens

up interesting new possibilities of database design.

Inheritance is a concept from object-oriented databases. Although

it opens up interesting new possibilities of database design,

this feature is currently unmaintained and known to have serious

gotchas in its foreign key implementation, which you should take

care to avoid. The fixes below are probably version-specific and may

require updates in the future.

</para>

<para>

The example below illustrates the gotcha.

</para>

<para>

<programlisting>

BEGIN;

CREATE TABLE foo (

foo_id SERIAL PRIMARY KEY

);

CREATE TABLE parent (

parent_id SERIAL PRIMARY KEY

, foo_id INTEGER NOT NULL REFERENCES foo(foo_id) ON DELETE CASCADE

, parent_1_text TEXT NOT NULL

);

CREATE TABLE child_1 (

child_1_text TEXT NOT NULL

) INHERITS(parent);

CREATE TABLE child_2 (

child_2_text TEXT NOT NULL

) INHERITS(parent);

INSERT INTO foo VALUES(DEFAULT);

INSERT INTO child_1 (foo_id, parent_1_text, child_1_text)

VALUES (currval('public.foo_foo_id_seq'), 'parent text 1', 'child_1 text 1');

INSERT INTO foo VALUES(DEFAULT);

INSERT INTO child_1 (foo_id, parent_1_text, child_1_text)

VALUES (currval('public.foo_foo_id_seq'), 'parent text 2', 'child_1 text 2');

INSERT INTO foo VALUES(DEFAULT);

INSERT INTO child_2 (foo_id, parent_1_text, child_2_text)

VALUES (currval('foo_foo_id_seq'), 'parent text 3', 'child_2 text 1');

DELETE FROM foo WHERE foo_id = 1;

SELECT * FROM parent;

parent_id | foo_id | parent_1_text

-----------+--------+---------------

1 | 1 | parent text 1

2 | 2 | parent text 2

3 | 3 | parent text 3

(3 rows)

SELECT * FROM child_1;

parent_id | foo_id | parent_1_text | child_1_text

-----------+--------+---------------+----------------

1 | 1 | parent text 1 | child_1 text 1

2 | 2 | parent text 2 | child_1 text 2

(2 rows)

ROLLBACK;

</programlisting>

</para>

<para>

Oops!! None of parent, child or foo should have any rows with

foo_id = 1 in them. Here is a way to fix the above tables.

</para>

<para>

To fix the gotcha, you must put foreign key constraints on each of

the child tables, as they will not be automatically inherited as

you might expect.

</para>

<para>

<programlisting>

ALTER TABLE child_1 ADD CONSTRAINT cascade_foo

FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON DELETE CASCADE;

ALTER TABLE child_2 ADD CONSTRAINT cascade_foo

FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON DELETE CASCADE;

</programlisting>

</para>

<para>

Let's create two tables: A table <classname>cities</classname>

and a table <classname>capitals</classname>. 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:

That caveat out of the way, let's create two tables: A table

<classname>cities</classname> and a table

<classname>capitals</classname>. 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:

<programlisting>

CREATE TABLE capitals (

</programlisting>

This works OK as far as querying goes, but it gets ugly when you

need to update several rows,to name one thing.

need to update several rows,for one thing.

</para>

<para>

<!--

$PostgreSQL: pgsql/doc/src/sgml/query.sgml,v 1.35 2003/11/29 19:51:37 pgsql Exp $

$PostgreSQL: pgsql/doc/src/sgml/query.sgml,v 1.36 2004/08/08 01:51:05 momjian Exp $

-->

<chapter id="tutorial-sql">

<programlisting>

SELECT * FROM weather;

</programlisting>

(here <literal>*</literal> means <quote>all columns</quote>) and

the output should be:

(here <literal>*</literal> means <quote>all columns</quote>.

Note: While <literal>SELECT *</literal> is useful for off-the-cuff

queries, it is considered bad style in production code for

maintenance reasons) and the output should be:

<screen>

city | temp_lo | temp_hi | prcp | date

---------------+---------+---------+------+------------