Search Postgresql Archives

Confusion about inheritance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux