[BUGS] BUG #14517: Inheritance to behave as for OOP - Mailing list pgsql-bugs
| From | scoutant@freesbee.fr |
|---|---|
| Subject | [BUGS] BUG #14517: Inheritance to behave as for OOP |
| Date | |
| Msg-id | 20170126170030.1440.67035@wrigleys.postgresql.org Whole thread Raw |
| Responses |
Re: [BUGS] BUG #14517: Inheritance to behave as for OOP
|
| List | pgsql-bugs |
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
pgsql-bugs by date: