dulkemin.sql

CREATE OR REPLACE VIEW public.extended_merchant_access_view
AS SELECT merchant1.assigned_id,
    merchant1.assigned_secure_id,
    merchant1.asigned_name,
    merchant1.privilege_id,
    merchant1.privilege_secure_id,
    merchant1.privilege_name
   FROM ( SELECT m.id AS assigned_id,
            m.secure_id AS assigned_secure_id,
            m.name AS asigned_name,
            m_child.child_id AS privilege_id,
            m_child.secure_id AS privilege_secure_id,
            m_child.child_name AS privilege_name
           FROM merchant m
             FULL JOIN ( SELECT mpcmr.parent_id,
                    m1.id AS child_id,
                    m1.secure_id,
                    m1.name AS child_name
                   FROM ( WITH RECURSIVE mlmr(parent_id, child_id, level) AS (
                                 SELECT mlm.parent_id,
                                    mlm.child_id,
                                    1 AS level
                                   FROM merchant_level_mapping mlm
                                UNION ALL
                                 SELECT mlm.parent_id,
                                    mlmr_1.child_id,
                                    mlmr_1.level + 1
                                   FROM merchant_level_mapping mlm
                                     JOIN mlmr mlmr_1 ON mlm.child_id = mlmr_1.parent_id
                                )
                         SELECT mlmr.parent_id,
                            mlmr.child_id,
                            mlmr.level,
                            mlmr.maxlevel
                           FROM ( SELECT mlmr_1.parent_id,
                                    mlmr_1.child_id,
                                    mlmr_1.level,
                                    max(mlmr_1.level) OVER (PARTITION BY mlmr_1.child_id) AS maxlevel
                                   FROM mlmr mlmr_1) mlmr) mpcmr
                     JOIN merchant m1 ON mpcmr.child_id = m1.id) m_child ON m.id = m_child.parent_id) merchant1
UNION
 SELECT m_parent.id AS assigned_id,
    m_parent.secure_id AS assigned_secure_id,
    m_parent.name AS asigned_name,
    m_child.id AS privilege_id,
    m_child.secure_id AS privilege_secure_id,
    m_child.name AS privilege_name
   FROM merchant m_parent
     JOIN merchant m_child ON m_parent.id = m_child.id;

-- Permissions

ALTER TABLE public.extended_merchant_access_view OWNER TO astrapay;
GRANT ALL ON TABLE public.extended_merchant_access_view TO astrapay;