Confusion about inheritance

Поиск
Список
Период
Сортировка
От felix-lists-pgsql@crowfix.com
Тема Confusion about inheritance
Дата
Msg-id 16546.18945.524455.249869@crowfix.com
обсуждение исходный текст
Ответы Re: Confusion about inheritance  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Список 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@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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Fred Fung"
Дата:
Сообщение: dbmirror
Следующее
От: felix-lists-pgsql@crowfix.com
Дата:
Сообщение: Confusion about inheritance -- NEVER MIND