I am trying to build my first Postgres database schema involving inheritance. I am aware of the foreign key problem as discussed in PostgreSQL foreign key not existing, issue of inheritance?. However, the answers on this question did not really give an example for the solution so I came up with my own (inspired by http://people.planetpostgresql.org/dfetter/index.php?/archives/51-Partitioning-Is-Such-Sweet-Sorrow.html):
CREATE TABLE a (
id SERIAL PRIMARY KEY INITIALLY DEFERRED,
foo TEXT
);
CREATE TABLE b (
PRIMARY KEY(id),
a_number REAL
) inherits(a);
CREATE TABLE c (
PRIMARY KEY(id),
a_number INTEGER
) inherits(a);
-- SELECT * FROM ONLY a; will always return an empty record.
CREATE TABLE x (
a_id INTEGER NOT NULL,
bar TEXT
);
CREATE TABLE xb (
FOREIGN KEY( a_id ) REFERENCES b INITIALLY DEFERRED
) inherits(x);
CREATE TABLE xc (
FOREIGN KEY( a_id ) REFERENCES c INITIALLY DEFERRED
) inherits(x);
In order to perform a working INSERT INTO x I defined the following trigger:
CREATE FUNCTION marshal_x()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
ref_table varchar;
BEGIN
SELECT INTO ref_table p.relname FROM a, pg_class p WHERE a.id = NEW.a_id AND a.tableoid = p.oid;
IF ref_table = 'b'
THEN INSERT INTO xb ( a_id, bar ) VALUES ( NEW.a_id, NEW.bar );
ELSIF ref_table = 'c'
THEN INSERT INTO xc ( a_id, bar ) VALUES ( NEW.a_id, NEW.bar );
END IF;
RETURN NULL;
END;
$$;
CREATE TRIGGER insert_x_trg
BEFORE INSERT ON x
FOR EACH ROW
EXECUTE PROCEDURE marshal_x();
EDIT: Does anyone see a problem with this definition, which are not obvious to my untrained eye?
Assume table c to be empty. Would the performance of
SELECT * FROM a JOIN x ON a.id= x.a_id;
be the same as
SELECT * FROM b JOIN x ON b.id= x.a_id;
? Many thanks in advance.
Isam