CREATE OR REPLACE VIEW v_doc_orgdoc AS 
 SELECT d.docid, d.ourfirmid, d.doctypeid, d.docregnumber, d.docregdate,
        d.docname,  d.docdescription, d.docformid,
        od.orgdocid,
        od.docstartdate, od.docclosedate, od.isdocfileoutsave,
        od.docfilename, od.docfile, od.docversion,
        d.isactive,
        d.modifyuser, d.modifyplace, d.modifydate,
        d.createuser, d.createplace, d.createdate
   FROM doc d, orgdoc od
   WHERE
        d.docid=od.orgdocid;

ALTER TABLE v_doc_orgdoc OWNER TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE v_doc_orgdoc TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES ON TABLE v_doc_orgdoc TO "ctuUserRole";


CREATE OR REPLACE RULE v_doc_orgdoc_ins AS ON INSERT TO v_doc_orgdoc
        DO INSTEAD (
        INSERT INTO doc 
                (
                docid, ourfirmid, doctypeid, docregnumber, docregdate,
                docname,  docdescription, docformid,
                isactive
                )
        VALUES (
                NEW.docid, NEW.ourfirmid, NEW.doctypeid, NEW.docregnumber, 
                NEW.docregdate,
                NEW.docname,  NEW.docdescription, NEW.docformid,
                NEW.isactive
                );                    

        INSERT INTO orgdoc(
                orgdocid, 
                docstartdate, docclosedate, isdocfileoutsave,
                docfilename, docfile, docversion
                )
        VALUES (
                NEW.orgdocid, 
                NEW.docstartdate, NEW.docclosedate, NEW.isdocfileoutsave,
                NEW.docfilename, NEW.docfile, NEW.docversion
                )
        );
CREATE OR REPLACE RULE v_doc_orgdoc_upd AS ON UPDATE TO v_doc_orgdoc
        DO INSTEAD (
        UPDATE orgdoc SET
                docstartdate=NEW.docstartdate, docclosedate=NEW.docclosedate, isdocfileoutsave=NEW.isdocfileoutsave,
                docfilename=NEW.docfilename, docfile=NEW.docfile, docversion=NEW.docversion
        WHERE
                orgdocid=NEW.orgdocid;
        UPDATE doc SET 
                ourfirmid=NEW.ourfirmid, doctypeid=NEW.doctypeid,
                docregnumber=NEW.docregnumber, docregdate=NEW.docregdate,
                docname=NEW.docname,  docdescription=NEW.docdescription, docformid=NEW.docformid,
                isactive=NEW.isactive
        WHERE
                docid=NEW.docid;
        );        
CREATE OR REPLACE RULE v_doc_orgdoc_del AS ON DELETE TO v_doc_orgdoc
        DO INSTEAD (
        --cтрока с селектом нужна что бы вернуть наружу правильный статус результата команды для ADO
        SELECT orgdoc.orgdocid  FROM orgdoc WHERE orgdoc.orgdocid = old.orgdocid; 
        DELETE FROM orgdoc WHERE orgdoc.orgdocid=OLD.orgdocid;
        DELETE FROM doc WHERE doc.docid=OLD.docid;    
        );

CREATE OR REPLACE FUNCTION do_del_orgdoc(pc_orgdocid character varying)
  RETURNS void AS
$BODY$
BEGIN
        DELETE FROM orgdoc WHERE orgdoc.orgdocid=pc_orgdocid;
        DELETE FROM doc WHERE doc.docid= pc_orgdocid;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST  10 ;
ALTER FUNCTION do_del_orgdoc(character varying) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION do_del_orgdoc(character varying) TO postgres;
GRANT EXECUTE ON FUNCTION do_del_orgdoc(character varying) TO "ctuUserRole";
        
CREATE OR REPLACE RULE v_doc_orgdoc_del AS ON DELETE TO v_doc_orgdoc
        DO INSTEAD( 
        --cтрока с селектом нужна что бы вернуть наружу правильный статус результата команды для ADO
        --SELECT orgdoc.orgdocid  FROM orgdoc WHERE orgdoc.orgdocid = old.orgdocid; 
        --DELETE FROM orgdoc WHERE orgdoc.orgdocid=OLD.orgdocid;
        --DELETE FROM doc WHERE doc.docid= OLD.docid;
        SELECT do_del_orgdoc(OLD.docid);
        );

CREATE OR REPLACE VIEW v_doc_orgdoc AS 
 SELECT d.docid, d.ourfirmid, d.doctypeid, d.docregnumber, d.docregdate, d.docname, d.docdescription, d.docformid, od.orgdocid, od.docstartdate, od.docclosedate, od.isdocfileoutsave, od.docfilename, od.docfile, od.docversion, d.isactive, d.modifyuser, d.modifyplace, d.modifydate, d.createuser, d.createplace, d.createdate
   FROM doc d
   LEFT JOIN orgdoc od ON d.docid = od.orgdocid;