[BUGS] BUG #14517: Inheritance to behave as for OOP

The following bug has been logged on the website:

Bug reference:      14517
Logged by:          Stephane COUTANT
Email address:      scoutant@freesbee.fr
PostgreSQL version: 9.5.0
Operating system:   Windows 10
Description:

Hello,

First of all THANK YOU for this great tool I use for developing my own
project without any problem.

PREAMBLE:
I'm not a DBA but a java developer.
And this email is rather a CR than a bug as PostgreSQL behaves as described
in the documentation.

CONTEXT:
For my project (a payment platform), I use the inheritance capability of the
DBMS as it sticks to my data model (an organisation tree view). And I have
created a tree view that every organisation node inherits to help
manipulating and browsing the entities (the tree consists in child and
parent foreign keys that reference the tree view by itself, classic).

PROBLEM: 
As a java developer, I would have expected parent table to contain the
inherited fields values each time a row is inserted in a child table (as for
objects extending a base class). It works fine for SELECT but the tree view
does not work as expected: foreign key violation (as stated in the
documentation, the INSERT only applies to the child table).

WORKAROUND:
The only workaround I have found is to duplicate (by triggers) in parent
table the values of the fields inherited when a row is inserted in the child
table. The result is that I have to cater for duplicates when I read the
parent table (SELECT DISTINCT FROM tree view) and obviously the parent table
size is twice or three times the size it should be.

IMPROVEMENT:
I'm probably not the only user of PostgreQSL having to deal with a data
model in the form of a tree view. And inheritance is particularly useful in
this case. Would it be possible somehow for an inherited field to be valued
in the parent table so that foreign keys and by extension (my guess) other
constraints can apply transparently?
--------------
Below is the scripts to create my database and where you can see the
triggers implementing the workaround.

Note: My project is confidential and I would prefer, if possible, to not
make the script below public.
--------------
    /* ====================================================================
*/
/* Database name:  mobilipay                                            */
/* DBMS name:      PostgreSQL 9.5                                       */
/* PURPOSE:        Creates IDP tables and tree, bank domain             */
/* VERSION:        1.0.0                                                */
/* AUTHOR:         Stephane Coutant                                     */
/* Based on work from www.depesz.com/2008/04/11/my-take-on-trees-in-sql */
/* ==================================================================== */

/* STEP 1 = create table bank_org_units
   STEP 2 = create table agents (see
mobilipay_create_tables_agent_domain.sql)
   STEP 3 = run the rest of this file */

/* NOTE ON INHERITANCE 

   Command                On table    Affects
   UPDATE                child        child and parent
   UPDATE                parent        parent
   INSERT                child        child and parent
   INSERT                parent        parent
   DELETE                child        child and parent
   DELETE               parent      parent and child
   SELECT               child       all rows from parent and child
   SELECT FROM ONLY      parent         all rows from parent
   SELECT                 parent[*]    all rows from parent and child(s) but only with
parent columns

    Triggers are not propagated to children, so "duplicate" parent triggers on
every child pointing to the same function!
    FK are not propagated to children, so create same reference on every
child!
    Contraints are not propagated to children and no workaround exists, avoid
them
    Serial Key as primary key is "luckily" working correctly (all children
share the same next function)

*/
    
/*
 * WARNING: if an index is created on a table and a massive import of data
is realised, then IT IS ABSOLUTELY REQUIRED TO RUN SEVERAL queries on that
table to 
 * populate the index UNTIL the query exec time comes back to a reasonable
time (should be less than 1 sec). Otherwise the first live query will take
possibly hours!!!!
 */


/**************************************************************/
/*        DATA TABLES (as superclass / subclasses)            */
/**************************************************************/

CREATE SCHEMA IF NOT EXISTS mobilipay AUTHORIZATION mobilipay;
--SET SCHEMA mobilipay; to be executed as mobilipay role

/* ========================================================== */
/* TABLE 'bank_org_units':                                    */
/* Superclass of all bank / entities (with fields only needed */
/* for bank_org_units_tree triggers)                          */
/* ========================================================== */

CREATE TYPE bank_entity_type AS ENUM (
    'BANK',                    -- the entity is a parent company
    'ORG_UNIT',                 -- the entity is a node in the organisation
    'BRANCH'                 -- the entity is a branch
);

/* Auto delete cascade (node + childs) when a node is deleted is not in
place.
 * Better approach is let DBMS raises an exception on FK constraint
violation, to force user to move childs to another node 
 * (no orphans) before deleting a node.
 * The FKEY on parent_id can't be set as per workaround explained in
bank_org_units_tree_ai trigger function */

