---------------------------------------------

CREATE TABLE territory (
    terr_id integer NOT NULL PRIMARY KEY,
    name character varying NOT NULL
);

COPY territory (terr_id, name) FROM stdin;
40	Foreign
\.

------------------------------------------------

CREATE TABLE org (
    org_id integer NOT NULL PRIMARY KEY,
    org_name character varying,
    terr_id integer REFERENCES territory(terr_id)
);

COPY org (org_id, org_name, terr_id) FROM stdin;
3152	District1	40
\.

---------------------------------------------------

CREATE TABLE orders (
    ordnum integer NOT NULL PRIMARY KEY,
    org_id integer REFERENCES org(org_id)
);

COPY orders (ordnum, org_id) FROM stdin;
30129	3152
\.

---------------------------------------------------

CREATE TABLE register (
    ttype character(2) NOT NULL,
    ropnum integer NOT NULL,
    CONSTRAINT register_ttype_check CHECK ((ttype = ANY (ARRAY['ad'::bpchar, 'bf'::bpchar, 'or'::bpchar, 'po'::bpchar, 'pt'::bpchar, 'rq'::bpchar, 'wm'::bpchar, 'wt'::bpchar])))
);

COPY register (ttype, ropnum) FROM stdin;
or	30129
\.

--------------------------------------------------

CREATE VIEW org_view AS
    SELECT co.org_id
        ,co.org_name
        ,ct.name     as tername
    from    org    co
        left join   territory   ct  on ct.terr_id = co.terr_id;

------------------------------------------------------

CREATE VIEW register_orders_view AS
    SELECT m.ttype
        , m.ropnum
        , co.org_name AS shop_name 
FROM register m 
	JOIN orders oh ON ((oh.ordnum = m.ropnum)) 
	JOIN org_view co ON ((co.org_id = oh.org_id)) 
WHERE m.ttype = 'or';