I converted some tables from individual definitions to use object
inheritance and now get failures of a foreign key constraint.
Here is the file which creates schema tst and the tables:
++++++++++++++++ begin
DROP SCHEMA tst CASCADE;
CREATE SCHEMA tst;
set search_path = tst;
CREATE TABLE a (
a SERIAL PRIMARY KEY
);
CREATE TABLE aa (
payload TEXT
) INHERITS (a);
CREATE TABLE b (
a INT REFERENCES a PRIMARY KEY
);
CREATE FUNCTION new_aa(TEXT) RETURNS INT AS '
DECLARE
a_id INT;
dummy INT;
BEGIN
INSERT INTO aa (payload) VALUES ($1);
SELECT INTO a_id currval(\'a_a_seq\');
SELECT INTO dummy a FROM b WHERE a = a_id;
IF NOT FOUND THEN
INSERT INTO b (a) VALUES (a_id);
END IF;
RETURN a_id;
END
' LANGUAGE plpgsql;
CREATE TABLE xx (
x SERIAL PRIMARY KEY,
payload TEXT
);
CREATE TABLE y (
x INT REFERENCES xx PRIMARY KEY
);
CREATE FUNCTION new_xx(TEXT) RETURNS INT AS '
DECLARE
x_id INT;
dummy INT;
BEGIN
INSERT INTO xx (payload) VALUES ($1);
SELECT INTO x_id currval(\'xx_x_seq\');
SELECT INTO dummy x FROM y WHERE x = x_id;
IF NOT FOUND THEN
INSERT INTO y (x) VALUES (x_id);
END IF;
RETURN x_id;
END
' LANGUAGE plpgsql;
++++++++++++++++ end
Table aa inherits its primary key from table a, and table b references
a's primary key.
Tables xx and y follow aa and b without the inheritance.
When I try "SELECT new_xx('howdy')", no pronlem:
new_xx
--------
1
(1 row)
When I try "SELECT new_aa('doody')", I get this error:
ERROR: insert or update on table "b" violates foreign key constraint "$1"
DETAIL: Key (a)=(1) is not present in table "a".
This is 7.4.2.
I am by no means an expert on SQL or PostgreSQL. I have been poking
at it, learning outer joins, triggers, rules, etc, and if I am doing
something incredibly stupid here, I'd appreciate knowing. Searching
the tech docs for inheritance did not find anything useful.
--
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o