CREATE TABLE bank_org_units (
    /* This bank organisation unit unique identifier (distinct from BIC for
trees) */
    id                            SERIAL PRIMARY KEY,
    /* The bank/entity/branch name, as appearing for end users */ 
    name                        VARCHAR(70) NOT NULL,
    /* The bank organisation node this bank/entity belongs to (null if root)
*/
    parent_id                    INTEGER /* NOT NULL REFERENCES bank_org_units(id) ON DELETE
RESTRICT, */
    /* The concatenated path to that org unit 'name[.name.[name...]]] */
    tree_path                    TEXT
    /* The entity type to ease further processing */
    entity_type                    bank_entity_type NOT NULL,
);

/* To speed up tree manipulation */
CREATE INDEX idx_bank_org_units_parent_id ON bank_org_units (parent_id) ;

/* ========================================================== */
/* TABLE 'bank_entities': INHERITS bank_org_units             */
/* Organisation units common data                             */
/* ========================================================== */

CREATE TYPE bank_entity_status_type AS ENUM (
    'ACTIVE',                                -- creation of the entity
    'SUSPENDED'                              -- bank suspends the service for this
entity (cannot process payment)
);

CREATE TABLE bank_entities (
    /* Adress fields */
    address_type                address_type_enum NOT NULL DEFAULT 'BIZZ',
    department                    VARCHAR(70) NULL,
    sub_department                VARCHAR(70) NULL,
    street_name                    VARCHAR(70) NULL,
    building_number                VARCHAR(16) NULL,
    postal_code                    VARCHAR(16) NULL,
    town_name                    VARCHAR(35) NULL,
    country_sub_division        VARCHAR(35) NULL,
    country                        CHAR(2) NOT NULL, -- pattern [A-Z]{2,2}, eg FR 
    address_lines                VARCHAR[7][70] NULL,
    /* Generic mailbox to adress common requests */
    generic_email                VARCHAR(120) NULL,
    /* References to main contact for claim management, accounting, legal
requests...  */
    main_contact_phone            VARCHAR(20) NULL,    -- pattern CC + NDC + SN
    main_contact_email             VARCHAR(120) NULL,
    main_contact_name             VARCHAR(70) NULL,
    event_notification            BOOLEAN DEFAULT FALSE
) INHERITS (bank_org_units);

/* ========================================================== */
/* TABLE 'banks': INHERITS bank_entities                      */
/* Organisation unit which represents a bank or a branch      */
/* ========================================================== */

CREATE TYPE bank_status_type AS ENUM (
    'ACTIVE',                                -- bank/branch added
    'SUSPENDED',                                    -- sponsor suspends the bank;
the bank cannot process any request from end-users 
    'TERMINATION'                                -- termination requested by the bank
or program sponsor (end of activity or end of subscribtion to the program)
);

CREATE TABLE banks (
    /* The Bank Identification Code or SWIFT Code format ISO 9362 (bank or
branch) */
    bic                            VARCHAR(11) NOT NULL
) INHERITS(bank_entities); 

/* ========================================================== */
/* TABLE 'bank_parent_companies': INHERITS banks               */
/* Organisation unit which represents the top level hierarchy */
/* ========================================================== */

CREATE TABLE bank_parent_companies (
      /* National Adherence Support Organisation (to SEPA SCT, SDD, CSF) the
bank is registered to (only for top level bank) */
      naso                        VARCHAR(70) NULL,
    /* State transition matrix managed programmatically by database trigger
*/
    bank_status                    bank_status_type NOT NULL
) INHERITS(banks);
ALTER TABLE bank_parent_companies ALTER COLUMN entity_type SET DEFAULT
'BANK';
ALTER TABLE bank_parent_companies ADD CONSTRAINT bank_parent_companies_pkey
PRIMARY KEY (id);
ALTER TABLE bank_parent_companies ADD CONSTRAINT
bank_parent_companies_parent_id_fkey FOREIGN KEY (parent_id)
      REFERENCES bank_org_units (id) ON DELETE RESTRICT;

/* ========================================================== */
/* TABLE 'branches': INHERITS banks                           */
/* Organisation unit which represents a branch                */
/* ========================================================== */

CREATE TABLE branches (
    /* Indicator that specifies to redirect calls to specified entity for
queries */
    referee                          integer REFERENCES bank_org_units (id) ON DELETE RESTRICT,
    branch_code                    INTEGER NOT NULL,
    /* State transition matrix managed programmatically by database trigger
*/
    entity_status                bank_entity_status_type NOT NULL
) INHERITS(banks);
ALTER TABLE branches ALTER COLUMN entity_type SET DEFAULT 'BRANCH';
ALTER TABLE branches ADD CONSTRAINT branches_pkey PRIMARY KEY (id);
ALTER TABLE branches ADD CONSTRAINT branches_bic_branch_code_key UNIQUE
(bic, branch_code);
ALTER TABLE branches ADD CONSTRAINT bank_branches_parent_id_fkey FOREIGN KEY
(parent_id)
      REFERENCES bank_org_units (id) ON DELETE RESTRICT;

