Confusion about inheritance

From: felix-lists-pgsql(at)crowfix(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: Confusion about inheritance
Date: 2004-05-12 16:00:01
Message-ID: 16546.18945.524455.249869@crowfix.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)crowfix(dot)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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message felix-lists-pgsql 2004-05-12 16:16:34 Confusion about inheritance -- NEVER MIND
Previous Message Doug McNaught 2004-05-12 15:37:51 Re: LISTEN/NOTIFY with JDBC