0

I am using this query to purge data from the bottom of the tree up. This is just the first step in my process. Does anyone have any suggestions on how to handle the recursion?

Thanks

DROP TABLE IF EXISTS parent_child_relationships_tab;
CREATE TABLE parent_child_relationships_tab
AS
SELECT pk.table_catalog,                  pk.table_schema,                  pk.table_name,
       pk.constraint_catalog,             pk.constraint_schema,             pk.constraint_name,
       rc.unique_constraint_catalog,      rc.unique_constraint_schema,      rc.unique_constraint_name,
       array_agg ( pk.column_name::text ORDER BY pk.ordinal_position )   AS parent_column_names
  FROM information_schema.key_column_usage                               AS pk -- parent_constraint_name, parent_table_name, parent key_column_name
  LEFT OUTER JOIN information_schema.referential_constraints             AS rc
    ON pk.table_catalog      = pk.table_catalog
   AND pk.table_schema       = pk.table_schema
   AND pk.table_name         = pk.table_name
   AND pk.constraint_catalog = rc.constraint_catalog
   AND pk.constraint_schema  = rc.constraint_schema
   AND pk.constraint_name    = rc.constraint_name
  GROUP BY pk.table_catalog,                  pk.table_schema,                  pk.table_name,
           pk.constraint_catalog,             pk.constraint_schema,             pk.constraint_name,
           rc.unique_constraint_catalog,      rc.unique_constraint_schema,      rc.unique_constraint_name;

DROP TABLE parent_child_relationships_heirarchical_query_tab;
CREATE TABLE parent_child_relationships_heirarchical_query_tab
AS
WITH RECURSIVE parent_child_relationships_rec
AS ( SELECT base_case.table_catalog,                     base_case.table_schema,                     base_case.table_name,
            base_case.constraint_catalog,                base_case.constraint_schema,                base_case.constraint_name,
            base_case.unique_constraint_catalog,         base_case.unique_constraint_schema,         base_case.unique_constraint_name,
            0 AS level
       FROM parent_child_relationships_tab         AS base_case
      UNION
     SELECT parent_child_tab.table_catalog,              parent_child_tab.table_schema,              parent_child_tab.table_name,
            parent_child_tab.constraint_catalog,         parent_child_tab.constraint_schema,         parent_child_tab.constraint_name,
            parent_child_tab.unique_constraint_catalog,  parent_child_tab.unique_constraint_schema,  parent_child_tab.unique_constraint_name,
            nth_case.level+1
       FROM parent_child_relationships_rec                AS nth_case
      INNER JOIN parent_child_relationships_tab     AS parent_child_tab
         ON nth_case.constraint_catalog       = parent_child_tab.unique_constraint_catalog
        AND nth_case.constraint_schema        = parent_child_tab.unique_constraint_schema
        AND nth_case.constraint_name          = parent_child_tab.unique_constraint_name  )
SELECT table_name,              MAX( level ) AS level
  FROM parent_child_relationships_rec
 GROUP BY table_name
 ORDER BY level, 1;

UPDATE: I simplified the logic greatly. The basic query is working, but the level does not increment between children, and grand children, and great grand children, etc. Hence, I get either 1 or 2 for level.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.