CREATE TABLE "employees" (
  "emp_uid" SERIAL,
  "emp_fam" TEXT DEFAULT 'н е  и з в е с т н о'::text NOT NULL,
  "emp_name" TEXT DEFAULT 'н е  и з в е с т н о'::text NOT NULL,
  "emp_otch" TEXT DEFAULT 'н е  и з в е с т н о'::text NOT NULL,
  "emp_was_born" DATE,
  "emp_email" TEXT,
  "emp_icq_num" VARCHAR(9),
  "emp_sex" CHAR(1),
  CONSTRAINT "employees_pkey" PRIMARY KEY("emp_uid")
) WITH OIDS;
 
CREATE TABLE "organization" (
  "organization_uid" SERIAL,
  "organization_name" TEXT NOT NULL,
  "organization_www_site" TEXT,
  "organization_email" TEXT,
  "organization_phone" VARCHAR(7),
  "organization_fax" VARCHAR(7),
  CONSTRAINT "organization_pkey" PRIMARY KEY("organization_uid")
) WITH OIDS;
 
CREATE TABLE "employees_post" (
  "post_uid" SERIAL,
  "post_name" TEXT NOT NULL,
  "post_short_name" TEXT,
  CONSTRAINT "employees_post_pkey" PRIMARY KEY("post_uid")
) WITH OIDS;
 
CREATE TABLE "organization_employees" (
  "organization_uid" INTEGER NOT NULL,
  "emp_uid" INTEGER NOT NULL,
  "org_boss" BOOLEAN DEFAULT false NOT NULL,
  "post_uid" INTEGER,
  CONSTRAINT "organization_employees_pkey" PRIMARY KEY("organization_uid", "emp_uid"),
  CONSTRAINT "organization_employees_post_fk" FOREIGN KEY ("post_uid")
    REFERENCES "employees_post"("post_uid")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE,
  CONSTRAINT "organization_employees_to_employees_fk" FOREIGN KEY ("emp_uid")
    REFERENCES "employees"("emp_uid")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE,
  CONSTRAINT "organization_employees_to_organization_fk" FOREIGN KEY ("organization_uid")
    REFERENCES "organization"("organization_uid")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE
) WITH OIDS;

CREATE VIEW "view1" (
    emp_uid,
    emp_fam,
    emp_name,
    emp_otch,
    emp_was_born,
    emp_email,
    emp_icq_num,
    emp_sex,
    organization_uid,
    organization_name,
    org_boss,
    post_uid,
    post_name)
AS
SELECT employees.emp_uid, employees.emp_fam, employees.emp_name,
    employees.emp_otch, employees.emp_was_born, employees.emp_email,
    employees.emp_icq_num, employees.emp_sex,
    organization_employees.organization_uid, organization.organization_name,
    organization_employees.org_boss, organization_employees.post_uid,
    employees_post.post_name
FROM employees
LEFT JOIN organization_employees ON employees.emp_uid = organization_employees.emp_uid
LEFT JOIN organization ON organization_employees.organization_uid = organization.organization_uid
LEFT JOIN employees_post ON organization_employees.post_uid = employees_post.post_uid;

CREATE RULE "view1_update_rl" AS ON UPDATE TO "view1"
DO INSTEAD (
UPDATE employees SET
(emp_fam,
emp_name,
emp_otch,
emp_was_born,
emp_email,
emp_icq_num,
emp_sex)=
(NEW.emp_fam,
NEW.emp_name,
NEW.emp_otch,
NEW.emp_was_born,
NEW.emp_email,
NEW.emp_icq_num,
NEW.emp_sex)
WHERE emp_uid=NEW.emp_uid
RETURNING employees.*;
UPDATE organization_employees SET
(org_boss,
post_uid)=
(NEW.org_boss,
NEW.post_uid)
WHERE organization_uid=NEW.organization_uid AND emp_uid=NEW.emp_uid
RETURNING organization_employees.*,
(SELECT organization_name FROM organization
WHERE organization.organization_uid=organization_employees.organization_uid),
(SELECT post_name FROM employees_post
WHERE employees_post.post_uid = organization_employees.post_uid);
);