/* ========================================================== */
/* TABLE 'bank_unqualified_org_units': INHERITS bank_entities */
/* Organisation unit which represents a node in the hierarchy */
/* ========================================================== */

CREATE TABLE bank_unqualified_org_units (
    /* Indicator that specifies to redirect calls to specified entity for
queries */
    referee                          integer REFERENCES bank_org_units (id) ON DELETE RESTRICT,
    /* State transition matrix managed programmatically by database trigger
*/
    entity_status                bank_entity_status_type NOT NULL
) INHERITS(banks);
ALTER TABLE bank_unqualified_org_units ALTER COLUMN entity_type SET DEFAULT
'ORG_UNIT';
ALTER TABLE bank_unqualified_org_units ADD CONSTRAINT
bank_unqualified_org_units_pkey PRIMARY KEY (id);
ALTER TABLE bank_unqualified_org_units ADD CONSTRAINT
bank_unqualified_org_units_parent_id_fkey FOREIGN KEY (parent_id)
      REFERENCES bank_org_units (id) ON DELETE RESTRICT;

/* ========================================================== */
/* TABLE 'bank_profiles': REFERENCES a bank_org_unit (FK)     */
/* The superclass of services profile provided by this bank   */
/* ========================================================== */

CREATE TYPE bank_profile_type AS ENUM (
    'PAYER',                    -- the branch is a bank where customers have
accounts
    'BENEFICIARY'                 -- the branch is a bank where beneficiaries
(merchants) have accounts
);

CREATE TABLE bank_profiles (
    profile_type                bank_profile_type NOT NULL,
    profile_id                    SERIAL PRIMARY KEY,
    /* The bank/branch this profile is attached to (delete profile when
org_unit is deleted) */
    bank_id                        INTEGER REFERENCES bank_org_units(id) ON DELETE CASCADE
);

/* ========================================================== */
/* TABLE 'payer_bank_profiles': INHERIT bank_profiles         */
/* Profile of a payer bank                                    */
/* ========================================================== */

CREATE TABLE payer_bank_profiles (
    /* Issues refund notification if true and mobile transaction is a refund 
       (using specific web service, ISO 20022 cain (not yet defined) or card
an ISO 8583 1100 with processing code = 20 (credit) */
    refund_notification            BOOLEAN NOT NULL DEFAULT FALSE,
    /* Issues reversal notification if true and mobile transaction is a
reversal 
       (using specific web service, ISO 20022 cain (not yet defined) or card
an ISO 8583 1420 */
    reversal_notification        BOOLEAN NOT NULL DEFAULT FALSE
) INHERITS (bank_profiles);
ALTER TABLE payer_bank_profiles ALTER COLUMN profile_type SET DEFAULT
'PAYER';
ALTER TABLE payer_bank_profiles ADD CONSTRAINT payer_bank_profiles_pkey
PRIMARY KEY (profile_id);
ALTER TABLE payer_bank_profiles ADD CONSTRAINT
payer_bank_profiles_bank_id_fkey FOREIGN KEY (bank_id) REFERENCES
bank_org_units(id);


/* ========================================================== */
/* TABLE 'beneficiary_bank_profiles': INHERIT bank_profiles   */
/* Profile of a beneficiary bank                              */
/* ========================================================== */

CREATE TABLE beneficiary_bank_profiles (
) INHERITS (bank_profiles);
ALTER TABLE beneficiary_bank_profiles ALTER COLUMN profile_type SET DEFAULT
'BENEFICIARY';
ALTER TABLE beneficiary_bank_profiles ADD CONSTRAINT
beneficiary_bank_profiles_pkey PRIMARY KEY (profile_id);
ALTER TABLE beneficiary_bank_profiles ADD CONSTRAINT
beneficiary_bank_profiles_bank_id_fkey FOREIGN KEY (bank_id) REFERENCES
bank_org_units(id);

/* ========================================================== */
/* TABLE 'bank_services': REFERENCES a bank_profile (FK)      */
/* The superclass of set of services a bank is providing      */
/* ========================================================== */

