fixing_constraints.sql

DO $$                                                                           
    DECLARE myrecord record;                                                    
    DECLARE theweek text;                                                       
                                                                                
    BEGIN                                                                       
        FOR myrecord IN SELECT relname, conname from pg_constraint               
            JOIN pg_class ON pg_constraint.conrelid = pg_class.oid              
            WHERE consrc ~ 'without' and split_part(relname, '_201', 1)         
            IN (select table_name from report_partition_info                    
                WHERE partition_column = 'date_processed') LIMIT 1              
        LOOP                                                                    
            EXECUTE 'ALTER TABLE ' || quote_ident(myrecord.relname)             
                || ' DROP CONSTRAINT IF EXISTS '                                
                || quote_ident(myrecord.conname) || ';';                        
                                                                                
            theweek = substring(myrecord.relname from '........$');             
                                                                                
            EXECUTE 'ALTER TABLE ' || quote_ident(myrecord.relname)             
                || ' ADD CONSTRAINT ' || quote_ident(myrecord.conname)          
                || ' CHECK ((date_processed >= timestamptz('                    
                || quote_literal(to_char(date(theweek), 'YYYY-MM-DD')) || '))'  
                || ' AND (date_processed < timestamptz('                        
                || quote_literal(to_char(date(theweek) + 7, 'YYYY-MM-DD'))      
                || ')));';                                                      
                                                                                
            RAISE NOTICE 'DONE: %', myrecord.relname;                           
    END LOOP;                                                                   
                                                                                
END$$;