Re: Using Lateral - Mailing list pgsql-general

Thanks Paul.

I was hesitant to post my full query.  It is a long and complicated
query.  But here it is now:

WITH p AS (
    SELECT
        ARRAY [ ARRAY [ 't',
'http://scientific.thomsonreuters.com/schema/wok5.4/public/FullRecord'
] ] AS ns),
uts AS (
    SELECT
        s.ut
    FROM
        wos_source.core_2015 s
    WHERE
        s.id BETWEEN 999900
        AND 100000
             ),
utsb AS (
    SELECT
        b.ut
    FROM
        wos_2017_1.belongs2 b,
        uts
    WHERE
        b.ut = uts.ut), q AS (
    SELECT
        s.ut,
        unnest (xpath
('//t:static_data/t:fullrecord_metadata/t:addresses/t:address_name/t:address_spec',
                xml,
                ns)) AS address_spec
    FROM
        p,
        uts a
    LEFT JOIN utsb b ON b.ut = a.ut
    LEFT JOIN wos_source.core_2015 s ON s.ut = a.ut
WHERE
    b.ut IS NULL), r AS (
    SELECT
        s.ut,
        unnest (xpath
('//t:static_data/t:item/t:reprint_contact/t:address_spec',
                xml,
                ns)) AS raddress_spec
    FROM
        p,
        wos_2017_1.publication l,
        uts a
    LEFT JOIN utsb b ON b.ut = a.ut
    LEFT JOIN wos_source.core_2015 s ON s.ut = a.ut
WHERE
    b.ut IS NULL
    AND xpath_exists
('//t:static_data/t:item/t:reprint_contact/t:address_spec', xml, ns)
    AND s.ut = l.ut
    AND l.pubyear < 1998), qd AS (
    SELECT
        DISTINCT ut,
        CASE
            WHEN xpath_exists ('//t:address_spec/t:country/text()',
                q.address_spec,
                p.ns)
            THEN unnest (xpath ('//t:address_spec/t:country/text()',
                    q.address_spec,
                    p.ns))::citext
            ELSE NULL
        END country,
        CASE
            WHEN xpath_exists ('//t:address_spec/t:city/text()',
                q.address_spec,
                p.ns)
            THEN unnest (xpath ('//t:address_spec/t:city/text()',
                    q.address_spec,
                    p.ns))::citext
            ELSE NULL
        END city,
        CASE
            WHEN xpath_exists ('//t:organizations/t:organization/text()',
                q.address_spec,
                ns)
            THEN unnest (xpath ('//t:organizations/t:organization/text()',
                    q.address_spec,
                    ns))::citext
            ELSE NULL
        END organisation,
        CASE
            WHEN xpath_exists
('//t:organizations/t:organization[@pref="Y"]/text()',
                q.address_spec,
                ns)
            THEN unnest (xpath
('//t:organizations/t:organization[@pref="Y"]/text()',
                    q.address_spec,
                    ns))::citext
            ELSE NULL
        END AS prefname,
        CASE
            WHEN xpath_exists ('//t:suborganizations/t:suborganization/text()',
                q.address_spec,
                ns)
            THEN unnest (xpath ('//t:suborganizations/t:suborganization/text()',
                    q.address_spec,
                    ns))::citext
            ELSE NULL
        END suborgname,
        CASE
            WHEN xpath_exists ('/t:address_spec/@addr_no',
                q.address_spec,
                ns)
            THEN (xpath ('/t:address_spec/@addr_no',
                    q.address_spec,
                    ns))
            [ 1 ]::text::INTEGER
            ELSE NULL
        END addr_no
    FROM
        p,
        q),
rd AS (
    SELECT
        DISTINCT ut,
        CASE
            WHEN xpath_exists ('//t:address_spec/t:country/text()',
                r.raddress_spec,
                p.ns)
            THEN unnest (xpath ('//t:address_spec/t:country/text()',
                    r.raddress_spec,
                    p.ns))::citext
            ELSE NULL
        END country,
        CASE
            WHEN xpath_exists ('//t:address_spec/t:city/text()',
                r.raddress_spec,
                p.ns)
            THEN unnest (xpath ('//t:address_spec/t:city/text()',
                    r.raddress_spec,
                    p.ns))::citext
            ELSE NULL
        END city,
        CASE
            WHEN xpath_exists ('//t:organizations/t:organization/text()',
                r.raddress_spec,
                ns)
            THEN unnest (xpath ('//t:organizations/t:organization/text()',
                    r.raddress_spec,
                    ns))::citext
            ELSE NULL
        END organisation,
        CASE
            WHEN xpath_exists
('//t:organizations/t:organization[@pref="Y"]/text()',
                r.raddress_spec,
                ns)
            THEN unnest (xpath
('//t:organizations/t:organization[@pref="Y"]/text()',
                    r.raddress_spec,
                    ns))::citext
            ELSE NULL
        END AS prefname,
        CASE
            WHEN xpath_exists ('//t:suborganizations/t:suborganization/text()',
                r.raddress_spec,
                ns)
            THEN unnest (xpath ('//t:suborganizations/t:suborganization/text()',
                    r.raddress_spec,
                    ns))::citext
            ELSE NULL
        END suborgname,
        CASE
            WHEN xpath_exists ('/t:address_spec/@addr_no',
                r.raddress_spec,
                ns)
            THEN (xpath ('/t:address_spec/@addr_no',
                    r.raddress_spec,
                    ns))
            [ 1 ]::text::INTEGER
            ELSE NULL
        END reprint_addr_no
    FROM
        p,
        r
    WHERE
        r.raddress_spec IS NOT NULL), uq AS (
    SELECT
        DISTINCT qd.ut,
        qd.addr_no::INTEGER,
        0 AS reprint_addr_no,
        c.uuid city_id,
        y.uuid country_id,
        o.uuid organisation_id,
        u.uuid suborg_id,
        p.uuid pref_name_id
    FROM
        qd
    LEFT JOIN wos_2017_1.city c ON (c.city = qd.city)
    LEFT JOIN wos_2017_1.country_alias y ON (y.country = qd.country)
    LEFT JOIN wos_2017_1.organisation o ON (o.organisation = regexp_replace (
         regexp_replace (
             regexp_replace (qd.organisation, '<', '<', 'g'),
        '&', '&', 'g'),
    '>', '>', 'g'))
    LEFT JOIN wos_2017_1.org_pref_name p ON (p.name = qd.prefname)
    LEFT JOIN wos_2017_1.suborg u ON (u.suborgname = regexp_replace (
         regexp_replace (
             regexp_replace (qd.suborgname, '<', '<', 'g'),
        '&', '&', 'g'),
    '>', '>', 'g'))),
ur AS (
    SELECT
        DISTINCT rd.ut,
        0 AS addr_no,
        rd.reprint_addr_no::INTEGER,
        c.uuid city_id,
        y.uuid country_id,
        o.uuid organisation_id,
        u.uuid suborg_id,
        p.uuid pref_name_id
    FROM
        r,
        rd
    LEFT JOIN wos_2017_1.city c ON (c.city = rd.city)
    LEFT JOIN wos_2017_1.country_alias y ON (y.country = rd.country)
    LEFT JOIN wos_2017_1.organisation o ON (o.organisation = regexp_replace (
         regexp_replace (
             regexp_replace (rd.organisation, '<', '<', 'g'),
        '&', '&', 'g'),
    '>', '>', 'g'))
    LEFT JOIN wos_2017_1.org_pref_name p ON (p.name = rd.prefname)
    LEFT JOIN wos_2017_1.suborg u ON (u.suborgname = regexp_replace (
         regexp_replace (
             regexp_replace (rd.suborgname, '<', '<', 'g'),
        '&', '&', 'g'),
    '>', '>', 'g'))
WHERE
    r.raddress_spec IS NOT NULL), qr AS (
    SELECT
        *
    FROM
        uq
    UNION
    SELECT
        ur.*
    FROM
        ur)
SELECT
    DISTINCT ON (qr.ut,
        qr.addr_no,
        qr.reprint_addr_no,
        a.uuid,
        qr.organisation_id,
        qr.suborg_id,
        qr.pref_name_id)
    qr.ut,
    qr.addr_no,
    qr.reprint_addr_no,
    a.uuid AS address_id,
    qr.organisation_id,
    qr.suborg_id,
    qr.pref_name_id,
    uuid_generate_v1 ()
    uuid
FROM
    qr
    LEFT JOIN wos_2017_1.address a ON (a.city_id = qr.city_id
        AND a.country_id = qr.country_id)




Johann
-- 
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)