CREATE TABLE bank_services (
    service_id                    SERIAL PRIMARY KEY,
    /* The bank/branch providing these services (delete service when profile is
deleted) */
    profile_id                    INTEGER REFERENCES bank_profiles(profile_id) ON DELETE
CASCADE,
    /* To ease the retrieval of the child DTO */
    profile_type                bank_profile_type NOT NULL,
    /* The agent renderring the service */
    primary_agent                INTEGER REFERENCES agents(agent_id) ON DELETE RESTRICT
);

/* ========================================================== */
/* TABLE 'payer_bank_services': INHERITS bank_services        */
/* Services provided by a payer bank                          */
/* ========================================================== */

CREATE TYPE payer_bank_service_type AS ENUM (
    'AUTHORISATION',                            -- (mandatory) invoked by a
Beneficiary bank or its agent to check funds before to effect the payment
    'COMPLETION',                                 -- (mandatory) invoked by a
Beneficiary bank or its agent to notify the outgoing of a previously
authorised transaction @ pos (e.g. for deferred transaction on automated
fuel dispenser) 
    'CANCELLATION',                             -- (mandatory) invoked by a
Beneficiary bank or its agent to notify a cancellation of a previously
authorised transaction
    'REVERSAL',                                 -- (optional) invoked by a
Beneficiary bank or its agent to notify a reversal 
    'CREDIT_TRANSFER',                            -- (mandatory) invoked by a
Beneficiary bank or its agent to generate the Customer Credit Transfer
Initiation(payment) after authorisation
    'ID_SERVICES',                                -- (mandatory) invoked from
customer smartphone to update customer-oriented data
    'EVENT_NOTIFICATION'                        -- (optional) invoked by
Identity Provider to notify the bank about changes in customer or bank
account status.
);

CREATE TABLE payer_bank_services (
    service_type                payer_bank_service_type NOT NULL
) INHERITS (bank_services);
ALTER TABLE payer_bank_services ALTER COLUMN profile_type SET DEFAULT
'PAYER';
ALTER TABLE payer_bank_services ADD CONSTRAINT payer_bank_services_pkey
PRIMARY KEY (service_id);
ALTER TABLE payer_bank_services ADD CONSTRAINT
payer_bank_services_profile_id_fkey FOREIGN KEY (profile_id) REFERENCES
bank_profiles(profile_id);

/* ========================================================== */
/* TABLE 'beneficiary_bank_services': INHERITS bank_services  */
/* Services provided by a beneficiary bank                    */
/* ========================================================== */

CREATE TYPE beneficiary_bank_service_type AS ENUM (
    'CHECKOUT',                                -- (mandatory) Exhibited by the
merchant and captured by the customer smartphone to proceed with a payment
transaction
    'PAYMENT',                    -- (mandatory) invoked by the Merchant Plug-In to proceed
with payment, refund or reversal
    'ID_SERVICES',                    -- (mandatory) invoked by a merchant back office
operator from an Internet browser to update merchant-oriented data
    'CREDIT_TRANSFER',                -- (optional) if the service is delegated to bank�
agent, invoked by the agent to generate the Customer Credit Transfer
Initiation for Refund and Reversal
    'EVENT_NOTIFICATION'                -- (optional) invoked by Identity Provider to
notify the bank about changes in merchant, bank account, entity, retail
location or POS status.
);

CREATE TABLE beneficiary_bank_services (
    service_type                beneficiary_bank_service_type NOT NULL
) INHERITS (bank_services);
ALTER TABLE beneficiary_bank_services ALTER COLUMN profile_type SET DEFAULT
'BENEFICIARY';
ALTER TABLE beneficiary_bank_services ADD CONSTRAINT
beneficiary_bank_services_pkey PRIMARY KEY (service_id);
ALTER TABLE beneficiary_bank_services ADD CONSTRAINT
beneficiary_bank_services_profile_id_fkey FOREIGN KEY (profile_id)
REFERENCES bank_profiles(profile_id);

/* =========================================================== */
/* TABLE 'bank_service_backup': REFERENCES a bank_service (FK) */
/* agent_id used as backup for the service                     */
/* Can define multiple backup.                                */
/* =========================================================== */

CREATE TABLE bank_service_backup (
    url_id                        SERIAL PRIMARY KEY,
    /* The service this url represents */
    service_id                    INTEGER REFERENCES bank_services(service_id) ON DELETE
CASCADE,
    /* The agent renderring the service as backup */
    backup_agent                INTEGER REFERENCES agents(agent_id) ON DELETE RESTRICT
);


/**************************************************************/
/*                          FUNCTIONS                         */
/**************************************************************/

