recursive_query.sql

drop table if exists metadata cascade ;
drop table if exists obj1;
drop table if exists obj2;
drop table if exists parent_child;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

/* This table is common across tables */
create table metadata (
                          id serial primary key,
                          external_id serial,
                          common_data varchar
);

create unique index metadata_ext_id on metadata(external_id);




/*
    obj1 and obj2 are of the same class of data.

    ID is unique between them because of the initially deferred reference.
    i.e metadata -> obj1 and metadata -> obj2 is a 1-2-1 relationship

*/
create table obj1 (
                      id int primary key
                          references metadata
                              deferrable initially deferred,
                      data1 varchar
);

create table obj2 (
                      id int primary key
                          references metadata
                              deferrable initially deferred,
                      data2 varchar
);


create table parent_child (
                              id serial primary key,
                              parent int not null,
                              child int not null,
                              constraint fk_parent foreign key(parent) references metadata(external_id),
                              constraint fk_child foreign key(child) references metadata(external_id)
);


BEGIN;
/* create some example data */
insert into metadata(id, external_id) select generate_series, generate_series from generate_series(0, 100000, 1);
insert into obj1(id, data1) select generate_series, gen_salt('md5') from generate_series(0, 100000, 2);
insert into obj2(id, data2) select generate_series, gen_salt('md5') from generate_series(1, 100000, 2);
insert into parent_child(parent, child) values (1, 2), (2, 5), (5, 100), (100, 101);

COMMIT ;

set join_collapse_limit = 12;

explain analyse with recursive parent_child_union_thing(
                                                        depth, source, id, data1, data2, type, common_data, external_id
    ) as (
    (
        select
            0 as depth,
            'parent' as source,
            cast(pjoin.id as int),
            pjoin.data1,
            pjoin.data2,
            pjoin.type,
            pjoin.common_data,
            pjoin.external_id
        from (
                 (
                     select obj1.id    as id,
                            data1 as data1,
                            null  as data2,
                            'obj1' as type,
                            m.common_data as common_data,
                            m.external_id as external_id
                     from obj1
                              join metadata m on obj1.id = m.id
                     order by id
                     limit 1000
                 )
                 union all
                 (
                     select obj2.id    as id,
                            null  as data1,
                            data2 as data2,
                            'obj2' as type,
                            m.common_data as common_data,
                            m.external_id as external_id
                     from obj2
                              join metadata m on obj2.id = m.id
                     limit 1000
                 )
             ) as pjoin ORDER BY pjoin.id
    )
    union all
    (

        select
                parent_child_union_thing.depth + 1,
                'child' as source,
                cast(pjoin.id as int),
                pjoin.data1,
                pjoin.data2,
                pjoin.type,
                metadata.common_data as common_data,
                metadata.external_id as external_id
        from (
                 (
                     select obj1.id as id,
                            data1   as data1,
                            null    as data2,
                            'obj1'  as type
                     from obj1
                 )
                 union all
                 (
                     select obj2.id as id,
                            null    as data1,
                            data2   as data2,
                            'obj2'  as type
                     from obj2
                 )
        ) as pjoin
        join metadata on pjoin.id = metadata.external_id
        join parent_child on metadata.external_id = parent_child.child
        join parent_child_union_thing on parent_child_union_thing.external_id = parent_child.parent
    )
)
select *
from parent_child_union_thing;