CREATE RULE "view1_insert_rl" AS ON INSERT TO "view1"
DO INSTEAD (
INSERT INTO employees (emp_uid, emp_fam, emp_name,
    emp_otch, emp_was_born, emp_email,
    emp_icq_num, emp_sex)
VALUES (NEW.emp_uid, NEW.emp_fam, NEW.emp_name,
    NEW.emp_otch, NEW.emp_was_born, NEW.emp_email,
    NEW.emp_icq_num, NEW.emp_sex) RETURNING employees.*;
INSERT INTO organization_employees (organization_uid, emp_uid,
    org_boss, post_uid) VALUES (NEW.organization_uid, NEW.emp_uid,
    NEW.org_boss, NEW.post_uid) RETURNING organization_employees.*,
    (SELECT organization.organization_name FROM organization
    WHERE organization.organization_uid = organization_employees.organization_uid),
    (SELECT employees_post.post_name FROM employees_post
    WHERE employees_post.post_uid = organization_employees.post_uid);
);

CREATE RULE "view1_update_rl" AS ON UPDATE TO "view1"
DO INSTEAD (
UPDATE employees SET
(emp_fam,
emp_name,
emp_otc,
emp_was_born,
emp_email,
emp_icq_num,
emp_sex)=
(NEW.emp_fam,
NEW.emp_name,
NEW.emp_otch,
NEW.emp_was_born,
NEW.emp_email,
NEW.emp_icq_num,
NEW.emp_sex)
WHERE emp_uid=NEW.emp_uid
RETURNING employees.emp_fam,
employees.emp_name,
employees.emp_otch,
employees.emp_was_born,
employees.emp_email,
employees.emp_icq_num,
employees.emp_sex;
UPDATE organization_employees SET
(org_boss,
post_uid)=
(NEW.org_boss,
NEW.post_uid)
WHERE organization_uid=NEW.organization_uid AND emp_uid=NEW.emp_uid
RETURNING organization_employees.org_boss,
organization_employees.post_uid,organization_employees.organization_uid,organization_employees.emp_uid,
(SELECT organization_name FROM organization
WHERE organization.organization_uid=organization_employees.organization_uid),
(SELECT post_name FROM employees_post
WHERE employees_post.post_uid = organization_employees.post_uid);
);

CREATE VIEW "public"."employees_view" (
    emp_uid,
    emp_fam,
    emp_name,
    emp_otch,
    emp_was_born,
    emp_email,
    emp_icq_num,
    emp_sex)
AS
SELECT employees.emp_uid, employees.emp_fam, employees.emp_name,
    employees.emp_otch, employees.emp_was_born, employees.emp_email,
    employees.emp_icq_num, employees.emp_sex
FROM employees;
 
CREATE RULE "employees_view_ins" AS ON INSERT TO "public"."employees_view"
DO INSTEAD (
INSERT INTO employees (emp_fam, emp_name, emp_otch, emp_was_born, emp_email, emp_icq_num, emp_sex)
  VALUES (new.emp_fam, new.emp_name, new.emp_otch, new.emp_was_born, new.emp_email, new.emp_icq_num, new.emp_sex)
  RETURNING employees.emp_uid, employees.emp_fam, employees.emp_name, employees.emp_otch, employees.emp_was_born, employees.emp_email, employees.emp_icq_num, employees.emp_sex;
);

INSERT INTO public.employees (emp_fam,emp_name,emp_otch)
VALUES ('Иванов',DEFAULT,DEFAULT),
(DEFAULT,DEFAULT,DEFAULT)
RETURNING *;

INSERT INTO public.employees_view (emp_fam,emp_name,emp_otch)
VALUES ('Иванов',DEFAULT,DEFAULT),
(DEFAULT,DEFAULT,DEFAULT)
RETURNING *;