/* Returns all the parent IDs for given orgID. (Used by view[item]Change SQL
orders for the purpose of event notification. */
CREATE OR REPLACE FUNCTION get_bank_parent_org_ids (orgID INTEGER) RETURNS
INTEGER[] AS
$$
    SELECT ARRAY (    SELECT distinct m.id 
                    FROM bank_org_units_tree t 
                    JOIN bank_org_units m ON m.id = t.parent_id
                    WHERE t.child_id = orgID AND depth > 0);
$$
LANGUAGE 'sql';         


/**************************************************************/
/*                 TREE TABLE and TRIGGERS                    */
/**************************************************************/

/* ========================================================== */
/* TABLE 'bank_org_units_tree'                                */
/* Represents the bank organisation in bank/entities/branches */
/* Stores information about all parents of given node         */
/* ========================================================== */

CREATE TABLE bank_org_units_tree (
    id                        SERIAL PRIMARY KEY,
    parent_id                INTEGER NOT NULL REFERENCES bank_org_units (id) ON DELETE
CASCADE,
    child_id                INTEGER NOT NULL REFERENCES bank_org_units (id) ON DELETE
CASCADE,
    depth                    INTEGER NOT NULL,
    UNIQUE (parent_id, child_id)
);
/* To speed up tree manipulations (triggers) */ 
CREATE INDEX idx_bank_org_units_tree_child_depth ON bank_org_units_tree
(child_id, depth);
CREATE INDEX idx_bank_org_unit_parent_id ON bank_org_units_tree
(parent_id);
CREATE INDEX idx_bank_org_unit_child_id ON bank_org_units_tree (child_id);


/* ========================================================== */
/* TRIGGER 'trigger_bank_org_units_tree_ai'                   */
/* Populates the tree on INSERT INTO in bank_org_unit         */

/* ========================================================== */

/*  0) duplicates new row in parent table (otherwise remains empty and
constraint violation on parent_id not existing (tree FK on bank_org_units
id)
 *     Warning : it requires SELECT DISTINCT FROM bank_org_units, if read
occurs in this table, to avoid duplicates
 *                  it also requires to not set parent_id FOREIGN KEY on id in
merchant_org_units otherwise any insert will violate the id primary key
(which is thus duplicated)
 *  1) it inserts row, where both parent_id and child_id are set to the same
value (id of newly inserted object), and depth is set to 0 (as both child
and parent are on the same level)
 *  2) in second insert, copy all rows that parent had as parents, but
modify child_id in these rows to be id of currently inserted row, and
increase depth */
CREATE OR REPLACE FUNCTION bank_org_units_tree_ai() RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO bank_org_units (id, name, parent_id, tree_path, entity_type)
VALUES (NEW.id, NEW.name, NEW.parent_id, NEW.tree_path, NEW.entity_type);
    INSERT INTO bank_org_units_tree (parent_id, child_id, depth) VALUES
(NEW.id, NEW.id, 0);
    INSERT INTO bank_org_units_tree (parent_id, child_id, depth) SELECT
x.parent_id, NEW.id, x.depth + 1 
           FROM bank_org_units_tree x WHERE x.child_id = NEW.parent_id;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trigger_bank_parent_companies_tree_ai AFTER INSERT ON
bank_parent_companies FOR EACH ROW EXECUTE PROCEDURE
bank_org_units_tree_ai();
CREATE TRIGGER trigger_bank_unqualified_org_unit_tree_ai AFTER INSERT ON
bank_unqualified_org_units FOR EACH ROW EXECUTE PROCEDURE
bank_org_units_tree_ai();
CREATE TRIGGER trigger_branches_tree_ai AFTER INSERT ON branches FOR EACH
ROW EXECUTE PROCEDURE bank_org_units_tree_ai();

/* ========================================================== */
/* TRIGGER 'trigger_bank_org_units_tree_au'                   */
/* Updates the tree when a bank_org_unit is updated, possibly */
/* including changing the parent_id                           */
/* Function OK with inheritance                               */
/* ========================================================== */

CREATE OR REPLACE FUNCTION bank_org_units_tree_au() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF NOT OLD.parent_id IS DISTINCT FROM NEW.parent_id THEN
        RETURN NEW;
    END IF;
    IF OLD.parent_id IS NOT NULL THEN
        DELETE FROM bank_org_units_tree WHERE id in (
            SELECT r2.id FROM bank_org_units_tree r1 join
bank_org_units_tree r2 on r1.child_id = r2.child_id
            WHERE r1.parent_id = NEW.id AND r2.depth > r1.depth
        );
    END IF;
    IF NEW.parent_id IS NOT NULL THEN
        INSERT INTO bank_org_units_tree (parent_id, child_id, depth)
            SELECT r1.parent_id, r2.child_id, r1.depth + r2.depth + 1
        FROM
            bank_org_units_tree r1,
            bank_org_units_tree r2
        WHERE
            r1.child_id = NEW.parent_id AND
            r2.parent_id = NEW.id;
    END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trigger_bank_parent_companies_tree_au AFTER UPDATE ON
