Temporary solution around inheritance/fireign key problem.

Hello all!

I create a temporary solution to solve problem with
inheritance/fireign
key behavior and I want to share this solution with Postgres users.

I want to use this solution for my internal project.
and if somebody from the experts can review this I will appreciate.

I am not sure about validity of foreign_key_* functions which
provide the same functionality as RI_FKey*

Entire docs in postgresql_autodoc format here
http://www.pathcom.com/~pvi/pgsql_inherit_complete/

Entire test package here
http://www.pathcom.com/~pvi/pgsql_inherit_complete/pgsql_inh erit_complete.tgz

This is README file.

# $Id: README,v 1.3 2004/09/07 19:56:42 pvi Exp $

Architecture restrictions
~~~~~~~~~~~~~~~~~
1) if a table in the hierarchy has a primary key this key must be
surrogate
2) all tables in the same hierarchy must have the same source of
primary key.
in the example below there are following tables: table_a1, table_b,
table_c, table_bb,
table_bbb, "table cc", "table ccc" has the same source of
primary key - sequence table_a1_id_seq.
3) the surrogate key can not be generated by the user.

mydb=# INSERT INTO inherit_tests.table_a1 ( id, f_a1 ) VALUES ( 13,
'test1' );
ERROR: id is surrogate key, you can not generate it

instead the folliwng statement should be used:

mydb=# INSERT INTO inherit_tests.table_a1 ( f_a1 ) VALUES ( 'test1'
);
id
----
18
(1 row)
RULE get_pkey_on_insert will return generated primary key

4) due to the primary key is surrogate, only 'ON UPDATE NOACTION' is
supported.

Current implementation restrictions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1)
Primary key name must be 'id', it is hard coded.
This restriction exists until the problem with old.id in plpgsql
trigger
is not resolved.The trigger uses foreign_key_* functions. The problem
can be
solved by rewriting the functions on 'C','pltcl' etc.
How to resolve this problem using PLPGSQL I do not know.

2) SQL RULEs are not copy from base table to derived.

Object modificators
~~~~~~~~~~~~~~~~~~~
The package supports the following modificators:

private - applicable for triggers, indexes, foreign key constraints
abstract - applicable for tables
final - applicable for foreign key constraints

To use the modificators include '' tag into SQL comment
for database object. The package uses only the ' tag in
the
comment, all others words are ignored. The syntaxic of the comment
should
looks like:
COMMENT ON IS 'some stringsome other
string';

For example, the table 'common_system.accounting' is declared as
abstract.
mydb=# COMMENT ON TABLE common_system.accounting IS '
superclass';
mydb=# \dt+ common_system.accounting
List of relations
Schema | Name | Type | Owner | Description
---------------+------------+-------+----------+------------ ------------
common_system | accounting | table | truemail |
superclass
(1 row)

Example
~~~~~~~
The following example displays the usage of the package.

+----------------------+
| schema common_system |
+-----------------+----------------------+------------------ -----------+
|
|
| accounting ( abstract )
|
| |
|
| |
|
| I
|
| N foreign_key_*( )
|
| H
|
| E inherit_complete( )
|
| R
|
| I and some others util functions
|
| T
|
| |
|
| +------------------------------------+
|
| | / \
|
| | / \
|
+------------|---------------------------------/-----\------ -----------+
| +----------------------+ / \
| | schema inherit_tests | / \
+------------|----+----------------------+--/-----------\--- -----------+
| \ (schema inherit_tests) \
|
| \ / \
|
| table_a1<---------FK1(id - a1_id)----------table_d1
|
| / \ / / ^ \
|
| table_b table_c / table_e | table_f
|
| / \ | / | |
|
| table_bb table_bbb "table cc" / table_ee |
|
| | / | |
|
| "table ccc" / table_eee |
|
| / |
|
| +-/--------FK2(d1_id - id)--+
|
| |/
|
| table_g1
|
| / \
|
| table_h table_i
|
|
|
+----------------------------------------------------------- -----------+

FK1 - FOREIGN KEY (a1_id) REFERENCES inherit_tests.table_a1(id)
ON DELETE NOACTION ONUPDATE NOACTION

FK2 - FOREIGN KEY (d1_id) REFERENCES inherit_tests.table_d1(id)
ON DELETE CASCADE ONUPDATE NOACTION

To fix the current inconsistence of the PostgreSQL implementation of
inheritance
the following statement have to be run.
mydb=# SELECT common_system.inherit_complete();
The function should be executed after the
database schema creation, but before inserting data.

The inherit_complete function executes the following steps:

1) If table has a abstract modificator, the followng trigger is
created:
".abstract" BEFORE INSERT OR DELETE OR UPDATE ON

FOR EACH STATEMENT EXECUTE PROCEDURE
common_system.make_db_class_abstract()

2) If the base table has non-private index, create the same index for
all derived tables.

3) Drop the default value for primary key column for all tables in the
hierarchy.
The next trigger is created instead of the default value.

"01_protect_surrogate_pkey" BEFORE INSERT OR UPDATE ON
FOR EACH ROW EXECUTE PROCEDURE
common_system.protect_surrogate_pkey(, )

4) The rule is created for returning the generated primary key

"_get_pkey_on_insert" AS ON INSERT TO
DO SELECT currval() AS id LIMIT 1

5) If the foreign key constraint in the base table dosn't have private
or final modificator,
it will be replaced to two constraint triggers on reftable.
On the original table the constraint check will be also created.
If the foreign key constraint in the base table has final modificator,
the same foreign key will be
created on the derived tables.

on the reftable create triggers
AFTER DELETE ON NOT DEFERRABLE
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
common_system.foreign_key_(,