Rerefences to derived rows - Mailing list pgsql-general

Hi ...

I have been trying to use the OO features in the PostgreSQL database but
have some problems regarding references between tables.

Normally it is quite simple to secure the integrity between table rows,
and PostgreSQL will check if the table id is valid, but if I try to make
a reference to a parent table, and then make a derived table row from
this parent, to which I make the reference point to,  it complains.

Hmm, this is near to impossible to describe, so here is an example i
made in hope to get to understand this problem :

---< cut >---
CREATE SEQUENCE tree_seq;

CREATE TABLE tree (
 id    INTEGER DEFAULT NEXTVAL( 'tree_seq' ) PRIMARY KEY NOT NULL,
    name            VARCHAR( 80 ),
 colour   INTEGER
);

CREATE TABLE able_tree (
 id     INTEGER PRIMARY KEY, -- Make sure to enherit
 kind    INTEGER
) INHERITS( tree );

CREATE TABLE frute  (
 tree_id   INTEGER REFERENCES tree( id ),
 name   VARCHAR( 80 ),
    state           INTEGER
);

-- This works, as expectet !
INSERT INTO tree ( name ) VALUES( 'aple tree' );
INSERT INTO frute ( tree_id, name ) VALUES( CURRVAL( 'tree_seq' ),
'first' );

INSERT INTO able_tree ( name ) VALUES( 'august able tree' );

-- This does not work -- why ?
INSERT INTO frute ( tree_id, name ) VALUES( CURRVAL( 'tree_seq' ),
'first' );
INSERT INTO frute ( tree_id, name ) VALUES( CURRVAL( 'tree_seq' ),
'second' );

-- cleanup in database
DROP TABLE frute;
DROP TABLE able_tree;
DROP TABLE tree;

DROP SEQUENCE tree_seq;
---< cut >---

The output from running this in "psql" is this :

---< cut >---
CREATE
psql:test.sql:8: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit
index 'tree_pkey' for table 'tree'
CREATE
psql:test.sql:13: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit
index 'able_tree_pkey' for table 'able_tree'
psql:test.sql:13: NOTICE:  CREATE TABLE: merging attribute "id" with
inherited definition
CREATE
psql:test.sql:19: NOTICE:  CREATE TABLE will create implicit trigger(s)
for FOREIGN KEY check(s)
CREATE
INSERT 30721 1
INSERT 30722 1
INSERT 30723 1
psql:test.sql:28: ERROR:  <unnamed> referential integrity violation -
key referenced from frute not found in tree
psql:test.sql:29: ERROR:  <unnamed> referential integrity violation -
key referenced from frute not found in tree
psql:test.sql:32: NOTICE:  DROP TABLE implicitly drops referential
integrity trigger from table "tree"
psql:test.sql:32: NOTICE:  DROP TABLE implicitly drops referential
integrity trigger from table "tree"
DROP
DROP
DROP
DROP
---< cut >---

First, its quite annoying with these "NOTICE" this as log as I don't
know how to remove them.

Second, why cant I refer to the derived table but only to the parent,
this looks quite normal in a pure OO sense.

Anyway, I hope someone will take the time to give me a hint about, what
I have done wrong.

/BL