bank_parent_companies FOR EACH ROW EXECUTE PROCEDURE
bank_org_units_tree_au();
CREATE TRIGGER trigger_bank_unqualified_org_unit_tree_au AFTER UPDATE ON
bank_unqualified_org_units FOR EACH ROW EXECUTE PROCEDURE
bank_org_units_tree_au();
CREATE TRIGGER trigger_branches_tree_au AFTER UPDATE ON branches FOR EACH
ROW EXECUTE PROCEDURE bank_org_units_tree_au();

/* ========================================================== */
/* TRIGGER 'trigger_bank_org_units_tree_au'                   */
/* Forbids moves that would create loops (e.g. UPDATE         */
/* bank_org_units, SET parent_id=4, id=1 (root) =             */
/* endless loop in trigger for update                         */
/* Function OK with inheritance                               */
/* ========================================================== */

CREATE OR REPLACE FUNCTION bank_org_units_tree_bu() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF NEW.id <> OLD.id THEN
        RAISE EXCEPTION 'Changing ids is forbidden.';
    END IF;
    IF NOT OLD.parent_id IS DISTINCT FROM NEW.parent_id THEN
        RETURN NEW;
    END IF;
    IF NEW.parent_id IS NULL THEN
        RETURN NEW;
    END IF;
    PERFORM 1 FROM bank_org_units_tree WHERE ( parent_id, child_id ) = (
NEW.id, NEW.parent_id );
    IF FOUND THEN
        RAISE EXCEPTION 'Update blocked, because it would create loop in
tree.';
    END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trigger_bank_parent_companies_tree_bu BEFORE UPDATE ON
bank_parent_companies FOR EACH ROW EXECUTE PROCEDURE
bank_org_units_tree_bu();
CREATE TRIGGER trigger_bank_unqualified_org_unit_tree_bu BEFORE UPDATE ON
bank_unqualified_org_units FOR EACH ROW EXECUTE PROCEDURE
bank_org_units_tree_bu();
CREATE TRIGGER trigger_branches_tree_bu BEFORE UPDATE ON branches FOR EACH
ROW EXECUTE PROCEDURE bank_org_units_tree_bu();

/* ========================================================== */
/* TRIGGER 'trigger_bank_org_units_tree_path_bi'              */
/* Update bank_org_units.tree_path when a new bank_org_unit   */
/* is added. The path separator is '.'                        */
/* OK with inheritance as bank_org_units parent exists        */
/* (see systematic insert in bank_org_units_tree_ai)          */
/* ========================================================== */

CREATE OR REPLACE FUNCTION bank_org_units_tree_path_bi() RETURNS TRIGGER
AS
$BODY$
BEGIN
    IF NEW.parent_id IS NULL OR NEW.parent_id = 0 THEN
        NEW.tree_path := NEW.name;
    ELSE
        SELECT tree_path || '.' || NEW.name INTO NEW.tree_path FROM
bank_org_units WHERE id = NEW.parent_id;
    END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trigger_bank_org_units_tree_path_bi BEFORE INSERT ON
bank_org_units FOR EACH ROW EXECUTE PROCEDURE
bank_org_units_tree_path_bi();
CREATE TRIGGER trigger_bank_parent_companies_tree_path_bi BEFORE INSERT ON
bank_parent_companies FOR EACH ROW EXECUTE PROCEDURE
bank_org_units_tree_path_bi();
CREATE TRIGGER trigger_bank_unqualified_org_unit_tree_path_bi BEFORE INSERT
ON bank_unqualified_org_units FOR EACH ROW EXECUTE PROCEDURE
bank_org_units_tree_path_bi();
CREATE TRIGGER trigger_branches_tree_path_bi BEFORE INSERT ON branches FOR
EACH ROW EXECUTE PROCEDURE bank_org_units_tree_path_bi();

/* =============================================================== */
/* TRIGGER 'trigger_on_bank_unqualified_org_unit_parent_change_bu' */
/* When parent id changes, updates tree_path on table and children */
/* as well (branches and possibly org unit).                       */
/* An org unit can move to another org unit only (app control)     */
/* A parent company cannot move (app control)                      */
/* =============================================================== */

CREATE OR REPLACE FUNCTION
update_on_bank_unqualified_org_unit_parent_change_bu() RETURNS TRIGGER AS
$BODY$
DECLARE
    replace_from TEXT := '^';
    replace_to   TEXT := '';
