Re: Inherited tables, triggers, and schemas...

Hello,


Sorry, I forgot the trigger:

-- 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 TRIGGER del_customer
    AFTER DELETE ON customer
    FOR EACH ROW
    EXECUTE PROCEDURE del_customer();

- Greg

>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