Inherited tables, triggers, and schemas...

Поиск
Список
Период
Сортировка
От Net Virtual Mailing Lists
Тема Inherited tables, triggers, and schemas...
Дата
Msg-id 20050308053029.25200@mail.net-virtual.com
обсуждение исходный текст
Ответы Re: Inherited tables, triggers, and schemas...  ("Net Virtual Mailing Lists" <mailinglists@net-virtual.com>)
Список pgsql-general
Hello,

I have a question about inherited tables w/r to triggers... Hopefully
this will make some sense... (I'll try to keep the schema example as
simple as possible):


Given the follow schema/tables:

-- public stuff
SET search_path = public, pg_catalog;
CREATE TABLE customer(
    customer_id SERIAL,
    customer_notification INTEGER,
    CONSTRAINT customer_notification CHECK ((((notification = 0) OR
(notification = 1)) OR (notification = 2))),

);
CREATE TABLE shipto (
    trans_id INTEGER
);


CREATE FUNCTION del_customer() RETURNS "trigger"
    AS '
begin
  delete from shipto where trans_id = old.id;
  return NULL;
end;
'
    LANGUAGE plpgsql;



-- test schema
CREATE SCHEMA test;
SET search_path = test, pg_catalog;
CREATE TABLE customer () INHERITS (public.customer);
CREATE TABLE proposals (
    proposal_id PRIMARY KEY,
    user_id INTEGER REFERENCES customer(name)
);
-- test2 schema
CREATE SCHEMA test2;
SET search_path = test2, pg_catalog;
CREATE TABLE customer () INHERITS (test.customer);
CREATE TABLE proposals () INHERITS (test.proposals);


Here are some questions:

#1. With regards to inserts/update/deletes on test.customer and
test2.customer, will the trigger on public.customer fire?  In this case
the shipto table does not exist in either schema test or test2, but I
need to make sure the trigger will fire when modifications are made to
the customer table in those schemas.

#2. Will inserts on the inherited table increment the user_id in the
public.customer table? (I'm fairly sure that it will, just wnat to be
certain.)

#3. With regards to the constraint, will it applied to the inherited
tables (I.E. only able to insert customer_notification when it contains a
value of 0, 1, or 2?) (I am fairly certain it will, but want to be sure!)

#4. This is where I get really confused.. :-(  With regards to the
REFERENCES customer(name), does this apply to test2.proposals with
regards to the test2.customer table?  Would it be possible to insert a
proposal that contained a user_id contained in test.customer, but not
test2.customer?....  I just don't understand the documentation on this
issue of foreign keys and what is actually inherited...

Is there someplace I can look for a more thorough explanation of how
postgres handles inheritance?


Thanks as always!

- Greg



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

Предыдущее
От: "Hagop Hagopian"
Дата:
Сообщение: Re: [CYGWIN] PostgreSQL installation problem on Windows XP Home
Следующее
От: "Net Virtual Mailing Lists"
Дата:
Сообщение: Re: Inherited tables, triggers, and schemas...