BEGIN
    IF NOT OLD.parent_id IS distinct FROM NEW.parent_id THEN
        RETURN NEW;
    END IF;
    IF OLD.parent_id IS NOT NULL THEN
        SELECT '^' || tree_path || '.' INTO replace_from FROM bank_org_units
WHERE id = OLD.parent_id;
    END IF;
    IF NEW.parent_id IS NOT NULL THEN
        SELECT tree_path || '.' INTO replace_to FROM bank_org_units WHERE id
= NEW.parent_id;
    END IF;
    NEW.tree_path := regexp_replace( NEW.tree_path, replace_from, replace_to
);
    UPDATE bank_unqualified_org_units SET tree_path = regexp_replace(
tree_path, replace_from, replace_to ) 
        WHERE id in (SELECT child_id FROM bank_org_units_tree WHERE parent_id =
NEW.id AND depth > 0);
    UPDATE branches SET tree_path = regexp_replace( tree_path, replace_from,
replace_to ) 
        WHERE id in (SELECT child_id FROM bank_org_units_tree WHERE parent_id =
NEW.id AND depth > 0);
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trigger_on_bank_unqualified_org_unit_parent_change_bu BEFORE
UPDATE ON bank_unqualified_org_units FOR EACH ROW EXECUTE PROCEDURE
update_on_bank_unqualified_org_unit_parent_change_bu();

/* =============================================================== */
/* TRIGGER 'trigger_on_branch_parent_change_bu'                    */
/* When parent id changes, updates tree_path on table              */
/* A branch cannot have child (controlled by app)                  */
/* =============================================================== */

CREATE OR REPLACE FUNCTION update_on_branch_parent_change_bu() RETURNS
TRIGGER AS
$BODY$
DECLARE
    replace_from TEXT := '^';
    replace_to   TEXT := '';
BEGIN
    IF NOT OLD.parent_id IS distinct FROM NEW.parent_id THEN
        RETURN NEW;
    END IF;
    IF OLD.parent_id IS NOT NULL THEN
        SELECT '^' || tree_path || '.' INTO replace_from FROM bank_org_units
WHERE id = OLD.parent_id;
    END IF;
    IF NEW.parent_id IS NOT NULL THEN
        SELECT tree_path || '.' INTO replace_to FROM bank_org_units WHERE id
= NEW.parent_id;
    END IF;
    NEW.tree_path := regexp_replace( NEW.tree_path, replace_from, replace_to
);
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trigger_on_branch_parent_change_bu BEFORE UPDATE ON branches
FOR EACH ROW EXECUTE PROCEDURE update_on_branch_parent_change_bu();

/* ============================================================== */
/* TRIGGER 'trigger_on_bank_parent_company_name_change_bu'        */
/* updates parent table (inheritance lack) and updates            */
/* tree_path when name change on table and children as well       */
/* (unqualified org units and branches).                          */
                             
/* ============================================================== */
CREATE OR REPLACE FUNCTION update_on_bank_parent_company_name_change_bu()
RETURNS TRIGGER AS
$BODY$
BEGIN
    IF OLD.name <> NEW.name THEN
        NEW.tree_path := replace( NEW.tree_path, OLD.name, NEW.name);
        UPDATE bank_unqualified_org_units SET tree_path = replace( tree_path,
OLD.name, NEW.name)
            WHERE id in (SELECT child_id FROM bank_org_units_tree WHERE parent_id =
NEW.id AND depth > 0);
        UPDATE branches SET tree_path = replace( tree_path, OLD.name, NEW.name)
            WHERE id in (SELECT child_id FROM bank_org_units_tree WHERE parent_id =
NEW.id AND depth > 0);
        UPDATE ONLY bank_org_units SET tree_path = replace( tree_path, OLD.name,
NEW.name)
            WHERE id in (SELECT child_id FROM bank_org_units_tree WHERE parent_id =
NEW.id AND depth > 0);
    END IF;
    UPDATE ONLY bank_org_units SET name = NEW.name, parent_id =
NEW.parent_id, tree_path = NEW.tree_path WHERE id = NEW.id;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trigger_on_bank_parent_company_name_change_bu BEFORE UPDATE
ON
           bank_parent_companies FOR EACH ROW EXECUTE PROCEDURE
update_on_bank_parent_company_name_change_bu();

/* ============================================================== */
/* TRIGGER 'trigger_on_bank_unqualified_org_unit_name_change_bu'  */
/* updates parent table (inheritance lack) and updates            */
/* tree_path when name change on table and children as well       */
/* (branches).                                                    */
                             
/* ============================================================== */
CREATE OR REPLACE FUNCTION
update_on_bank_unqualified_org_unit_name_change_bu() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF OLD.name <> NEW.name THEN
        NEW.tree_path := replace( NEW.tree_path, OLD.name, NEW.name);
        UPDATE branches SET tree_path = replace( tree_path, OLD.name, NEW.name)
            WHERE id in (SELECT child_id FROM bank_org_units_tree WHERE parent_id =
NEW.id AND depth > 0);
        UPDATE ONLY bank_org_units SET tree_path = replace( tree_path, OLD.name,
NEW.name)
            WHERE id in (SELECT child_id FROM bank_org_units_tree WHERE parent_id =
NEW.id AND depth > 0);
    END IF;
    UPDATE ONLY bank_org_units SET name = NEW.name, parent_id =
NEW.parent_id, tree_path = NEW.tree_path WHERE id = NEW.id;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trigger_on_bank_unqualified_org_unit_name_change_bu BEFORE
UPDATE ON
           bank_unqualified_org_units FOR EACH ROW EXECUTE PROCEDURE
update_on_bank_unqualified_org_unit_name_change_bu();

/* ============================================================== */
/* TRIGGER 'trigger_decommission_bank_accounts_on_bic_deleted_bd' */
/* Set account status to decommissioned in                        */
/* [payee/beneficiary]_bank_accounts when a bank is deleted       */
/* for each account managed by this bic.                          */
                             
/* ============================================================== */

CREATE OR REPLACE function set_account_status_decommissionned() RETURNS
TRIGGER AS
$BODY$
BEGIN
    UPDATE payee_bank_accounts SET account_status='DECOMMISSIONNED' WHERE
bic=OLD.bic;
    UPDATE beneficiary_bank_accounts SET account_status='DECOMMISSIONNED' WHERE
bic=OLD.bic;
    return OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trigger_decommission_bank_accounts_on_bic_deleted_bd BEFORE
DELETE ON
           banks FOR EACH ROW EXECUTE PROCEDURE
set_account_status_decommissionned();
CREATE TRIGGER trigger_decommission_bank_accounts_on_parent_companies_bd
BEFORE DELETE ON
           bank_parent_companies FOR EACH ROW EXECUTE PROCEDURE
set_account_status_decommissionned();
CREATE TRIGGER trigger_decommission_bank_accounts_on_branches_bd BEFORE
DELETE ON
           branches FOR EACH ROW EXECUTE PROCEDURE
set_account_status_decommissionned();

/* ======================================================= */
/* TRIGGER 'trigger_bank_profiles_ai'                      */
/* Populates parent on INSERT INTO in xxx_bank_profiles    */
/* ======================================================= */

/*  Duplicates new row in parent table (otherwise remains empty and
constraint violation on parent_id not existing (FK on bank_profiles
profile_id)
 *  Warning : it requires SELECT DISTINCT FROM bank_profiles, if read occurs
in this table, to avoid duplicates
 */
CREATE OR REPLACE FUNCTION bank_profiles_ai() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
    INSERT INTO bank_profiles (profile_id, profile_type, bank_id) VALUES
(NEW.profile_id, NEW.profile_type, NEW.bank_id);
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trigger_payer_bank_profiles_ai AFTER INSERT ON
payer_bank_profiles FOR EACH ROW EXECUTE PROCEDURE bank_profiles_ai();
CREATE TRIGGER trigger_beneficiary_bank_profiles_ai AFTER INSERT ON
beneficiary_bank_profiles FOR EACH ROW EXECUTE PROCEDURE
bank_profiles_ai();

/* ======================================================= */
/* TRIGGER 'trigger_bank_services_ai'                      */
/* Populates parent on INSERT INTO in xxx_bank_services    */
/* ======================================================= */

/*  Duplicates new row in parent table (otherwise remains empty and
constraint violation on parent_id not existing (FK on bank_services
service_id)
 *  Warning : it requires SELECT DISTINCT FROM bank_services, if read occurs
in this table, to avoid duplicates
 */
CREATE OR REPLACE FUNCTION bank_services_ai() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
    INSERT INTO bank_services (service_id, profile_id, profile_type,
primary_agent) VALUES (NEW.service_id, NEW.profile_id, NEW.profile_type,
NEW.primary_agent);
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trigger_payer_bank_services_ai AFTER INSERT ON
payer_bank_services FOR EACH ROW EXECUTE PROCEDURE bank_services_ai();
CREATE TRIGGER trigger_beneficiary_bank_services_ai AFTER INSERT ON
beneficiary_bank_services FOR EACH ROW EXECUTE PROCEDURE
bank_services_ai();




--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs